Constraints & Data Integrity
3/3

NOT NULL, DEFAULT & GENERATED Columns

The NOT NULL and DEFAULT constraints handle the most fundamental question about your data: what happens when a value is missing? NOT NULL prevents gaps, DEFAULT fills them automatically, and GENERATED columns compute values from other data. Together, they ensure your tables always contain complete, predictable data.

NOT NULL

The NOT NULL constraint prevents a column from containing NULL values. Any INSERT or UPDATE that would set the column to NULL is rejected.

Declaring NOT NULL

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,            -- this CAN be NULL
  notes TEXT             -- this CAN also be NULL
);

What Happens When You Violate NOT NULL

INSERT INTO customers (name, email) VALUES ('Alice', 'alice@example.com');
-- Succeeds: name and email are provided, phone and notes default to NULL

INSERT INTO customers (name) VALUES ('Bob');
-- ERROR: null value in column "email" violates not-null constraint
-- DETAIL: Failing row contains (2, Bob, null, null, null).

Explicit NULLs are also rejected:

INSERT INTO customers (name, email) VALUES ('Carol', NULL);
-- ERROR: null value in column "email" violates not-null constraint

Adding NOT NULL to an Existing Table

ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;

This will fail if any existing rows have NULL in the phone column. You must update those rows first:

-- First, fill in missing values
UPDATE customers SET phone = 'unknown' WHERE phone IS NULL;

-- Then add the constraint
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;

Removing NOT NULL

ALTER TABLE customers ALTER COLUMN phone DROP NOT NULL;

When to Use NOT NULL

As a general rule, use NOT NULL unless you have a specific reason to allow NULLs. Columns that should almost always be NOT NULL include:

  • Names and identifiers
  • Dates that are always known (created_at, order_date)
  • Status columns
  • Quantities and amounts
  • Foreign keys in required relationships

Columns where NULL is appropriate:

  • Optional fields (middle name, phone number)
  • Dates that may not have occurred yet (shipped_at, deleted_at)
  • Foreign keys in optional relationships (manager_id)

DEFAULT Values

The DEFAULT constraint specifies a value to use when an INSERT statement does not provide one for that column.

Declaring DEFAULT Values

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  quantity INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  is_priority BOOLEAN NOT NULL DEFAULT false
);

How DEFAULT Works

When you omit a column in your INSERT, the default value is used:

INSERT INTO orders (customer_name) VALUES ('Alice');
-- Resulting row: id=1, customer_name='Alice', status='pending',
--                quantity=1, created_at=<current timestamp>, is_priority=false

You can override the default by specifying a value explicitly:

INSERT INTO orders (customer_name, status, quantity)
VALUES ('Bob', 'rush', 5);
-- status='rush' (override), quantity=5 (override)

Note that explicitly inserting NULL still inserts NULL, not the default:

INSERT INTO orders (customer_name, status) VALUES ('Carol', NULL);
-- ERROR if status is NOT NULL
-- If status allowed NULL, status would be NULL, not 'pending'

To explicitly request the default value, use the DEFAULT keyword:

INSERT INTO orders (customer_name, status) VALUES ('Dave', DEFAULT);
-- status = 'pending' (the default)

Common DEFAULT Patterns

Timestamps:

CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  action TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

NOW() and CURRENT_TIMESTAMP both return the current date and time. They are evaluated at the time of insertion.

Boolean flags:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT true,
  is_admin BOOLEAN NOT NULL DEFAULT false,
  is_verified BOOLEAN NOT NULL DEFAULT false
);

Counters and quantities:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  view_count INTEGER NOT NULL DEFAULT 0,
  stock_quantity INTEGER NOT NULL DEFAULT 0
);

String defaults:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT NOT NULL DEFAULT 'Unassigned',
  status TEXT NOT NULL DEFAULT 'active'
);

UUID primary keys:

CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id INTEGER NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Modifying DEFAULT on Existing Tables

-- Add or change a default
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new';

-- Remove a default (NULLs will be inserted if column is omitted)
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;

Changing a default only affects future inserts. Existing rows are not modified.

Combining NOT NULL and DEFAULT

NOT NULL and DEFAULT complement each other perfectly. The pattern NOT NULL DEFAULT value means:

  1. The column can never be NULL
  2. If you do not provide a value, the default is used automatically
CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'todo',
  priority INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

This makes INSERT statements simpler because you only need to provide the truly required, user-supplied values:

