17 Column Types and Constraints

A real table picks a type for every column and often adds constraints — rules the database enforces on every row. The most common constraints are NOT NULL (the column must have a value), DEFAULT (use this value if none is given), CHECK (a boolean expression that must be true), UNIQUE, PRIMARY KEY, and REFERENCES (foreign key). Each gets its own lesson; here we combine several into one realistic table.

What you’ll learn

• Combining multiple types and constraints in one CREATE TABLE
• Inline DEFAULT and NOT NULL
• CHECK constraints for domain rules
• Using timestamptz with current_timestamp as a default
• Inspecting constraints in the catalog

Example

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id          integer PRIMARY KEY,
  name        text NOT NULL,
  price       numeric(10, 2) NOT NULL CHECK (price >= 0),
  in_stock    boolean NOT NULL DEFAULT true,
  category    text DEFAULT 'misc',
  created_at  timestamptz NOT NULL DEFAULT current_timestamp,
  CONSTRAINT name_not_blank CHECK (length(trim(name)) > 0)
);

INSERT INTO products (id, name, price)              VALUES (1, 'Widget',    19.99);
INSERT INTO products (id, name, price, in_stock)    VALUES (2, 'Gadget',    49.99, false);
INSERT INTO products (id, name, price, category)    VALUES (3, 'Sprocket',  4.95, 'parts');

SELECT * FROM products ORDER BY id;

-- Inspect the constraints on the table
SELECT conname, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conrelid = 'products'::regclass
ORDER BY conname;

Each constraint is one promise the database makes about your data. NOT NULL says the value is always present. DEFAULT supplies a value when an INSERT omits the column. CHECK (expr) rejects any row where expr is false (NULLs pass). PRIMARY KEY implies NOT NULL and UNIQUE, and creates an index.

Constraints can be inline (after the column type, as above) or table-level (after all the columns). Table-level syntax is required for composite PRIMARY KEY, UNIQUE, and foreign-key constraints. A CHECK can reference several columns in either position, and both column and table constraints can be named with CONSTRAINT name for clearer errors.

timestamptz NOT NULL DEFAULT current_timestamp is one of the most common column patterns — the row’s creation time, stored in UTC, never null, set automatically.

Run the example

To run:

$ psql -f source/column-types-and-constraints.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
 id |   name   | price | in_stock | category |          created_at
----+----------+-------+----------+----------+-------------------------------
  1 | Widget   | 19.99 | t        | misc     | 2024-xx-xx xx:xx:xx.xxxxxx+00
  2 | Gadget   | 49.99 | f        | misc     | 2024-xx-xx xx:xx:xx.xxxxxx+00
  3 | Sprocket |  4.95 | t        | parts    | 2024-xx-xx xx:xx:xx.xxxxxx+00
(3 rows)
 ...

Common pitfalls

• Forgetting NOT NULL on columns that should never be null — adding it later requires backfilling and may take a long lock on a large table.
• CHECK accepts a row when the expression is NULL. To reject NULLs, combine NOT NULL and CHECK.
• Using now() as a DEFAULT: it works, but inside a long transaction every default is the transaction-start time. Usually that is fine; for true per-row wall-clock use clock_timestamp().
• Adding a DEFAULT and NOT NULL to a new column on a huge table used to rewrite the entire table; modern PostgreSQL (11+) avoids that rewrite when the default is a constant, but volatile defaults still rewrite.

Downloads

SQL file: column-types-and-constraints.sql · All lesson files · Setup guide