How to Use Data Types in PostgreSQL

How to Use Data Types in PostgreSQL

Learn the syntax, best practices, and common pitfalls when choosing and casting PostgreSQL data types.

What are the built-in PostgreSQL data types?

PostgreSQL ships with numeric, character, date/time, boolean, enumerated, geometric, network, JSON, UUID, array, range, and composite data types. Each class optimizes storage and behavior for its value domain.

How do I declare a column with a specific data type?

Supply the data type in a CREATE TABLE or ALTER TABLE statement. Add modifiers such as length, precision, NOT NULL, DEFAULT, or CHECK to tighten constraints.

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id UUID NOT NULL,
total NUMERIC(10,2) CHECK (total >= 0),
status TEXT DEFAULT 'new',
placed_at TIMESTAMPTZ DEFAULT now()
);

When should I use VARCHAR, TEXT, or CHAR?

Prefer TEXT for variable-length strings without a hard limit. Use VARCHAR(n) only when a business rule requires an upper bound. Avoid CHAR(n) unless you truly need fixed-width padding.

How do I cast between data types?

Use the :: operator or CAST(expr AS type). Explicit casts avoid surprises and use indexes when the cast is immutable.

SELECT '42'::INT;
SELECT CAST('2024-01-01' AS DATE);

Why choose JSONB over JSON?

JSONB stores data in a binary format that allows indexing with GIN/GiST, equality comparison, and faster read access. JSON preserves original whitespace and ordering but lacks efficient operators.

How do arrays and ranges work?

Arrays store ordered collections of the same type: INTEGER[], TEXT[], etc. Ranges track contiguous value spans like INT4RANGE or TSRANGE. Both support containment and overlap operators.

Best practices for selecting data types

Select the narrowest native type that supports your domain. Avoid over-allocating precision. Use UUID for distributed keys, NUMERIC for money, and TIMESTAMPTZ for timestamps.