Logo ← PostgreSQL Blog

PostgreSQL Data Types

Choosing the correct data type in PostgreSQL is not just about storage — it’s about performance, maintainability, and scalability. Yet…

PostgreSQL Data Types

Choosing the correct data type in PostgreSQL is not just about storage — it’s about performance, maintainability, and scalability. Yet, many developers fall into long-standing myths or carry over habits from other databases like MySQL or Oracle.

This article will guide you through:

  • Common myths vs facts about PostgreSQL data types
  • How type choice impacts performance and storage
  • A full list of PostgreSQL native and custom data types
  • Detailed explanation of enum, domain, composite, and range types

Myths and Facts: Don’t Fall for These Traps

Myth 1: varchar(n) uses less space than text

Fact: Both text and varchar(n) are stored the same internally. The only difference is the length constraint. No performance or storage gain exists in modern PostgreSQL versions (>= 9.6).

Myth 2: char(n) is better for fixed strings

Fact: char(n) pads strings with spaces, resulting in wasted storage and slower comparisons. Only use it if required by external systems or strict formatting.

Myth 3: Always use numeric for numbers

Fact: numeric(p,s) is flexible but CPU-intensive. It’s ideal for precise financial calculations. Otherwise, prefer integer, bigint, or real for performance.

Myth 4: json and jsonb are the same

Fact: jsonb is binary, indexable, and faster to query. json is plain text. Always prefer jsonb unless you explicitly need formatting.

Myth 5: Use smallint for booleans

Fact: boolean uses 1 byte. smallint uses 2 bytes. Don’t sacrifice semantics and performance for imagined flexibility.

Myth 6: Always define a maximum length like varchar(255)

Fact: This is a legacy from MySQL. PostgreSQL can handle text efficiently. Adding unnecessary limits only increases maintenance complexity.

Performance Impact of Type Selection

Choosing the wrong data type leads to:

  • Wasted disk space
  • Bloated indexes
  • Slower queries
  • Higher memory and CPU usage
| Field      | Type           | Storage Efficiency               |
| ---------- | -------------- | -------------------------------- |
| username | text             | Efficient                      |
| username | varchar(200)     | Same as text                   |
| username | char(200)        | 200 characters always reserved |

Avoid char(n) unless truly necessary — it reserves space even if data is shorter.

PostgreSQL Native Data Types

| Type               | Description                         | Size     |
| ------------------ | ----------------------------------- | -------- |
| smallint           | 2-byte integer (±32k)               | 2 bytes  |
| integer / int      | 4-byte standard integer             | 4 bytes  |
| bigint             | 8-byte large integer                | 8 bytes  |
| decimal(p,s)       | Arbitrary precision decimal         | variable |
| numeric(p,s)       | Same as decimal (ideal for finance) | variable |
| real               | Single-precision float              | 4 bytes  |
| double precision   | Double-precision float              | 8 bytes  |
| serial             | Auto-increment integer              | 4 bytes  |
| bigserial          | Auto-increment bigint               | 8 bytes  |

Date & Time Types

| Type          | Description                         |
| ------------- | ----------------------------------- |
|   timestamp     | Date and time (no timezone)       |
|   timestamptz   | Timestamp with time zone          |
|   date          | Only date                         |
|   time          | Only time                         |
|   interval      | Time span (e.g., '1 hour 10 min') |

Character Types

| Type         | Description                     |
| ------------ | --------------------------------|
| text       | Unlimited-length string           |
| varchar(n) | Variable-length with limit        |
| char(n)    | Fixed-length string, space-padded |
| name       | System object names               |

Logical Type

| Type      | Description                |
| --------- | -------------------------- |
| boolean   | true, false, or null       |

Binary, Bit & Byte Types

| Type             | Description                   |
| ---------------- | ----------------------------- |
| bytea          | Binary data (e.g. file blobs) |
| bit(n)         | Bit string of fixed length    |
| bit varying(n) | Bit string of variable length |

JSON & Arrays

| Type    | Description                     |
| ------- | ------------------------------- |
| json  | Raw JSON string                 |
| jsonb | Binary JSON (faster, indexable) |
| array | Arrays of any PostgreSQL type   |

Special Types

| Type      | Description                |
| --------- | -------------------------- |
| boolean   | true, false, or null       |

Geographic Types (PostGIS)

| Type      | Description                |
| --------- | -------------------------- |
| boolean   | true, false, or null       |

(Requires PostGIS extension)

PostgreSQL Custom Data Types

PostgreSQL supports powerful custom types for domain-specific modeling:

enum – Enumerated Types

  • Store a fixed set of values like 'active', 'inactive'
  • Saves space and improves semantic integrity
  • Example:
CREATE TYPE status AS ENUM ('active', 'inactive', 'pending');

Use in tables:

status status NOT NULL

domain – Constrained Types

  • Define reusable types with built-in constraints
  • Cleaner and DRY schema design

Example:

CREATE DOMAIN email AS TEXT
  CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

Now use email just like any data type.

composite – Row Types

  • Define complex records as a single type
  • Useful in functions and for logical modeling

Example:

CREATE TYPE full_name AS (
  first_name text,
  last_name text
);

range – Range Types

  • Represent a range of values (e.g., daterange, int4range)
  • Great for versioning, timelines, period logic

Example:

SELECT '[2023-01-01,2023-12-31]'::daterange;

PostgreSQL gives you incredible flexibility with data types. But with great power comes great responsibility. Here’s what you should keep in mind:

Prefer text over arbitrary varchar(n)
Use boolean, enum, and jsonb wisely
Avoid char(n) and numeric unless justified
Explore custom types (domain, range, composite) for better design
Know that type choice directly impacts performance and storage