Data types

The type matrix

The pg OID is what a Postgres driver sees in the row description, and therefore how it decodes the value.

The sixteenth storage type, a 32-bit int (wire int4, 23, range-checked 22003), is not produced by any Postgres DDL spelling; it comes from the typed gRPC CreateTable request (int/int32) or from CREATE TABLE … AS SELECT inference.

A type outside this table (TIME, INTERVAL, MONEY, XML, enums, ranges) is Refused at CREATE TABLE, with the column named in the error. INTERVAL still works as an expression type (now() - INTERVAL '1 day').

Strings and widths

VARCHAR, TEXT and UUID are unbounded UTF-8. A declared width is a real constraint: since 0.1.43

CREATE TABLE tags (id BIGINT PRIMARY KEY, code VARCHAR(3));
INSERT INTO tags VALUES (1, 'abcd');
-- ERROR:  value too long for type character varying(3) — column "code" of "tags" got 4 characters
-- SQLSTATE 22001

The width is visible to your tools: DESCRIBE tags reports string(3) and information_schema.columns.character_maximum_length is 3 (see Catalog).

Integers

INTEGER is widened on purpose: generated keys are 64-bit snowflake ids, so a foreign-key column declared integer to match a parent’s declared type would otherwise truncate silently.

Over the HTTP JSON door, a long outside ±(2⁵³ − 1) should be sent as a JSON string to survive the client’s number parser. Postgres drivers bind int8 natively and are unaffected.

Exact numerics

SELECT CAST(2.01 / 2 AS DECIMAL(10,2));   -- 1.01, as in Postgres (not 1.00)
SELECT CAST(10.005 AS DECIMAL(10,2));      -- 10.01
SELECT 1.0 / 0;
-- ERROR:  division by zero   (SQLSTATE 22012)

A value with more fractional digits than the declared scale is rounded half away from zero on write (1.005 into NUMERIC(10,2) stores 1.01), exactly as Postgres does. The digits are carried exactly on every entry path: SQL literal, text bind, binary bind and COPY, and read back exactly in text or binary. Exponent literals (1e-6) are exact numerics. since 0.1.37

Floats

REAL/FLOAT4 is stored in 32 bits and advertised as float4, so a round-tripped REAL may differ in the last digits from the 64-bit value you sent. DOUBLE PRECISION round-trips exactly. FLOAT(p) picks the width from p as Postgres does. Do not use floats for money; use NUMERIC.

Temporal

There is one timestamp type. It holds microseconds since the epoch in UTC and is described to drivers as timestamptz.

CREATE TABLE t (id BIGINT PRIMARY KEY, at3 TIMESTAMP(3));
INSERT INTO t VALUES (1, TIMESTAMP '2026-09-18 10:11:12.987654');
SELECT at3 FROM t;
-- 2026-09-18 10:11:12.988+00
SELECT at3 AT TIME ZONE 'Europe/London' FROM t;   -- works; lowers to timezone()

date_trunc('day', at3) and EXTRACT(hour FROM at3) evaluate in UTC regardless of the client’s zone; use AT TIME ZONE in the query, or convert in the application, for display (see Functions).

Boolean

BOOLEAN/BOOL, wire bool. Text input accepts true/false, t/f, 1/0. Postgres’s extra spellings (yes/no, on/off) are refused, and so is any other string (22P02 on a write, Could not convert string … to BOOL in a SELECT); nothing becomes NULL. The same rule holds for every type: a datetime string into DATE, a bare date into TIMESTAMP and 'true' into BOOLEAN coerce as in Postgres; an impossible date (2026-13-45) or an unreadable number is 22P02.

Binary

BYTEA, BLOB, BINARY, VARBINARY store raw bytes and are returned as bytea. The text form is Postgres hex, '\xdeadbeef', in and out; Postgres drivers bind binary natively. Over HTTP JSON the value travels base64-encoded.

JSON

JSON and JSONB are one type, advertised as jsonb (3802) in metadata and on the wire, so psycopg returns a dict, SQLx a serde_json::Value, node-postgres an object. The document is stored and served verbatim: key order, duplicate keys and integers beyond 2⁵³ are preserved. The Postgres operators ->, ->> and the DuckDB json_* functions work; see Functions → JSON. For fields you filter or aggregate on constantly, promote them to typed columns.

Arrays and structs

T[] / ARRAY<T> and STRUCT(f t, …) are native nested columns. On the pg wire an array is advertised as text and rendered as a {…} literal, a struct as jsonb. Text and binary result formats are therefore both valid, and binary-binding drivers never fail on them; native pg array OIDs are not yet used for results. Binding an array parameter (WHERE id = ANY($1)) works with every tested driver.

Vectors

VECTOR(n) holds n 32-bit floats; n is required (Refused for a bare VECTOR). On the wire it is text in pgvector’s […] spelling, so pgvector client helpers parse it. Distance operators and index kinds are on Functions → Vector operators and Schema changes → Indexes.

Default expressions

A column default is validated when the table is created, not on first insert, and fires only when the column is absent from the row. An explicit NULL is stored as NULL.

Any other default expression, and any function or literal whose type does not match the column, is refused at CREATE TABLE (42703, DEFAULT literal … does not match column type). The trailing () is optional on the function forms. In ordinary query SQL, use gen_random_uuid() or uuid(); GENERATE_UUID() is only valid as a default.