INSERT INTO tasks (title) VALUES ('Write documentation');
-- id=1, title='Write documentation', status='todo', priority=0, created_at=<now>

GENERATED Columns

PostgreSQL supports two types of generated columns: identity columns and computed (stored) columns.

Identity Columns (GENERATED AS IDENTITY)

Identity columns are the modern replacement for SERIAL. They automatically generate sequential values.

GENERATED ALWAYS:

CREATE TABLE invoices (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount NUMERIC NOT NULL
);

With GENERATED ALWAYS, you cannot manually specify the id. PostgreSQL always generates it:

INSERT INTO invoices (amount) VALUES (99.99);
-- id is auto-generated

INSERT INTO invoices (id, amount) VALUES (100, 49.99);
-- ERROR: cannot insert a non-DEFAULT value into column "id"
-- HINT: Use OVERRIDING SYSTEM VALUE to override.

If you truly need to override it (rare cases like data migration):

INSERT INTO invoices (id, amount) OVERRIDING SYSTEM VALUE VALUES (100, 49.99);

GENERATED BY DEFAULT:

CREATE TABLE invoices (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  amount NUMERIC NOT NULL
);

With GENERATED BY DEFAULT, the value is auto-generated only when you do not specify one. You can manually provide values:

INSERT INTO invoices (amount) VALUES (99.99);
-- id auto-generated

INSERT INTO invoices (id, amount) VALUES (100, 49.99);
-- Succeeds: id = 100

Computed Columns (GENERATED ALWAYS AS ... STORED)

Computed columns automatically calculate their value from other columns in the same row. The value is stored on disk and recomputed whenever the source columns change.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC NOT NULL,
  tax_rate NUMERIC NOT NULL DEFAULT 0.08,
  total_price NUMERIC GENERATED ALWAYS AS (price + price * tax_rate) STORED
);

INSERT INTO products (name, price) VALUES ('Widget', 100.00);
-- total_price is automatically computed: 100 + 100 * 0.08 = 108.00

SELECT name, price, tax_rate, total_price FROM products;
-- Widget | 100.00 | 0.08 | 108.00

You cannot manually insert or update a generated column:

INSERT INTO products (name, price, total_price) VALUES ('Gadget', 50.00, 999.99);
-- ERROR: cannot insert a non-DEFAULT value into column "total_price"

More examples:

CREATE TABLE people (
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

CREATE TABLE rectangles (
  id SERIAL PRIMARY KEY,
  width NUMERIC NOT NULL,
  height NUMERIC NOT NULL,
  area NUMERIC GENERATED ALWAYS AS (width * height) STORED,
  perimeter NUMERIC GENERATED ALWAYS AS (2 * (width + height)) STORED
);

Limitations of Generated Columns

  1. The expression can only reference columns in the same table (not other tables).
  2. The expression cannot reference other generated columns.
  3. The expression cannot use subqueries or aggregate functions.
  4. PostgreSQL only supports STORED generated columns (computed at write time). Some other databases support VIRTUAL columns (computed at read time), but PostgreSQL does not yet.

Best Practices

  1. Default to NOT NULL. Start with NOT NULL on every column, then remove it only when you have a clear reason to allow NULLs. This prevents many bugs caused by unexpected NULLs.

  2. Use DEFAULT for timestamps. Always use DEFAULT NOW() or DEFAULT CURRENT_TIMESTAMP for created_at columns. This eliminates the need for application code to set the timestamp.

  3. Pair NOT NULL with DEFAULT. If a column has a sensible default value, use both constraints together. This simplifies INSERT statements and ensures data consistency.

  4. Prefer GENERATED ALWAYS AS IDENTITY over SERIAL. The identity column syntax is the SQL standard and provides better control. SERIAL is a PostgreSQL-specific shorthand with some quirks around ownership and permissions.

  5. Use computed columns for derived data. If a value can always be computed from other columns (like full_name from first_name and last_name), use a GENERATED column to ensure consistency.

Try It Yourself

Think through how you would design these schemas:

  1. A blog_posts table with title (required), body (required), status (defaults to "draft"), published_at (optional, NULL when not published), and created_at (auto-filled with current time).

  2. A shapes table for circles with radius (required, must be positive) and a generated area column that computes pi * radius^2. (Hint: use 3.14159 for pi.)

  3. A users table with an identity column for id, a required email, an is_active flag defaulting to true, and a login_count defaulting to 0.

Take Quiz Next Lesson