Numeric
| Type | Size | Notes |
|---|---|---|
smallint | 2 bytes | ±32,767 |
integer / int | 4 bytes | ±2.1 billion — the usual choice |
bigint | 8 bytes | Very large values |
numeric(p,s) / decimal | variable | Exact — use for money |
real / double precision | 4 / 8 bytes | Approximate floating point |
smallserial / serial / bigserial | — | Auto-incrementing integer |
Postgres has no UNSIGNED. Enforce non-negativity with a CHECK constraint instead.
Auto-increment: serial vs identity
-- older style
id serial PRIMARY KEY
-- SQL-standard style, preferred since Postgres 10
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
serial is shorthand that creates a sequence and a default; identity columns are standard SQL, are owned properly by the table, and cannot be accidentally overwritten when declared ALWAYS.
Character types
| Type | Use |
|---|---|
text | Unlimited length — the normal choice in Postgres |
varchar(n) | Same as text, with a length limit |
char(n) | Blank-padded fixed length; rarely useful |
Unlike some databases, text is not slower than varchar in Postgres. Use varchar(n) only when the limit is a real business rule.
Date and time
| Type | Stores |
|---|---|
date | Calendar date |
time / time with time zone | Time of day |
timestamp | Date and time, no zone |
timestamptz | Date and time, stored as UTC, converted on read |
interval | A duration ('3 days 4 hours') |
Use timestamptz almost always. It stores UTC internally and converts to the client's TimeZone setting on output, which is what you want for any application with users in more than one place.
SHOW timezone;
SET timezone = 'Asia/Dhaka';
SELECT now(), now() AT TIME ZONE 'UTC';
Boolean, UUID, and network types
active boolean DEFAULT true -- true/false/null, accepts 't','yes','1'
id uuid DEFAULT gen_random_uuid() -- built in since Postgres 13
ip inet, mac macaddr, network cidr
JSON and JSONB
prefs json -- stores the exact text, preserves key order and whitespace
prefs jsonb -- parsed binary form: faster to query, indexable, de-duplicates keys
Use jsonb unless you need to preserve the original document byte-for-byte.
Arrays
CREATE TABLE posts (
id serial PRIMARY KEY,
title text,
tags text[]
);
INSERT INTO posts (title, tags) VALUES ('Intro', ARRAY['sql','beginner']);
SELECT * FROM posts WHERE 'sql' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql']; -- indexable with GIN
Enums, ranges and composites
CREATE TYPE mood AS ENUM ('sad','ok','happy');
CREATE TABLE t (id int, feeling mood);
CREATE TABLE bookings (room int, during tstzrange);
SELECT * FROM bookings WHERE during @> now();
Range types (int4range, daterange, tstzrange) plus an exclusion constraint are the clean way to prevent overlapping bookings — something most databases cannot express at all.
Casting
SELECT '42'::integer, CAST('42' AS integer);
SELECT 3.7::int; -- 4 (rounds)
SELECT '2026-09-17'::date + 7;
SELECT pg_typeof(now());