Data Types and Casts

Choose and convert PostgreSQL types safely. Results appear in the same fence: same-line -- comments when short, multiline -- blocks below the sample when not.

Numeric Types

Prefer numeric for money; int/bigint for counters.

SELECT 1::int, 1::bigint, 1.23::numeric(10,2);
-- 1 | 1 | 1.23

timestamptz

Store instants in timestamptz; display in a zone.

SELECT now() AT TIME ZONE 'UTC' AS utc_now;
-- utc_now: current UTC timestamp

UUID gen

Generate UUIDs (pgcrypto/core depending on version).

SELECT gen_random_uuid();
-- e.g. 550e8400-e29b-41d4-a716-446655440000

Cast Syntax

:: cast or CAST(x AS type).

SELECT '42'::int, CAST('2020-01-01' AS date);
-- 42 | 2020-01-01

Array Literals

Curly literal or ARRAY constructor.

SELECT ARRAY[1,2,3] AS a, '{a,b}'::text[] AS t;
-- a: {1,2,3}

Boolean

true/false/unknown; accept t/f in text casts.

SELECT true::text, 'yes'::boolean;
-- true | true

Interval Math

Add intervals to timestamps.

SELECT timestamptz '2020-01-01' + interval '7 days';
-- 2020-01-08 00:00:00+00 (zone dependent)

Text Types

text is preferred; varchar(n) when length limit matters.

SELECT length('hello'), char_length('hello');
-- 5 | 5

Bytea

Binary columns; encode for display.

SELECT encode('\xDEADBEEF'::bytea, 'hex');
-- deadbeef

Enum Type

Create and cast enums.

-- CREATE TYPE mood AS ENUM ('happy', 'sad');
SELECT 'happy'::mood;
-- happy

Domain Type

Constrained alias of a base type.

-- CREATE DOMAIN email AS text CHECK (VALUE ~ '@');
-- 'a@b.co'::email

Range Types

Inclusive/exclusive bounds.

SELECT int4range(1, 5, '[)') @> 4;
-- t  (contains 4)

Network Types

inet/cidr for addresses.

SELECT '10.0.0.1'::inet << '10.0.0.0/8'::cidr;
-- t

JSON vs JSONB

jsonb is binary, indexable; prefer jsonb for storage.

SELECT '{"a":1}'::jsonb ->> 'a';
-- 1

Safe Cast

RETURN NULL on failure with try-style patterns.

SELECT nullif(trim('x'), '')::int; -- may error
-- prefer: CASE WHEN s ~ '^\d+$' THEN s::int END

Related

• JSONB and Arrays - document and array types
• Constraints and Schema - typed columns
• Insert Update Delete - typed literals on write

External Related Links

• Current Version Documentation - Full manual for the latest stable release.
• pgPedia – PostgreSQL Encyclopedia - Encyclopedia of PostgreSQL features, history, and change tracking.
• Official PostgreSQL Documentation - Central hub for all manuals.
• Learn PostgreSQL (2nd Edition) - Luca Ferrari & Enrico Pirozzi.
• Mastering PostgreSQL 17 (6th Edition) - Hans-Jürgen Schönig.

Stack versions: PostgreSQL 18.4 (stable 18, maintenance 17) · pgvector 0.8+