Different PostgreSQL Data Types
PostgreSQL is an open-source relational database management system known for its robustness and extensibility. This also means PostgreSQL offers a variety of data types for users.
In this post, we'll look at the various data types supported by PostgreSQL. PostgreSQL supports the following data types:
- Character Types [ such as char, varchar, and text]
- Numeric Types [ such as integer and floating-point numbers]
- Temporal Types [ such as date, time, timestamp, and interval]
- UUID [ for storing UUID (Universally Unique Identifiers) ]
- Array [ for storing array strings, numbers, etc.]
- JSON [ stores JSON data]
- hstore [ stores key-value pair]
- Special Types [ such as network address and geometric data]
- Monetary The monetary data type stores a numeric amount of money with fixed fractional precision
- Bit String The Bit string type stores strings of 1's and 0's
- Object Identifier
- Pseudo Types
Now let’s get a quick review on how these different data types work and differ among others.
The “bool” or ”boolean” keyword on PostgreSQL is necessary to initialize a Boolean data type. They can contain true, false, and null values. Boolean data types are stored in the database with these conditions:
- 1, yes, y, t, true values are converted to true
- 0, no, false, f values are converted to false
The boolean data are transformed and returned as follows when they are queried:
- t to true
- f to false
- space to null
PostgreSQL has three character data types namely, CHAR(n), VARCHAR(n), and TEXT.
- CHAR(n) is used for data(string) with a fixed length of characters with padded spaces. In case the length of the string is smaller than the value of “n”, then the rest of the remaining spaces are automatically padded. Similarly for a string with a length greater than the value of “n”, PostgreSQL throws an error.
- VARCHAR(n) is the variable-length character string. Similar to CHAR(n), it can store “n” length data. But unlike CHAR(n) no padding is done in case the data length is smaller than the value of “n”.
- TEXT is the variable-length character string. It can store data with unlimited length.
In PostgreSQL, there are two different kinds of numbers: integers and floating-point numbers.
- Small integer (SMALLINT) has a range -32, 768 to 32, 767 and has a size of 2-byte.
- Integer (INT) has a range -2, 147, 483, 648 to 2, 147, 483, 647 and has a size of 4-byte.
- Serial (SERIAL) works similar to the integers except these are automatically generated in the columns by PostgreSQL.
2. Floating-point number:
- float(n) is used for floating-point numbers with n precision and can have a maximum of 8-bytes.
- float8 or real is used to represent 4-byte floating-point numbers.
- A real number N(d,p) with d number of digits and p number of decimal points after, are part of numeric or numeric(d, p). These are generally very precise.
Temporal data type:
Date-and-time information is stored using this data type. There are 5 temporal data types in PostgreSQL:
- DATE is used to store the dates only.
- TIME is used to store the time of day values.
- TIMESTAMP is used to store both date and time values.
- TIMESTAMPTZ is used to store a timezone-aware timestamp data type.
- INTERVAL is used to store periods of time.
In PostgreSQL, an array column can be used to hold an array of letters, an array of characters, or other kinds of data. It could be handy for preserving data, such as days of months, a year, or only a week, etc.
JSON and JSONB (Binary JSON), are two forms of JSON supported by PostgreSQL. When a query uses the JSON data type, it stores plain JSON data, which is parsed each time. The JSONB data type, on the other hand, is used to store JSON data in binary form. On the one hand, this permits indexing of table data, which speeds up data querying, but slows down the data insertion process as it supports indexing of table data.
The RFC 4122-defined Universal Unique Identifiers (UUIDs) can be stored using the UUID data type. The UUID values can be used to conceal sensitive data that is accessible to the public, such as the id values in URLs, and guarantee a higher level of uniqueness than SERIAL.
Unique Universal Identifiers is the name of the concept. These are employed to assign an exclusive ID to information that is singular across the database. The UUID of the data defined by RFC 4122 is stored using the UUID data type. These work better when compared to SERIAL data types in terms of uniqueness and are frequently used to protect sensitive data, such as credit card information.
Special data types:
PostgreSQL provides a few different data types that are connected to networks or geometrics in addition to the primitive data types. The following is a list of these unique data types:
- box: It is used to store rectangular boxes.
- point: It is used to store geometric pairs of numbers.
- lseg: It is used to store line segments.
- point: It is used to store geometric pairs of numbers.
- polygon: It is used to store closed geometrics.
- inet: It is used to store an IP4 address.
- macaddr: It is used to store MAC addresses.
The monetary data type stores a numeric amount of money with fixed fractional precision. This type stores up to 8 bytes of data with a range of -92233720368547758.08 to +92233720368547758.07 and use numeric integer and bigint data types as values.
PostgreSQL can save variable-length binary strings as the bytes data type, taking 1 or 4 bytes plus the size of the actual binary string.
Bit string type stores strings of 1's and 0's, used to store or visualize bit masks:
Stores a bit string with a fixed length of n characters.
Stores a bit string of a varying length, up to n characters.
PostgreSQL allows you to save XML data as an XML data type using the XMLPARSE function:
- [document name]: A singly-rooted XML document.
- [XML content]: Valid XML value
- WELLFORMED: This option guarantees that [document name] or [XML content] resolve to a well-formed XML document. Use it only when you don't want the database to check if the input is well-formed.
Composite data types allow you to use a row or record of a table as a data element. Similar to array data types, you can also declare, search, and modify composite values
PostgreSQL uses object identifiers as primary key systems when performing specialized input and output operations:
Numeric object identifier.
Function with argument types.
Operator with argument types.
Data type name.
Text search configuration.
Text search dictionary.
Pseudo types are a collection of special entries for declaring a function's argument or result type:
Function accepts any input data type.
Function accepts any data type.
Function accepts any array data type.
Function accepts any non-array data type.
Function accepts any enumerated data type.
Function accepts any range data type.
Function accepts or return a null-terminated C string.
Function accepts or return server-internal data type.
Function returns language handler.
A foreign-data wrapper handler returns fdw_handler.
Finds a function that returns an unspecified row type.
A trigger function returns the trigger.
Function returns no value.
A user-defined data type called a domain is one that is based on an underlying type. It may also include restrictions that limit the set of possible values for it to take to a subset of what the underlying type would permit. For example, any operator or function that can be applied to the underlying type will operate on the domain type. Otherwise, it behaves like the underlying type. The underlying type may be an enum type, array type, composite type, range type, built-in or user-defined base type, or it may belong to another domain.
For example, we could create a domain over integers that accepts only positive integers:
CREATE DOMAIN posint AS integer CHECK (VALUE > 0);
CREATE TABLE mytable (id posint);
INSERT INTO mytable VALUES(1); -- works
INSERT INTO mytable VALUES(-1); -- fails
When an operator or function of the underlying type is applied to a domain value, the domain is automatically down-cast to the underlying type. Thus, for example, the result of mytable.id - 1 is considered to be of type integer, not posint. We could write (mytable.id - 1)::posint to cast the result back to posint, causing the domain's constraints to be rechecked. In this case, that would result in an error if the expression had been applied to an id value of 1. Assigning a value of the underlying type to a field or variable of the domain type is allowed without writing an explicit cast, but the domain's constraints will be checked.
After reading this article, you should have a solid understanding of the different PostgreSQL data types.
MongoDB vs PostgreSQL: A Comparison
Installing PostgreSQL on a MacOS