Overview
Type choice sets storage size, index behavior, and available operators. Defaults: text, bigint identity keys, numeric for money, timestamptz for instants, jsonb for documents. Operators for JSON are in postgres-jsonb; index choice by type is in postgres-indexes.
Text
Use text, and add varchar(n) only for a real domain limit.
| Type | Notes |
|---|---|
text | Unlimited length; the default choice. |
varchar(n) | Same storage as text plus a length check; use only for a real domain limit. |
char(n) | Blank-padded to n; avoid. |
citext | Case-insensitive text; needs CREATE EXTENSION citext. |
Postgres has no performance difference between varchar(255) and text; the 255 habit comes from MySQL.
Numbers
Use bigint identity keys and numeric for money.
| Type | Size | Range or use |
|---|---|---|
smallint / integer / bigint | 2 / 4 / 8 bytes | ±32,767 / ±2.1 billion / ±9.2 × 10^18. Use bigint for IDs and counters. |
numeric(p,s) | variable | Exact decimals; use for money. |
real / double precision | 4 / 8 bytes | IEEE floats; not exact for decimal fractions. |
serial / bigserial | 4 / 8 bytes | Legacy shorthand for an integer with a sequence default. |
GENERATED ALWAYS AS IDENTITY | SQL-standard auto-increment; prefer over serial. |
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
total numeric(12,2) NOT NULL
);Do not store currency in floats; use numeric or integer cents.
Date and time
Use timestamptz for instants.
| Type | Size | Notes |
|---|---|---|
timestamptz | 8 bytes | An instant; stored as UTC, shown in the session time zone. Default choice. |
timestamp | 8 bytes | No zone; any offset in the input is dropped. |
date / time | 4 / 8 bytes | Date only / time of day. |
timetz | 12 bytes | Time with zone; avoid, use timestamptz. |
interval | 16 bytes | Duration. |
started_at timestamptz NOT NULL DEFAULT now(),
ended_at timestamptz,
duration interval GENERATED ALWAYS AS (ended_at - started_at) STOREDJSON
Use jsonb unless you must preserve the original text.
| Type | Notes |
|---|---|
jsonb | Parsed binary form; supports GIN indexes and operators; drops duplicate keys (last wins) and key order. |
json | Verbatim text; keeps key order and duplicates; no GIN index. Use only to round-trip the original bytes. |
CREATE TABLE products (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, attrs jsonb NOT NULL DEFAULT '{}');
CREATE INDEX ON products USING GIN (attrs);
SELECT * FROM products WHERE attrs @> '{"color":"red"}';Arrays
Use arrays for small, simple lists, and a join table when elements need their own columns.
| Operation | Syntax |
|---|---|
| Declare / literal | tags text[], ARRAY['a','b'] or '{a,b}' |
| Element (1-based) / length | tags[1], cardinality(tags) |
| Contains / overlaps | tags @> ARRAY['a'], tags && ARRAY['a','b'] |
| Append / concatenate | array_append(tags, 'x'), tags || ARRAY['x'] |
| One row per element | unnest(tags) |
Index arrays with GIN: CREATE INDEX ON posts USING GIN (tags);.
UUID
Use uuidv7() on Postgres 18 and later when keys are inserted at a high rate.
| Expression | Notes |
|---|---|
gen_random_uuid() | Random v4; built in since Postgres 13. |
uuidv7() | Time-ordered v7; built in from Postgres 18. Earlier versions need an extension or app-side generation. |
uuidv4() | Postgres 18 alias for gen_random_uuid(). |
uuid_generate_v4() | Needs the uuid-ossp extension. |
Random v4 keys insert at random B-tree positions; prefer v7 when insert rate and index locality matter.
Gotchas
Avoid these; each corrupts time handling or trips up indexing.
| Gotcha | Fix |
|---|---|
Plain timestamp silently discards time zone offsets. | Use timestamptz. |
tags[0] is NULL; arrays start at 1. | Index from 1. |
serial is not a real type and has sequence-ownership quirks. | Use identity columns. |
now() is the transaction start time. | Use clock_timestamp() for wall-clock time. |