Postgres Types

PostgreSQL extends the ANSI SQL type system with additional data types, optimized storage, and specialized casting mechanisms. While it implements many ANSI SQL standard types, it also introduces unique types and features that differentiate it from other relational databases.

Numeric Types

PostgreSQL supports both ANSI SQL numeric types and additional optimizations.

• ANSI SQL Compliant:
  • SMALLINT: 16-bit integer, range -32,768 to 32,767.
  • INTEGER (INT): 32-bit integer, range -2,147,483,648 to 2,147,483,647.
  • BIGINT: 64-bit integer, range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
  • DECIMAL(p, s) / NUMERIC(p, s): Exact fixed-point decimals. ANSI SQL typically allows up to 31 digits of precision, but implementations may vary.
  • REAL: 32-bit floating-point number.
  • DOUBLE PRECISION: 64-bit floating-point number.
• PostgreSQL-Specific Enhancements:
  • SERIAL / BIGSERIAL: Auto-incrementing integer types that generate unique values. Not an ANSI SQL type, but a shorthand for sequences.
  • NUMERIC Precision: Unlike ANSI SQL, PostgreSQL allows arbitrary precision for NUMERIC, supporting up to 131072 digits before the decimal point and up to 16383 digits after.

Character Types

• ANSI SQL Compliant:

  • CHAR(n): Fixed-length, right-padded with spaces.
  • VARCHAR(n): Variable-length, constrained by n.
  • CLOB: ANSI SQL defines CLOB (Character Large Object) for storing large text data, with implementations typically supporting sizes up to several terabytes.

• PostgreSQL-Specific Enhancements:

  • Unlike other databases, TEXT and VARCHAR(n) are stored with the same efficiency, meaning VARCHAR(n) does not gain performance benefits over TEXT.

Binary Types

• ANSI SQL Compliant:
  • BINARY(n): Fixed-length binary storage.
  • VARBINARY(n): Variable-length binary storage.
  • BLOB (ANSI Large Object Storage Equivalent): ANSI SQL defines BLOB (Binary Large Object) for storing binary data, typically supporting sizes up to 4TB, depending on the database.

PostgreSQL-Specific Enhancements are described in Large Object Storage in Postgres

JSON vs JSONB Trade-offs

PostgreSQL offers two JSON storage types: JSON and JSONB, each with its advantages.

• When to Use JSON:

  • If storing JSON as a raw text representation without frequent queries.
  • Faster insert and update operations since no transformation is applied.
  • Preferred when maintaining strict original formatting of JSON documents.

• When to Use JSONB:

  • When indexing and querying JSON data efficiently is required.
  • JSONB supports GIN indexes, allowing fast lookups on key-value pairs.
  • Uses a binary format, making queries significantly faster than JSON.
  • Ignores whitespace and order differences in keys, reducing redundancy.