PostgreSQL - data types

Boolean

• True
• False

Boolean constants can be represented in SQL queries by the SQL key words TRUE, FALSE, and NULL.

The datatype input function for type boolean accepts these string representations for the “true” state: true, yes, on, 1 and these representations for the “false” state: false, no, off, 0 Unique prefixes of these strings are also accepted, for example t or n. Leading or trailing whitespace is ignored, and case does not matter.

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est

SELECT * FROM test1 WHERE a;
 a |    b
---+---------
 t | sic est

Character

• character varying(n), varchar(n): variable-length with limit
• character(n), char(n) fixed-length: blank padded
• text: variable unlimited length

The notations varchar(n) and char(n) are aliases for character varying(n) and character(n), respectively.

Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way.

Numeric

Integer Types

• smallint: small-range integer (2 bytes)
• integer: typical choice for integer (4 bytes)
• bigint: large-range integer (8 bytes)

NOTE: 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 is designed to be used when the range of the integer type is insufficient.

Arbitrary Precision Numbers

The type numeric can store numbers with a very large number of digits. It is especially recommended for storing monetary amounts and other quantities where exactness is required. Calculations with numeric values yield exact results where possible, e.g., addition, subtraction, multiplication. However, calculations on numeric values are very slow compared to the integer types, or to the floating-point types described in the next section.

The types decimal and numeric are equivalent. Both types are part of the SQL standard.

• numeric: user-specified precision, exact
• decimal: user-specified precision, exact

Serial Type

The data types smallserial, 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).

• smallserial: small autoincrementing integer (2 bytes)
• serial: autoincrementing integer (4 bytes)
• bigserial: large autoincrementing integer (8 bytes)

Temporal

• date
• time
• timestamp
• interval

UUID

The data type uuid stores Universally Unique Identifiers (UUID).

This identifier is a 128-bit quantity that is generated by an algorithm chosen to make it very unlikely that the same identifier will be generated by anyone else in the known universe using the same algorithm. Therefore, for distributed systems, these identifiers provide a better uniqueness guarantee than sequence generators, which are only unique within a single database.

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

Array

• integer[]
• text[]

An array data type is named by appending square brackets ([]) to the data type name of the array elements.

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);
INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

JSON

• json
• jsonb

JSON data types are for storing JSON (JavaScript Object Notation) data. Such data can also be stored as text, but the JSON data types have the advantage of enforcing that each stored value is valid according to the JSON rules. There are also assorted JSON-specific functions and operators available for data stored in these data types.

The json and jsonb data types accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

Reference

• Data Types