# Column Types and Constraints

> Combine data types, defaults, and constraints in a practical table definition.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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 {#example}

```sql {num="17.1" caption="Column Types and Constraints"}
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 {#run}

To run:

```text
$ 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 {#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.

> [!TIP] Tip
> Name your constraints. The default constraint names PostgreSQL generates are functional but cryptic. A name like `products_price_nonneg` is much more helpful when it appears in an error message in production.

> [!NOTE] Try it
> Try `INSERT INTO products (id, name, price) VALUES (4, '   ', 5);` and observe the `name_not_blank` check rejection. Try `INSERT INTO products (id, name, price) VALUES (5, 'Negative', -1);` to see the price check trigger. Then add a constraint after the fact: `ALTER TABLE products ADD CONSTRAINT category_valid CHECK (category IN ('misc','parts','tools'));`.

## Downloads {#downloads}

SQL file: [column-types-and-constraints.sql](/examples/source/column-types-and-constraints.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)