Tables and data types_

A table is the unit of storage in a relational database, a named grid where:

• each row is one record: one customer, one order
• each column is one attribute every record shares: name, price, creation time
• each column has a type that determines what values it accepts and how they compare, sort, and calculate

Unlike a spreadsheet, the set of columns is declared up front and enforced; every row has exactly those columns. This fixed shape is what lets the database enforce correctness: precise types and constraints reject bad data before it ever reaches your application.

The examples on this page build up pieces of a small store schema: customers, products, and orders.

Create a table

CREATE TABLE names the table and declares each column with its type and the rules it must obey:

CREATE TABLE customers (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    email      text NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now()
);

This declaration packs in the most common column features:

• GENERATED ALWAYS AS IDENTITY makes the database assign an auto-incrementing value. Prefer it over the older serial shorthand, which creates a separate sequence with looser ownership semantics.
• PRIMARY KEY uniquely identifies each row and creates an index automatically.
• NOT NULL rejects missing values, and UNIQUE rejects duplicates.
• DEFAULT now() fills the column when an insert doesn't provide a value.

Numeric types

The critical distinction is exact versus approximate:

• numeric stores decimal values exactly: 0.1 + 0.2 is 0.3.
• double precision stores binary approximations: the same sum comes back as 0.30000000000000004.

Always use numeric for money. A price column like numeric(10, 2) holds up to 8 digits before the decimal point and exactly 2 after; plain numeric with no qualifiers accepts values of any precision.

Text types

PostgreSQL has exactly three character types, and they all share the same storage underneath:

There is no tiered family like other databases' TINYTEXT or LONGTEXT; one text type covers everything. Use text for all strings. PostgreSQL does not treat varchar differently from text in storage or performance, so a length limit is purely a business rule:

CREATE TABLE products (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  text NOT NULL,
    sku   varchar(20) NOT NULL UNIQUE,  -- length limit as a business rule
    price numeric(10, 2) NOT NULL CHECK (price >= 0)
);

Date and time types

Default to timestamptz. It converts input to UTC on write and renders it in the session's time zone on read, so two clients in different time zones always agree on the moment an event happened:

CREATE TABLE deployments (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    version     text NOT NULL,
    deployed_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO deployments (version, deployed_at) VALUES
    ('1.4.0', '2026-07-15 09:00:00+02'),   -- written from Berlin
    ('1.4.1', '2026-07-15 07:30:00+00');   -- written from London

SELECT version, deployed_at FROM deployments ORDER BY deployed_at;

Both rows are stored in UTC, so the ordering is correct regardless of the time zone each writer used: 07:00 UTC (1.4.0) sorts before 07:30 UTC (1.4.1).

Boolean, UUID, and binary

Three more types cover flags, identifiers, and raw bytes:

• boolean accepts true/false and the literals 'yes', 'no', '1', '0'.
• uuid stores 128-bit identifiers natively in 16 bytes, and gen_random_uuid() generates them without any extension.
• bytea holds raw bytes, such as hashes or encrypted blobs.

CREATE TABLE api_tokens (
    token_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    active   boolean NOT NULL DEFAULT true,
    secret   bytea NOT NULL
);

JSON columns

Use jsonb for schemaless data attached to structured rows. It stores a parsed binary representation that supports indexing and containment queries; plain json keeps the original text and is only useful when you must preserve key order or duplicates:

CREATE TABLE events (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kind    text NOT NULL,
    payload jsonb NOT NULL DEFAULT '{}'
);

INSERT INTO events (kind, payload)
VALUES ('signup', '{"plan": "pro", "referrer": "newsletter"}');

SELECT payload->>'plan' AS plan
FROM events
WHERE payload @> '{"referrer": "newsletter"}';

->> extracts a field as text, and @> tests containment. Reach for jsonb when attributes vary per row; keep anything you filter or join on regularly as a real column.

Array types

Any type can be stored as an array by appending []:

CREATE TABLE articles (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tags text[] NOT NULL DEFAULT '{}'
);

INSERT INTO articles (tags) VALUES (ARRAY['sql', 'tutorial']);

SELECT * FROM articles WHERE 'sql' = ANY (tags);

Arrays suit small, ordered value lists owned by one row. When the values need their own attributes or are shared across rows, model them as a separate table instead. See Data modeling.

Constraints

Constraints are rules the database enforces on every write, no matter which application or migration performs it:

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    total       numeric(10, 2) NOT NULL CHECK (total >= 0)
);

A foreign key also controls what happens when the referenced row disappears. ON DELETE CASCADE removes dependent rows, ON DELETE SET NULL orphans them explicitly, and the default NO ACTION blocks the delete. Foreign keys are covered in depth in Joins and relationships.

Generated columns

A generated column is one the database computes for you from the row's other columns, instead of accepting a value from the application. You declare the formula once in the schema; every write runs it automatically. This removes a whole class of bugs where a derived value, like a line total, is computed in application code and drifts out of sync with the columns it came from.

CREATE TABLE line_items (
    quantity   integer NOT NULL,
    unit_price numeric(10, 2) NOT NULL,
    line_total numeric(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

INSERT INTO line_items (quantity, unit_price) VALUES (3, 19.99);

SELECT line_total FROM line_items;

 line_total
------------
      59.97

The insert supplies only quantity and unit_price; the database fills in line_total as 3 × 19.99. Writing to the column directly is an error, and if a later UPDATE changes the quantity, the total is recomputed in the same statement, so it can never disagree with its inputs. STORED means the result is computed on write and saved on disk like a normal column, making reads free.

Change a table

ALTER TABLE evolves a schema in place:

ALTER TABLE customers ADD COLUMN phone text;
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;
ALTER TABLE customers RENAME COLUMN phone TO phone_number;
ALTER TABLE customers DROP COLUMN phone_number;

Adding a nullable column or one with a constant default doesn't rewrite the table, so it's fast at any size, though it still takes a brief exclusive lock and waits for in-flight queries. Adding NOT NULL to an existing column scans the table to validate existing rows, so on large tables do it during a quiet period.