Data Types

Text

Three char types exist. Use text unless something forces your hand. varchar(n) and text have identical performance in Postgres, so varchar(n) buys you only a length check that a CHECK constraint expresses better and that ALTER TABLE has to rewrite to change.

SELECT length('abc  '::char(10));       -- 3, the padding is invisible
SELECT 'abc  '::char(10) = 'abc';       -- true, padding ignored
SELECT 'abc  '::text = 'abc';           -- false

Numbers

numeric is exact decimal arithmetic. real and double precision are IEEE binary floats and cannot represent 0.1 exactly. Money is always numeric.

SELECT 0.1::float8 + 0.2::float8 = 0.3::float8;   -- false
SELECT (0.1::float8 + 0.2::float8)::text;         -- 0.30000000000000004
SELECT 0.1::numeric + 0.2::numeric = 0.3::numeric; -- true

Store currency as numeric(12,2) (or as integer cents). Never float. Skip the built-in money type as well, it carries a locale-dependent output format and only two fixed decimal places.

Dates and times

The blunt rule: always timestamptz. timestamp (without time zone) stores a wall-clock reading with no offset, so two rows written from two servers are not comparable. timestamptz stores a single absolute instant in UTC and renders it in the session TimeZone.

SELECT now();                             -- timestamptz, transaction start time
SELECT clock_timestamp();                 -- real wall clock, moves inside a txn
SELECT now() AT TIME ZONE 'UTC';          -- timestamptz -> timestamp in UTC
SELECT (now() AT TIME ZONE 'America/New_York');
SELECT date_trunc('month', now());
SELECT '2024-03-01'::date + '1 month'::interval;   -- 2024-04-01 00:00:00
SELECT extract(epoch FROM '1 day'::interval);      -- 86400
SELECT age(now(), '2000-01-01'::timestamptz);
SELECT generate_series(date '2024-01-01', date '2024-01-05', '1 day');

An interval keeps months and days apart on purpose, so '1 month' is 28 to 31 real days depending on where you add it. Use '30 days' if you mean 30 days.

Identity, UUIDs, and booleans

serial is legacy. It is not a type, it is macro sugar for an integer plus a sequence plus a default, and it leaves the sequence ownership and grants slightly off. The SQL-standard GENERATED ... AS IDENTITY (10+) is the modern form.

-- Modern
CREATE TABLE t (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
-- Allows an explicit override on INSERT
CREATE TABLE t (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);
-- Legacy equivalent of bigserial
CREATE TABLE t (id bigserial PRIMARY KEY);

ALWAYS rejects a client-supplied id unless the statement says OVERRIDING SYSTEM VALUE. BY DEFAULT accepts one, which is what data loaders want.

gen_random_uuid() has been built in since 13, so a plain uuid primary key needs no extension. Random v4 UUIDs scatter btree inserts, which is a real write-amplification cost on a large table, so a bigint identity is still the better default for internal keys.

SELECT '192.168.1.5'::inet << '192.168.1.0/24'::cidr;  -- true, is contained by
SELECT encode('Hello'::bytea, 'hex');                  -- 48656c6c6f

Do not store files in bytea if you can avoid it. Rows carry into every backup and every replica.

Enums, arrays, ranges, and JSON

ALTER TYPE mood ADD VALUE 'ecstatic' AFTER 'happy';   -- 9.1+, cannot remove values
SELECT enum_range(NULL::mood);                        -- {sad,ok,happy,ecstatic}

Adding an enum value is cheap. Removing or reordering one is not possible, you rebuild the type. If the value set churns, use text with a CHECK constraint or a lookup table instead.

jsonb deduplicates keys, discards insignificant whitespace, and does not preserve key order. json preserves the input byte for byte. Choose jsonb unless you need exact round-tripping of the original document.

Casting and NULL

Two cast spellings. :: is the Postgres shorthand, CAST(x AS type) is the standard and is required inside some grammars.

SELECT '42'::int, CAST('42' AS int);
SELECT pg_typeof(1), pg_typeof(1.0);         -- integer, numeric
SELECT '2024-13-01'::date;                   -- error, out of range for a month
SELECT nullif(total, 0) FROM orders;         -- 0 becomes NULL
SELECT coalesce(nickname, name, 'anon');

NULL is not a value, it is the absence of one. Every comparison against it is NULL, which is not true, so it never satisfies a WHERE.

IS DISTINCT FROM is the NULL-safe inequality, and IS NOT DISTINCT FROM the NULL-safe equality. Use them in change-detection updates so a NULL-to-value transition is caught.

UPDATE customers SET phone = $1
WHERE id = $2 AND phone IS DISTINCT FROM $1;