PostgreSQL Data Types - PostgreSQL Tutorial

Numbers

Type	Use
`SMALLINT`	−32k … 32k
`INTEGER` / `INT`	±2.1 billion — the default
`BIGINT`	very large ids, counters
`NUMERIC(p,s)`	exact decimals — money
`REAL` / `DOUBLE PRECISION`	approximate floats — science, not money
`SERIAL` / `BIGSERIAL`	auto-incrementing integer

Money goes in NUMERIC, never in a float:

SQL

SELECT 0.1::float + 0.2::float;      -- 0.30000000000000004
SELECT 0.1::numeric + 0.2::numeric;  -- 0.3

Identity columns

SERIAL is the old way. Modern Postgres prefers standard identity columns:

SQL

CREATE TABLE books (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL
);

Text

Type	Notes
`TEXT`	unlimited — use this by default
`VARCHAR(n)`	limited length
`CHAR(n)`	fixed, space-padded — avoid

In Postgres, TEXT and VARCHAR perform identically; there is no efficiency reason to pick VARCHAR(255). Use TEXT plus a CHECK constraint if you need a limit.

Dates and times

Type	Stores
`DATE`	date only
`TIME`	time only
`TIMESTAMP`	date+time, no timezone
`TIMESTAMPTZ`	date+time, timezone-aware
`INTERVAL`	a duration

Use TIMESTAMPTZ for almost everything. Plain TIMESTAMP records a wall-clock reading with no idea what zone it came from, which becomes unfixable later.

SQL

SELECT now();                                 -- current timestamptz
SELECT now() + INTERVAL '7 days';
SELECT age(now(), '1990-01-01');
SELECT date_trunc('month', now());            -- start of this month
SELECT EXTRACT(DOW FROM now());               -- day of week

Boolean

SQL

CREATE TABLE t (is_active BOOLEAN NOT NULL DEFAULT true);

Accepts true/false, 't'/'f', 'yes'/'no', 1/0.

UUID

SQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

UUIDs avoid exposing row counts and let clients generate ids offline, at the cost of being larger and less index-friendly than integers.

Arrays

SQL

CREATE TABLE posts (tags TEXT[]);

INSERT INTO posts (tags) VALUES (ARRAY['nepali', 'fiction']);

SELECT * FROM posts WHERE 'fiction' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['nepali'];   -- contains

ENUM

SQL

CREATE TYPE order_status AS ENUM ('new', 'paid', 'shipped', 'cancelled');
CREATE TABLE orders (status order_status NOT NULL DEFAULT 'new');

Type-safe, but adding a value requires ALTER TYPE. Many teams prefer TEXT with a CHECK constraint for easier evolution.

Casting

SQL

SELECT '42'::INTEGER;
SELECT CAST('42' AS INTEGER);
SELECT price::TEXT FROM books;