Pirate Wiki

Toggle Theme

Data Types ​

PostgreSQL has one of the richest sets of native data types of any relational database. Choosing the right data type ensures data integrity, saves disk space, and improves query performance.

Numeric Types ​

  • INT / INTEGER: Standard 4-byte integer. Ranges from -2 billion to +2 billion.
  • BIGINT: 8-byte integer. Use this for highly active primary keys.
  • SMALLINT: 2-byte integer.
  • DECIMAL(p, s) / NUMERIC(p, s): Exact precision numbers. p is total digits, s is digits after the decimal. Ideal for financial data (e.g., NUMERIC(10, 2)).
  • REAL / DOUBLE PRECISION: Inexact, variable-precision (floating-point). Faster than NUMERIC but can introduce rounding errors.

Character Types ​

  • VARCHAR(n): Variable-length string with a maximum length of n.
  • CHAR(n): Fixed-length string. It is padded with spaces if the input is shorter than n. (Rarely used in modern applications).
  • TEXT: Variable-length string with unlimited length.

PostgreSQL Specifics

In PostgreSQL, there is no performance difference among VARCHAR(n), VARCHAR, and TEXT. Under the hood, they use the same storage mechanism. If you don't have a strict requirement to limit string length, just use TEXT.

Date/Time Types ​

  • DATE: Stores the date (Year, Month, Day).
  • TIME: Stores the time of day.
  • TIMESTAMP: Stores both date and time, without time zone.
  • TIMESTAMPTZ: Stores date and time with time zone.

IMPORTANT

Always use TIMESTAMPTZ for storing precise moments in time. It converts the input to UTC for storage and converts it back to the client's local time zone on retrieval, preventing massive headaches in global applications.

Boolean Type ​

  • BOOLEAN / BOOL: Can be TRUE, FALSE, or NULL. PostgreSQL is generous with input: 't', 'true', 'y', 'yes', '1' all evaluate to TRUE.

Advanced PostgreSQL Types ​

PostgreSQL shines with its advanced types:

  • UUID: Universally Unique Identifier (128-bit). Excellent for distributed system primary keys to avoid collisions.
  • JSON / JSONB: Stores JSON data. JSONB stores it in a decomposed binary format. It is slightly slower to write but much faster to process, as it supports indexing. Always prefer JSONB.
  • Arrays: You can define a column as an array of any valid data type (e.g., TEXT[] or INTEGER[]).

For an exhaustive list, refer to the Official PostgreSQL Data Types Documentation.