postgresql-data-types-cheatsheet.md

PostgreSQL Data Types Cheatsheet

Here's a list with some of the most common data types in PostgreSQL.

Boolean

• BOOLEAN - a Boolean data type can hold one of three possible values: true, false, or null.

Character Types:

• CHAR(n) - fixed-length character with space padded.
• VARCHAR(n) - variable-length strings.
• TEXT - variable-length without a limit.

Integer numbers:

• SMALLINT - is a 2-byte signed integer (allows to store numbers from -32,768 to 32,767)
• INT - is a 4-byte signed integer (allows to store numbers from -2,147,483,648 to 2,147,483,647).
• SERIAL - is the same as integer except that PostgreSQL will automatically generate and populate values into the SERIAL column (it is often used to generate unique ids).

Decimal numbers:

• NUMERIC(precision, scale) - used for storing exact numeric values with a user-defined precision and scale.
  • For example, NUMERIC(10, 2) allows up to 10 total digits, with 2 digits after the decimal point.

Temporal data types:

• DATE - stores the dates only.
• TIME - stores the time of day values.
• TIMESTAMP - stores both date and time values.
• TIMESTAMPTZ - similar to timestamp but it also includes the timezone.
• INTERVAL - stores periods.

Custom data types

• PostgreSQL also allows you to create your own data types. Here's an example:

  CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Some other data types:

• Arrays - allows you to store multiple values of the same data type in a single column (for example, VARCHAR(5)[] means an array of strings where each string can have a maximum length of 5 characters)
• JSON - allows to store data in json format.
• UUID - allows you to store Universal Unique Identifiers, 36-character alphanumeric strings that are expected to be unique (more info here)

More info:

• https://neon.tech/postgresql/postgresql-tutorial/postgresql-data-types