PostgreSQL offers a rich set of native data types for users. Users can add new types with the help of CREATE TYPE command. It also makes queries simpler and more readable.
PostgreSQL supports the following data types:
- Text Types
- Numeric Types
- Dates and Times
- XML
- JSON
- Boolean
- Bits
- Binary Data
- Network
- Arrays
- Create your Data Type
- Boolean
- Temporal
- UUID
- Array
- JSON
- Special Data types for storing a network address and geometric data.
Let's study them in detail
Character Datatypes
PostgreSQL supports character data types for storing text values. PostgreSQL builds character data types off of the same internal structures. PostgreSQL offers three character data types: CHAR(n), VARCHAR(n), and TEXT.Name | Description |
varchar(n) | Allows you to declare variable-length with a limit |
Char(n) | Fixed-length, blank padded |
Text | Use can use this data type to declare a variable with unlimited length |
Numeric Datatypes
PostgreSQL supports two distinct types of numbers:
- Integers
- Floating-point numbers
Name | Store size | Range |
smallest | 2 bytes | -32768 to +32767 |
integer | 4 bytes | -2147483648 to +2147483647 |
bigint | 4 bytes | -9223372036854775808 to 9223372036854775807 |
decimal | variable | If you declared it as decimal datatype ranges from 131072 digits before the decimal point to 16383 digits after the decimal point |
numeric | variable | If you declare it as the number, you can include number up to 131072 digits before the decimal point to 16383 digits after the decimal point |
real | 4 bytes | 6 decimal digits precision |
double | 8 bytes | 15 decimal digits precision |
Binary Data Types
A binary string is a sequence of octets or bytes. Binary Data types are divided in two ways.
- Binary strings allow storing odds of value zero
- Non- printable octets
Name | Storage size | Description |
Byte | 1 to 4 bytes plus the size of the binary string | Variable-length binary string |
Network Address Type
Many applications store network information like IP address of users or
sensors. PostgreSQL has three native types which help you to optimize the network data. Name | Size | Description |
cider | 7 or 19 byes | IPV4 and IPv6 networks |
Inet | 7 or 19 bytes | IPV4 and IPV5 host and networks |
macaddr | 6 bytes | MAC addresses |
Using Network Address Types has following advantages
- Storage Space Saving
- Input error checking
- Functions like searching data by subnet
Text Search Type
PostgreSQL provides two data types which are designed to support full-text search. Full-text search is searching through a collection of natural-language documents to search those that best match a query.
- Tsvector text search data type represents a document in a form optimized for text search
- The query type text search stores the keywords that need to be searched
Date/Time Datatypes
PostgreSQL timestamp offers microsecond precision instead of second precision. Moreover, you also have the option of storing with timezone or without. PostgreSQL will convert timestamp with timezone to UTC on input and store it.
Date and time input is accepted in various format, including traditional Postgres, ISO 8601. SQL-compatible etc.
PostgreSQL supports Day / Month / Year ordering. Formats supported are DMY, MDY, YMD Name | Size | Range | Resolution |
Timestamp without timezone | 8 bytes | 4713 BC to 294276 AD | 1microsecond/14 digits |
Timestamp with timezone | 8 bytes | 4713 BC to 294276 AD | 1microsecond/14 digits |
date | 4 bytes | 4713 BC to 294276 AD | One day |
Time without timezone | 8 bytes | 00:00:00 to 24:00:00 | 1microsecond/14 digits |
Time with timezone | 12 bytes | 00:00:00 + 1459 to 24:00:00-1459 | 1microsecond/14 digits |
Interval | 12 bytes | -178000000 to 178000000 years | 1microsecond/14 digits |
Input | Description |
2025-09-07 | ISO 8601, September 7 with any date style (recommended format) |
September 7, 2025 | September 7 with any date style |
9/7/2025 | September 7 with MDY, July 9 with DMY |
9/7/25 | September 7, 2025, with MDY |
2025-Sep-7 | September 7 with any date style |
Sep-7-2018 | September 7 with any date style |
7-Sep-25 | September 7, 2025, with YMD |
20250907 | ISO 8601,7 Sep 20225 in any mode |
2025.250 | year and day of the year, in this case, Sep 7, 2025 |
J25250 | Julian date |
Input | Description |
11:19:38.507 11:19:38 11:19 111938 | ISO 8601 |
11:19 AM | Same as 11:19 |
11:19 PM | same as 23:19 |
23:19-3 23:19-03:00 231900-03 | ISO 8601, same as 11:19 PM EST |
23:19 EST | time zone specified as EST, same as 11:19 PM EST |
![Postgresql Postgresql](/uploads/1/2/3/7/123760102/838674314.png)
Boolean Type
A Boolean data type can hold
- True
- False
- null
values.
You use a bool or boolean keyword to declare a column with the Boolean data type.
When you insert values into a boolean column, Postgre converts values like
- Yes
- y
- 1
- t
- true
into 1.
While values like
- No
- N
- 0
- F
- False
are converted to 0
Game consoles for sale in south africa. For online support related queries: 0861 426 333 email protected For store & general support queries: 0861 426 322 73 email protected For store card/credit related queries.
While selecting data, the values are again converted back to yes, true, y, etc.
Geometric Data Types
Geometric data types represent two-dimensional spatial objects. They help perform operations like rotations, scaling, translation, etc.Name | Storage Size | Representation | Description |
Point | 16 bytes | Point on a plane | (x,y) |
Line | 32 bytes | Infinite line | ((xl.yl ).(x2.y2)) |
Lseg | 32 bytes | Finite line segment | ((xl.yl ).(x2.y2)) |
Box | 32 bytes | Rectangular Box | ((xl.yl ).(x2.y2)) |
Path | 16n + 16n bytes | Close and Open Path | ((xl.yl),..) |
Polygon | 40 + 16n bytes | Polygon | [(xl.yl)..] |
Circle | 24 bytes | Circle | <(x.y).r> (center point and radius) |
Enumerated Types
Postgres Double Type
Enumerated Data types in PostgreSQL is useful for representing rarely changing information such as country code or branch id. The Enumerated data type is represented in a table with foreign keys to ensure data integrity.
Example:
Hair color is fairly static in a demographic database
Range Type
Many business applications require data in ranges. Typically, two columns (example: start date, end date) are defined to deal with ranges. This is both inefficient and difficult to maintain.
Postgre has built range types as follows
- int4range — Display range of integer
- int8range — Display range of bigint
- numrange — Shows the numeric range
- tstrange — Helps you to display timestamp without time zone
- strange — Allows you to display timestamp with time zone
- date range — Range of date
UUID type
Universally Unique Identifies (UUID) is a 128-bit quantity which is generated by an algorithm. It is very unlikely that the same identifier will be generated by another person in the world using the same algorithm. That's why for the distributed systems, these identifiers are an ideal choice as it offers uniqueness within a single database. A UUID is written as a group of lower-case hexadecimal digits, with various groups separated by hyphens.
PostgreSQL has a native UUID data type which consumes 16 bytes of storage. UUID is an ideal Data type for primary keys.
Example:
Postgre also accepts alternative forms of UUID inputs like all capital case, no hyphens, braces, etc.
XML type
PostgreSQL allows you to store XML data in a data type, but it is nothing more than an extension to a text data type. But the advantage is that it checks that the input XML is well-formed.
Example:
JSON Type
To store JSON data PostgreSQL offers 2 data types
- JSON
- JSONB
json | Jsonb |
A simple extension of a text data type with JSON validation | A binary representation of the JSON data |
Insert is fast but data retrieval relatively slow. | Insert is slow but select (data retrieval is fast) |
Saves inputted data exactly the way it is including whitespace. | Supports indexing. May optimize the whitespace to make retrieval faster. |
Reprocessing on data retrieval | No reprocessing required on data retrieval |
Most widely used JSON data type used us jsonb unless there is some specialized need to use JSON data type.
Example:
Pseudo-Types
PostgreSQL has many special-purpose entries that are called pseudo-types. You can't use pseudo-type as a column data type. There are used to declare or function's argument or return type.
Each of the available pseudo-types is helpful in situations where a function's behavior docs do not correspond to simply taking or returning a value of a specific SQL data type. Name | Description |
Any | Function accepts all input data type. |
An array | The function accepts any array data type. |
Any element | The function accepts any data type. |
Any enum | The function accepts any enum data type. |
Nonarray | The function accepts any non-array data type. |
Cstring | Function accepts or returns null-terminated C string. |
Internal | Internal function accepts or returns server-internal data type. |
Language_handler | It is declared to return language handler. |
Record | Find a function which returns an unspecified row type. |
Trigger | A trigger function is used to return trigger. |
It is important that the user who is using this function need to make sure that the function will behave securely when a pseudo-type is used as an argument type.
Best practices using Data types
- Use 'text' data type unless you want to limit the input
- Never use 'char.'
- Integers use 'int.' Use bigint only when you have really big numbers
- Use 'numeric' almost always
- Use float data type if you have IEEE 754 data source
Summary
- PostgreSQL offers a rich set of native data types for users
- PostgreSQL supports character data types for storing text values
- PostgreSQL supports two distinct types of numbers: 1. Integers, 2. Floating-point numbers
- A binary string is a sequence of bytes or octets
- PostgreSQL has Network address type help you to optimize storage of network data
- Text search data types are designed to support full-text search
- Date/Time data types are allow date and time information in various formats
- A Boolean data type can hold three values 1. True 2. False 3. Null
- Geometric data types represent two-dimensional spatial objects
- Enumerated Data types in PostgreSQL is useful for representing rarely changing information such as country code or branch id
- Universally Unique Identifies (UUID) is a 128-bit quantity which is generated by an algorithm
- PostgreSQL has many special-purpose entries that are called pseudo-types
- It is best practice to use 'text' data type unless you want to limit the input
Numeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating-point numbers, and selectable-precision decimals. Table 8-2 lists the available types.
Table 8-2. Numeric Types
Name | Storage Size | Description | Range |
---|---|---|---|
smallint | 2 bytes | small-range integer | -32768 to +32767 |
integer | 4 bytes | typical choice for integer | -2147483648 to +2147483647 |
bigint | 8 bytes | large-range integer | -9223372036854775808 to 9223372036854775807 |
decimal | variable | user-specified precision, exact | no limit |
numeric | variable | user-specified precision, exact | no limit |
real | 4 bytes | variable-precision, inexact | 6 decimal digits precision |
double precision | 8 bytes | variable-precision, inexact | 15 decimal digits precision |
serial | 4 bytes | autoincrementing integer | 1 to 2147483647 |
bigserial | 8 bytes | large autoincrementing integer | 1 to 9223372036854775807 |
The syntax of constants for the numeric types is described in Section 4.1.2. The numeric types have a full set of corresponding arithmetic operators and functions. Refer to Chapter 9 for more information. The following sections describe the types in detail.
8.1.1. Integer Types
The types smallint, integer, and bigint store whole numbers, that is, numbers without fractional components, of various ranges. Attempts to store values outside of the allowed range will result in an error.
The type integer is the common choice, as it offers the best balance between range, storage size, and performance. The smallint type is generally only used if disk space is at a premium. The bigint type should only be used if the integer Visual logic free download. range is insufficient, because the latter is definitely faster.
On very minimal operating systems the bigint type might not function correctly, because it relies on compiler support for eight-byte integers. On such machines, bigint acts the same as integer, but still takes up eight bytes of storage. (We are not aware of any modern platform where this is the case.)
SQL only specifies the integer types integer (or int), smallint, and bigint. The type names int2, int4, and int8 are extensions, which are also used by some other SQL database systems.
8.1.2. Arbitrary Precision Numbers
The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, arithmetic on numeric values is very slow compared to the integer types, or to the floating-point types described in the next section.
We use the following terms below: The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.
Both the maximum precision and the maximum scale of a numeric column can be configured. To declare a column of type numeric use the syntax:
The precision must be positive, the scale zero or positive. Alternatively:
selects a scale of 0. Specifying:
without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale. (The SQL standard requires a default scale of 0, i.e., coercion to integer precision. We find this a bit useless. If you're concerned about portability, always specify the precision and scale explicitly.)
If the scale of a value to be stored is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits. Then, if the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is raised.
Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the numeric type is more akin to varchar(n) than to char(n).) The actual storage requirement is two bytes for each group of four decimal digits, plus five to eight bytes overhead.
In addition to ordinary numeric values, the numeric type allows the special value NaN, meaning 'not-a-number'. Any operation on NaN yields another NaN. When writing this value as a constant in an SQL command, you must put quotes around it, for example UPDATE table SET x = 'NaN'. On input, the string NaN is recognized in a case-insensitive manner.
Note: In most implementations of the 'not-a-number' concept, NaN is not considered equal to any other numeric value (including NaN). In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.
The types decimal and numeric are equivalent. Both types are part of the SQL standard.
8.1.3. Floating-Point Types
The data types real and double precision are inexact, variable-precision numeric types. In practice, these types are usually implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.
Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points: Final fantasy tactics war of the lions mods.
- If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.
- If you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.
- Comparing two floating-point values for equality might not always work as expected.
On most platforms, the real type has a range of at least 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are too large or too small will cause an error. Rounding might take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.
Note: The extra_float_digits setting controls the number of extra significant digits included when a floating point value is converted to text for output. With the default value of 0, the output is the same on every platform supported by PostgreSQL. Increasing it will produce output that more accurately represents the stored value, but may be unportable.
In addition to ordinary numeric values, the floating-point types have several special values:
Infinity
-Infinity
NaN
These represent the IEEE 754 special values 'infinity', 'negative infinity', and 'not-a-number', respectively. (On a machine whose floating-point arithmetic does not follow IEEE 754, these values will probably not work as expected.) When writing these values as constants in an SQL command, you must put quotes around them, for example UPDATE table SET x = 'Infinity'. On input, these strings are recognized in a case-insensitive manner. -Infinity
NaN
Note: IEEE754 specifies that NaN should not compare equal to any other floating-point value (including NaN). In order to allow floating-point values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.
PostgreSQL also supports the SQL-standard notations float and float(p) for specifying inexact numeric types. Here, p specifies the minimum acceptable precision in binary digits. PostgreSQL accepts float(1) to float(24) as selecting the real type, while float(25) to float(53) select double precision. Values of p outside the allowed range draw an error. float with no precision specified is taken to mean double precision.
Note: Prior to PostgreSQL 7.4, the precision in float(p) was taken to mean so many decimal digits. This has been corrected to match the SQL standard, which specifies that the precision is measured in binary digits. The assumption that real and double precision have exactly 24 and 53 bits in the mantissa respectively is correct for IEEE-standard floating point implementations. On non-IEEE platforms it might be off a little, but for simplicity the same ranges of p are used on all platforms.
8.1.4. Serial Types
The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:
is equivalent to specifying:
Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as 'owned by' the column, so that it will be dropped if the column or table is dropped.
Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to have a unique constraint or be a primary key, it must now be specified, just like any other data type.
To insert the next value of the sequence into the serial column, specify that the serial column should be assigned its default value. This can be done either by excluding the column from the list of columns in the INSERT statement, or through the use of the DEFAULT key word.
The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work the same way, except that they create a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table.
The sequence created for a serial column is automatically dropped when the owning column is dropped. You can drop the sequence without dropping the column, but this will force removal of the column default expression.
We hope you find this tutorial helpful. In addition to guides like this one, we provide simple cloud infrastructure for developers. Learn more →
Introduction
The relational data model, which organizes data in tables of rows and columns, predominates in database management tools. Today there are other data models, including NoSQL and NewSQL, but relational database management systems (RDBMSs) remain dominant for storing and managing data worldwide.
This article compares and contrasts three of the most widely implemented open-source RDBMSs: SQLite, MySQL, and PostgreSQL. Specifically, it will explore the data types that each RDBMS uses, their advantages and disadvantages, and situations where they are best optimized.
A Bit About Database Management Systems
Databases are logically modelled clusters of information, or data. A database management system (DBMS), on the other hand, is a computer program that interacts with a database. A DBMS allows you to control access to a database, write data, run queries, and perform any other tasks related to database management. Although database management systems are often referred to as 'databases,' the two terms are not interchangeable. A database can be any collection of data, not just one stored on a computer, while a DBMS is the software that allows you to interact with a database.
All database management systems have an underlying model that structures how data is stored and accessed. A relational database management system is a DBMS that employs the relational data model. In this model, data are organized into tables, which in the context of RDBMSs are more formally referred to as relations. A relation is a set of tuples, or rows in a table, with each tuple sharing a set of attributes, or columns:
Most relational databases use structured query language (SQL) to manage and query data. However, many RDBMSs use their own particular dialect of SQL, which may have certain limitations or extensions. These extensions typically include extra features that allow users to perform more complex operations than they otherwise could with standard SQL.
Note: The term 'standard SQL' comes up several times throughout this guide. SQL standards are jointly maintained by the American National Standards Institute (ANSI), the International Organization for Standardization (ISO), and the International Electrotechnical Commission (IEC). Whenever this article mentions 'standard SQL' or 'the SQL standard,' it's referring to the current version of the SQL standard published by these bodies.
It should be noted that the full SQL standard is large and complex: full core SQL:2011 compliance requires 179 features. Because of this, most RDBMSs don't support the entire standard, although some do come closer to full compliance than others.
Each column is assigned a data type which dictates what kind of entries are allowed in that column. Different RDBMSs implement different data types, which aren't always directly interchangeable. Some common data types include dates, strings, integers, and Booleans.
Numeric data types can either be signed, meaning they can represent both positive and negative numbers, or unsigned, which means they can only represent positive numbers. For example, MySQL's
tinyint
data type can hold 8 bits of data, which equates to 256 possible values. The signed range of this data type is from -128 to 127, while the unsigned range is from 0 to 255.Sometimes, a database administrator will impose a constraint on a table to limit what values can be entered into it. A constraint typically applies to one particular column, but some constraints can also apply to an entire table. Here are some constraints that are commonly used in SQL:
UNIQUE
: Applying this constraint to a column ensures that no two entries in that column are identical.NOT NULL
: This constraint ensures that a column doesn’t have anyNULL
entries.PRIMARY KEY
: A combination ofUNIQUE
andNOT NULL
, thePRIMARY KEY
constraint ensures that no entry in the column isNULL
and that every entry is distinct.FOREIGN KEY
: AFOREIGN KEY
is a column in one table that refers to thePRIMARY KEY
of another table. This constraint is used to link two tables together: entries to theFOREIGN KEY
column must already exist in the parentPRIMARY KEY
column for the write process to succeed.CHECK
: This constraint limits the range of values that can be entered into a column. For example, if your application is intended only for residents of Alaska, you could add aCHECK
constraint on a ZIP code column to only allow entries between 99501 and 99950.DEFAULT
: This provides a default value for a given column. Unless another value is specified, SQLite enters the default value automatically.INDEX
: Used to help retrieve data from a table more quickly, this constraint is similar to an index in a textbook: instead of having to review every entry in a table, a query only has to review entries from the indexed column to find the desired results.
If you'd like to learn more about database management systems, check out our article on Understanding SQL and NoSQL Databases and Different Database Models.
Now that we've covered relational database management systems generally, let's move onto the first of the three open-source relational databases this article will cover: SQLite.
SQLite
SQLite is a self-contained, file-based, and fully open-source RDBMS known for its portability, reliability, and strong performance even in low-memory environments. Its transactions are ACID-compliant, even in cases where the system crashes or undergoes a power outage.
The SQLite project's website describes it as a 'serverless' database. Most relational database engines are implemented as a server process in which programs communicate with the host server through an interprocess communication that relays requests. With SQLite, though, any process that accesses the database reads from and writes to the database disk file directly. This simplifies SQLite's setup process, since it eliminates any need to configure a server process. Likewise, there's no configuration necessary for programs that will use the SQLite database: all they need is access to the disk.
SQLite is free and open-source software, and no special license is required to use it. However, the project does offer several extensions — each for a one-time fee — that help with compression and encryption. Additionally, the project offers various commercial support packages, each for an annual fee.
SQLite's Supported Data Types
SQLite allows a variety of data types, organized into the following storage classes:
Data Type | Explanation |
---|---|
null | Includes any NULL values. |
integer | Signed integers, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. |
real | Real numbers, or floating point values, stored as 8-byte floating point numbers. |
text | Text strings stored using the database encoding, which can either be UTF-8, UTF-16BE or UTF-16LE. |
blob | Any blob of data, with every blob stored exactly as it was input. |
In the context of SQLite, the terms 'storage class' and 'data type' are considered interchangeable. If you'd like to learn more about SQLite's data types and SQLite type affinity, check out SQLite's official documentation on the subject.
Advantages of SQLite
- Small footprint: As its name implies, the SQLite library is very lightweight. Although the space it uses varies depending on the system where it's installed, it can take up less than 600KiB of space. Additionally, it’s fully self-contained, meaning there aren't any external dependencies you have to install on your system for SQLite to work.
- User-friendly: SQLite is sometimes described as a 'zero-configuration' database that's ready for use out of the box. SQLite doesn't run as a server process, which means that it never needs to be stopped, started, or restarted and doesn't come with any configuration files that need to be managed. These features help to streamline the path from installing SQLite to integrating it with an application.
- Portable: Unlike other database management systems, which typically store data as a large batch of separate files, an entire SQLite database is stored in a single file. This file can be located anywhere in a directory hierarchy, and can be shared via removable media or file transfer protocol.
Disadvantages of SQLite
- Limited concurrency: Although multiple processes can access and query an SQLite database at the same time, only one process can make changes to the database at any given time. This means SQLite supports greater concurrency than most other embedded database management systems, but not as much as client/server RDBMSs like MySQL or PostgreSQL.
- No user management: Database systems often come with support for users, or managed connections with predefined access privileges to the database and tables. Because SQLite reads and writes directly to an ordinary disk file, the only applicable access permissions are the typical access permissions of the underlying operating system. This makes SQLite a poor choice for applications that require multiple users with special access permissions.
- Security: A database engine that uses a server can, in some instances, provide better protection from bugs in the client application than a serverless database like SQLite. For example, stray pointers in a client cannot corrupt memory on the server. Also, because a server is a single persistent process, a client-server database cancontrol data access with more precision than a serverless database, allowing for more fine-grained locking and better concurrency.
When To Use SQLite
- Embedded applications: SQLite is a great choice of database for applications that need portability and don't require future expansion. Examples include single-user local applications and mobile applications or games.
- Disk access replacement: In cases where an application needs to read and write files to disk directly, it can be beneficial to use SQLite for the additional functionality and simplicity that comes with using SQL.
- Testing: For many applications it can be overkill to test their functionality with a DBMS that uses an additional server process. SQLite has an in-memory mode which can be used to run tests quickly without the overhead of actual database operations, making it an ideal choice for testing.
When Not To Use SQLite
- Working with lots of data: SQLite can technically support a database up to 140TB in size, as long as the disk drive and filesystem also support the database's size requirements. However, the SQLite website recommends that any database approaching 1TB be housed on a centralized client-server database, as an SQLite database of that size or larger would be difficult to manage.
- High write volumes: SQLite allows only one write operation to take place at any given time, which significantly limits its throughput. If your application requires lots of write operations or multiple concurrent writers, SQLite may not be adequate for your needs.
- Network access is required: Because SQLite is a serverless database, it doesn't provide direct network access to its data. This access is built into the application, so if the data in SQLite is located on a separate machine from the application it will require a high bandwidth engine-to-disk link across the network. This is an expensive, inefficient solution, and in such cases a client-server DBMS may be a better choice.
MySQL
According to the DB-Engines Ranking, MySQL has been the most popular open-source RDBMS since the site began tracking database popularity in 2012. It is a feature-rich product that powers many of the world's largest websites and applications, including Twitter, Facebook, Netflix, and Spotify. Getting started with MySQL is relatively straightforward, thanks in large part to its exhaustive documentation and large community of developers, as well as the abundance of MySQL-related resources online.
MySQL was designed for speed and reliability, at the expense of full adherence to standard SQL. The MySQL developers continually work towards closer adherence to standard SQL, but it still lags behind other SQL implementations. It does, however, come with various SQL modes and extensions that bring it closer to compliance. Unlike applications using SQLite, applications using a MySQL database access it through a separate daemon process. Because the server process stands between the database and other applications, it allows for greater control over who has access to the database.
MySQL has inspired a wealth of third-party applications, tools, and integrated libraries that extend its functionality and help make it easier to work with. Some of the more widely-used of these third-party tools are phpMyAdmin, DBeaver, and HeidiSQL.
MySQL's Supported Data Types
MySQL's data types can be organized into three broad categories: numeric types, date and time types, and string types.
Numeric types:
Data Type | Explanation |
---|---|
tinyint | A very small integer. The signed range for this numeric data type is -128 to 127, while the unsigned range is 0 to 255. |
smallint | A small integer. The signed range for this numeric type is -32768 to 32767, while the unsigned range is 0 to 65535. |
mediumint | A medium-sized integer. The signed range for this numeric data type is -8388608 to 8388607, while the unsigned range is 0 to 16777215. |
int or integer | A normal-sized integer. The signed range for this numeric data type is -2147483648 to 2147483647, while the unsigned range is 0 to 4294967295. |
bigint | A large integer. The signed range for this numeric data type is -9223372036854775808 to 9223372036854775807, while the unsigned range is 0 to 18446744073709551615. |
float | A small (single-precision) floating-point number. |
double , double precision , or real | A normal sized (double-precision) floating-point number. |
dec , decimal , fixed , or numeric | A packed fixed-point number. The display length of entries for this data type is defined when the column is created, and every entry adheres to that length. |
bool or boolean | A Boolean is a data type that only has two possible values, usually either true or false . |
bit | A bit value type for which you can specify the number of bits per value, from 1 to 64. |
Date and time types:
Data Type | Explanation |
---|---|
date | A date, represented as YYYY-MM-DD . |
datetime | A timestamp showing the date and time, displayed as YYYY-MM-DD HH:MM:SS . |
timestamp | A timestamp indicating the amount of time since the Unix epoch (00:00:00 on January 1, 1970). |
time | A time of day, displayed as HH:MM:SS . |
year | A year expressed in either a 2 or 4 digit format, with 4 digits being the default. |
String types:
Data Type | Explanation |
---|---|
char | A fixed-length string; entries of this type are padded on the right with spaces to meet the specified length when stored. |
varchar | A string of variable length. |
binary | Similar to the char type, but a binary byte string of a specified length rather than a nonbinary character string. |
varbinary | Similar to the varchar type, but a binary byte string of a variable length rather than a nonbinary character string. |
blob | A binary string with a maximum length of 65535 (2^16 - 1) bytes of data. |
tinyblob | A blob column with a maximum length of 255 (2^8 - 1) bytes of data. |
mediumblob | A blob column with a maximum length of 16777215 (2^24 - 1) bytes of data. |
longblob | A blob column with a maximum length of 4294967295 (2^32 - 1) bytes of data. |
text | A string with a maximum length of 65535 (2^16 - 1) characters. |
tinytext | A text column with a maximum length of 255 (2^8 - 1) characters. |
mediumtext | A text column with a maximum length of 16777215 (2^24 - 1) characters. |
longtext | A text column with a maximum length of 4294967295 (2^32 - 1) characters. |
enum | An enumeration, which is a string object that takes a single value from a list of values that are declared when the table is created. |
set | Similar to an enumeration, a string object that can have zero or more values, each of which must be chosen from a list of allowed values that are specified when the table is created. |
Advantages of MySQL
- Popularity and ease of use: As one of the world's most popular database systems, there's no shortage of database administrators who have experience working with MySQL. Likewise, there's an abundance of documentation in print and online on how to install and manage a MySQL database, as well as a number of third-party tools — such as phpMyAdmin — that aim to simplify the process of getting started with the database.
- Security: MySQL comes installed with a script that helps you to improve the security of your database by setting the installation's password security level, defining a password for the root user, removing anonymous accounts, and removing test databases that are, by default, accessible to all users. Also, unlike SQLite, MySQL does support user management and allows you to grant access privileges on a user-by-user basis.
- Speed: By choosing not to implement certain features of SQL, the MySQL developers were able to prioritize speed. While more recent benchmark tests show that other RDBMSs like PostgreSQL can match or at least come close to MySQL in terms of speed, MySQL still holds a reputation as an exceedingly fast database solution.
- Replication: MySQL supports a number of different types of replication, which is the practice of sharing information across two or more hosts to help improve reliability, availability, and fault-tolerance. This is helpful for setting up a database backup solution or horizontally scaling one's database.
Disadvantages of MySQL
- Known limitations: Because MySQL was designed for speed and ease of use rather than full SQL compliance, it comes with certain functional limitations. For example, it lacks support for
FULL JOIN
clauses. - Licensing and proprietary features: MySQL is dual-licensed software, with a free and open-source community edition licensed under GPLv2 and several paid commercial editions released under proprietary licenses. Because of this, some features and plugins are only available for the propriety editions.
- Slowed development: Since the MySQL project was acquired by Sun Microsystems in 2008, and later by Oracle Corporation in 2009, there have been complaints from users that the development process for the DBMS has slowed down significantly, as the community no longer has the agency to quickly react to problems and implement changes.
When To Use MySQL
- Distributed operations: MySQL's replication support makes it a great choice for distributed database setups like primary-secondary or primary-primary architectures.
- Websites and web applications: MySQL powers many websites and applications across the internet. This is, in large part, thanks to how easy it is to install and set up a MySQL database, as well as its overall speed and scalability in the long run.
- Expected future growth: MySQL's replication support can help facilitate horizontal scaling. Additionally, it's a relatively straightforward process to upgrade to a commercial MySQL product, like MySQL Cluster, which supports automatic sharding, another horizontal scaling process.
When Not To Use MySQL
- SQL compliance is necessary: Since MySQL does not try to implement the full SQL standard, this tool is not completely SQL compliant. If complete or even near-complete SQL compliance is a must for your use case, you may want to use a more fully compliant DBMS.
- Concurrency and large data volumes: Although MySQL generally performs well with read-heavy operations, concurrent read-writes can be problematic. If your application will have many users writing data to it at once, another RDBMS like PostgreSQL might be a better choice of database.
PostgreSQL
PostgreSQL, also known as Postgres, bills itself as 'the most advanced open-source relational database in the world.' It was created with the goal of being highly extensible and standards compliant. PostgreSQL is an object-relational database, meaning that although it's primarily a relational database it also includes features — like table inheritance and function overloading — that are more often associated with object databases.
Postgres is capable of efficiently handling multiple tasks at the same time, a characteristic known as concurrency. It achieves this without read locks thanks to its implementation of Multiversion Concurrency Control (MVCC), which ensures the atomicity, consistency, isolation, and durability of its transactions, also known as ACID compliance.
PostgreSQL isn't as widely used as MySQL, but there are still a number of third-party tools and libraries designed to simplify working with with PostgreSQL, including pgAdmin and Postbird.
PostgreSQL's Supported Data Types
PostgreSQL supports numeric, string, and date and time data types like MySQL. In addition, it supports data types for geometric shapes, network addresses, bit strings, text searches, and JSON entries, as well as several idiosyncratic data types.
Numeric types:
Data Type | Explanation |
---|---|
bigint | A signed 8 byte integer. |
bigserial | An autoincrementing 8 byte integer. |
double precision | An 8 byte double precision floating-point number. |
integer | A signed 4 byte integer. |
numeric or decimal | An number of selectable precision, recommended for use in cases where exactness is crucial, such as monetary amounts. |
real | A 4 byte single precision floating-point number. |
smallint | A signed 2 byte integer. |
smallserial | An autoincrementing 2 byte integer. |
serial | An autoincrementing 4 byte integer. |
Character types:
Data Type | Explanation |
---|---|
character | A character string with a specified fixed length. |
character varying or varchar | A character string with a variable but limited length. |
text | A character string of a variable, unlimited length. |
![Postgres Compare Double Precision Postgres Compare Double Precision](/uploads/1/2/3/7/123760102/655225397.png)
Date and time types:
Data Type | Explanation |
---|---|
date | A calendar date consisting of the day, month, and year. |
interval | A time span. |
time or time without time zone | A time of day, not including the time zone. |
time with time zone | A time of day, including the time zone. |
timestamp or timestamp without time zone | A date and time, not including the time zone. |
timestamp with time zone | A date and time, including the time zone. |
Geometric types:
Data Type | Explanation |
---|---|
box | A rectangular box on a plane. |
circle | A circle on a plane. |
line | An infinite line on a plane. |
lseg | A line segment on a plane. |
path | A geometric path on a plane. |
point | A geometric point on a plane. |
polygon | A closed geometric path on a plane. |
Network address types:
Data Type | Explanation |
---|---|
cidr | An IPv4 or IPv6 network address. |
inet | An IPv4 or IPv6 host address. |
macaddr | A Media Access Control (MAC) address. |
Bit string types:
Data Type | Explanation |
---|---|
bit | A fixed-length bit string. |
bit varying | A variable-length bit string. |
Text search types:
Data Type | Explanation |
---|---|
tsquery | A text search query. |
tsvector | A text search document. |
JSON types:
Data Type | Explanation |
---|---|
json | Textual JSON data. |
jsonb | Decomposed binary JSON data. |
Other data types:
Data Type | Explanation |
---|---|
boolean | A logical Boolean, representing either true or false . |
bytea | Short for 'byte array', this type is used for binary data. |
money | An amount of currency. |
pg_lsn | A PostgreSQL Log Sequence Number. |
txid_snapshot | A user-level transaction ID snapshot. |
uuid | A universally unique identifier. |
xml | XML data. |
Advantages of PostgreSQL
- SQL compliance: More so than SQLite or MySQL, PostgreSQL aims to closely adhere to SQL standards. According to the official PostgreSQL documentation, PostgreSQL supports 160 out of the 179 features required for full core SQL:2011 compliance, in addition to a long list of optional features.
- Open-source and community-driven: A fully open-source project, PostgreSQL's source code is developed by a large and devoted community. Similarly, the Postgres community maintains and contributes to numerous online resources that describe how to work with the DBMS, including the official documentation, the PostgreSQL wiki, and various online forums.
- Extensible: Users can extend PostgreSQL programmatically and on the fly through its catalog-driven operation and its use of dynamic loading. One can designate an object code file, such as a shared library, and PostgreSQL will load it as necessary.
Disadvantages of PostgreSQL
- Memory performance: For every new client connection, PostgreSQL forks a new process. Each new process is allocated about 10MB of memory, which can add up quickly for databases with lots of connections. Accordingly, for simple read-heavy operations, PostgreSQL is typically less performant than other RDBMSs, like MySQL.
- Popularity: Although more widely used in recent years, PostgreSQL historically lagged behind MySQL in terms of popularity. One consequence of this is that there are still fewer third-party tools that can help to manage a PostgreSQL database. Similarly, there aren't as many database administrators with experience managing a Postgres database compared to those with MySQL experience.
When To Use PostgreSQL
- Data integrity is important: PostgreSQL has been fully ACID-compliant since 2001 and implements multiversion currency control to ensure that data remains consistent, making it a strong choice of RDBMS when data integrity is critical.
- Integration with other tools: PostgreSQL is compatible with a wide array of programming languages and platforms. This means that if you ever need to migrate your database to another operating system or integrate it with a specific tool, it will likely be easier with a PostgreSQL database than with another DBMS.
- Complex operations: Postgres supports query plans that can leverage multiple CPUs in order to answer queries with greater speed. This, coupled with its strong support for multiple concurrent writers, makes it a great choice for complex operations like data warehousing and online transaction processing.
When Not To Use PostgreSQL
- Speed is imperative: At the expense of speed, PostgreSQL was designed with extensibility and compatibility in mind. If your project requires the fastest read operations possible, PostgreSQL may not be the best choice of DBMS.
- Simple setups: Because of its large feature set and strong adherence to standard SQL, Postgres can be overkill for simple database setups. For read-heavy operations where speed is required, MySQL is typically a more practical choice.
- Complex replication: Although PostgreSQL does provide strong support for replication, it's still a relatively new feature and some configurations — like a primary-primary architecture — are only possible with extensions. Replication is a more mature feature on MySQL and many users see MySQL's replication to be easier to implement, particularly for those who lack the requisite database and system administration experience.
Conclusion
Today, SQLite, MySQL, and PostgreSQL are the three most popular open-source relational database management systems in the world. Each has its own unique features and limitations, and excels in particular scenarios. There are a quite a few variables at play when deciding on an RDBMS, and the choice is rarely as simple as picking the fastest one or the one with the most features. The next time you're in need of a relational database solution, be sure to research these and other tools in depth to find the one that best suits your needs.
If you'd like to learn more about SQL and how to use it to manage a relational database, we encourage you to refer to our How To Manage an SQL Database cheat sheet. On the other hand, if you'd like to learn about non-relational (or NoSQL) databases, check out our Comparison Of NoSQL Database Management Systems.
References
Hmm.. The Bug1010992DoubleValueSupport test seems to make an assert that is too strict, if I understand floating point properly. In general, when comparing floats one always sets a 'tolerance' (or 'epsilon') value, within which differences are tolerated - see the section on float comparison in the NUnit docs. Among other things, this is because floating point arithmetic does not always yield the expected result (e.g. (x * 0.5) / 0.5 isn't necessarily exactly equal to x).
![Postgresql float precision Postgresql float precision](/uploads/1/2/3/7/123760102/740226121.png)
In other words, I think it's perfectly acceptable for Bug1010992DoubleValueSupport to fail as it's currently written.
I guess we're in agreement to stop setting extra_float_digits in Npgsql 3.0, and leave it up to the user, right? @franciscojunior, it's possible to add this as a connection string parameter but I'm not sure about the usefulness - why provide it for this parameter and not for any/all other parameters Postgresql supports? Also, connection string parameters are important for connection pooling - connections with different extra_float_digits won't be reused by the pool, although they are in essence the same..
I'm not really that against an extra_float_digits connstring param, I'm just not sure it make that much sense.. Let me know what you think.
a_horse_with_no_name319k5050 gold badges491491 silver badges594594 bronze badges
yannicksoldatiyannicksoldati
closed as not a real question by Michael Petrotta, Frank Heikens, Pere Villega, J0HN, WillAug 31 '11 at 14:54
It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.
2 Answers
Floating-point representations like double precision fields in pg are by their very nature subject to rounding errors. This paper gives a good mathematical introduction.
How you deal with these rounding errors depends on a bit on your application, and varies between:
- just living with them eg by sort of ignoring them - format that result as %.2f and the error will become invisible. Just make sure you understand all the consequences of that choice!!
- switching datatypes to something that by design is not subject to rounding issues - check out pg's money type and the exact numeric types.
The latter should be the preferred approach especially in contexts where your application handles money.
fvufvu29.4k55 gold badges4949 silver badges7070 bronze badges
You're getting those results because
product_qty*100
has different IEEE-754 binary representation than round(product_qty * 100)
:It's easy to see that 55 should be represented as 1,71875 * 25 (as in with round), but due to floating point arithmetic
product_qty*100
is slightly different.If you want to still live with floating point number, then I recommend to read 'What every computer scientist should know about floating-point arithmetic' paper, or just switch to
Grzegorz SzpetkowskiGrzegorz Szpetkowskinumeric
type with arbitrary precision.31k44 gold badges6969 silver badges115115 bronze badges