DDL Data Types and Constraints

Overview

DDL (Data Definition Language): Defines structure — CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language): Works with rows — SELECT, INSERT, UPDATE, DELETE, MERGE (see Inserts Updates Upserts and MERGE)
Postgres Twist: DDL is transactional in Postgres — CREATE/ALTER/DROP can be rolled back (unlike MySQL/Oracle)
Sample Data: Examples use the schema from E-commerce Practice Queries

DDL Commands

CREATE TABLE

CREATE TABLE IF NOT EXISTS coupons (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code        text NOT NULL UNIQUE,
    percent_off numeric(5,2) NOT NULL CHECK (percent_off > 0 AND percent_off <= 100),
    valid_from  timestamptz NOT NULL DEFAULT now(),
    valid_to    timestamptz,
    created_by  bigint REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT valid_range CHECK (valid_to IS NULL OR valid_to > valid_from)
);
 
-- Copy structure (columns, defaults, constraints, indexes) without data
CREATE TABLE coupons_archive (LIKE coupons INCLUDING ALL);
 
-- Create from a query (no constraints/indexes copied)
CREATE TABLE top_products AS
SELECT id, name, price FROM products WHERE price > 500;

ALTER TABLE

ALTER TABLE coupons ADD COLUMN max_uses int;
ALTER TABLE coupons ALTER COLUMN max_uses SET DEFAULT 100;
ALTER TABLE coupons ALTER COLUMN max_uses SET NOT NULL;     -- fails if NULLs exist
ALTER TABLE coupons RENAME COLUMN max_uses TO usage_limit;
ALTER TABLE coupons ALTER COLUMN usage_limit TYPE bigint;    -- may rewrite the table
ALTER TABLE coupons ADD CONSTRAINT usage_positive CHECK (usage_limit > 0);
ALTER TABLE coupons DROP CONSTRAINT usage_positive;
ALTER TABLE coupons DROP COLUMN usage_limit;
ALTER TABLE top_products RENAME TO expensive_products;

DROP and TRUNCATE

DROP TABLE IF EXISTS expensive_products;
DROP TABLE IF EXISTS coupons_archive CASCADE;   -- CASCADE also drops dependent views / FKs
TRUNCATE coupons RESTART IDENTITY;              -- fast delete-all, resets identity counter

Command	Removes	Speed	Fires DELETE triggers	Rollback in PG
DELETE FROM t	Rows (optionally filtered)	Slow (row by row, leaves dead tuples)	Yes	Yes
TRUNCATE t	All rows	Very fast (new empty file)	No	Yes
DROP TABLE t	Table + data + indexes	Fast	No	Yes

Transactional DDL

BEGIN;
ALTER TABLE coupons ADD COLUMN notes text;
-- deploy script failed half way? everything undoes cleanly
ROLLBACK;

Safe Migrations on Big Tables

Adding a Column with a Constant DEFAULT: Instant since PG 11 (no table rewrite)
Adding NOT NULL: Scans the table under an ACCESS EXCLUSIVE lock — add a CHECK (col IS NOT NULL) NOT VALID, VALIDATE it, then SET NOT NULL (PG 12+ uses the validated check to skip the scan)
Adding a FK / CHECK: Use NOT VALID then VALIDATE CONSTRAINT to avoid a long blocking scan
Changing a Column Type: Usually rewrites the table — plan downtime or do add-column + backfill + swap
Lock Timeout: SET lock_timeout = '3s'; before DDL so a migration doesn’t queue behind a long query and block everyone

ALTER TABLE coupons ADD CONSTRAINT coupons_creator_fk_v2
    FOREIGN KEY (created_by) REFERENCES users(id) NOT VALID;   -- instant, checks new rows only
ALTER TABLE coupons VALIDATE CONSTRAINT coupons_creator_fk_v2;  -- scans, but allows reads/writes
ALTER TABLE coupons DROP CONSTRAINT coupons_creator_fk_v2;

Data Types

Integers and IDs

Type	Size	Range	Use
smallint	2 B	±32K	Small enums, counters
integer / int	4 B	±2.1 billion	Most counts
bigint	8 B	±9.2 × 10¹⁸	IDs of anything that grows
numeric(p,s)	Variable	Exact, up to 131072 digits	Money, quantities that must be exact
real / double precision	4 / 8 B	Approximate (IEEE 754)	Measurements, science — never money

SELECT 0.1::float8 + 0.2::float8 = 0.3 AS float_equal,     -- false
       0.1::numeric + 0.2::numeric = 0.3 AS numeric_equal;  -- true

serial vs IDENTITY

serial / bigserial: Old Postgres shorthand — creates a sequence + DEFAULT nextval(...). Not SQL standard; the sequence is a separate object with its own permissions
GENERATED ALWAYS AS IDENTITY: SQL-standard, PG 10+. Sequence is owned by the column; manual inserts of id are rejected unless you say OVERRIDING SYSTEM VALUE
GENERATED BY DEFAULT AS IDENTITY: Like serial — allows explicit values (handy for data imports)
Recommendation: Use bigint GENERATED ALWAYS AS IDENTITY for new tables

CREATE TABLE id_demo (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    note text
);
INSERT INTO id_demo (note) VALUES ('auto id');                        -- id = 1
INSERT INTO id_demo (id, note) OVERRIDING SYSTEM VALUE VALUES (100, 'forced');
-- INSERT INTO id_demo (id, note) VALUES (5, 'x');
--   ERROR: cannot insert a non-DEFAULT value into column "id"
SELECT * FROM id_demo;

UUID

Pros: Globally unique, can be generated by clients, no ID guessing
Cons: 16 bytes vs 8; random v4 UUIDs scatter inserts across the B-tree (page splits, poor cache locality)
Generation: gen_random_uuid() built in since PG 13 (v4). PG 18 adds uuidv7() — time-ordered, index-friendly

CREATE TABLE sessions (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at timestamptz NOT NULL
);
INSERT INTO sessions (user_id, expires_at) VALUES (1, now() + interval '7 days') RETURNING id;

Text: text vs varchar(n) vs char(n)

text: Unlimited length; the idiomatic choice in Postgres
varchar(n): Same storage and speed as text, plus a length check. Changing n later needs ALTER
char(n): Blank-padded to n — almost never what you want (fine for fixed codes like ISO country char(2))
Performance: No difference between text and varchar in Postgres — prefer text + a CHECK (length(col) <= n) if you need a limit

Dates and Times

Type	Stores	Use
timestamptz	An absolute instant (stored as UTC, shown in session `TimeZone`)	Almost everything: created_at, events
timestamp	Wall-clock date + time with no zone	Rare — e.g. “store opens 09:00 local”
date	Calendar day	Birthdays, billing dates
interval	A duration	`now() - interval '30 days'`

SET timezone = 'UTC';
SELECT now()                                         AS now_utc,
       now() AT TIME ZONE 'Asia/Kolkata'             AS ist_wall_clock,
       '2025-03-31'::date + 1                        AS next_day,
       date_trunc('month', timestamptz '2025-03-18 10:00+00') AS month_start,
       age(timestamptz '2025-07-15', timestamptz '2025-01-10') AS age_between,
       extract(dow FROM date '2025-07-15')           AS day_of_week;   -- 0 = Sunday

Boolean, JSONB, Arrays, Enums

boolean: true / false / NULL
jsonb: Binary JSON, indexable — see JSONB and Arrays. Avoid plain json unless you need key order / duplicate keys preserved
Arrays: text[], int[] — great for tags; avoid for relationships you need FKs on
Enum: CREATE TYPE ... AS ENUM — compact and type-safe, but removing/renaming values is awkward. A CHECK (status IN (...)) or a lookup table is easier to evolve

CREATE TYPE ticket_priority AS ENUM ('low', 'medium', 'high');
SELECT 'high'::ticket_priority > 'low'::ticket_priority AS ordered_by_declaration;  -- true

Constraints

Constraint Types

Constraint	Guarantees	Notes
PRIMARY KEY	Unique + NOT NULL; one per table	Creates a unique B-tree index
FOREIGN KEY	Value exists in parent table	Does not auto-create an index on the child column
UNIQUE	No duplicates	NULLs are distinct by default (many NULLs allowed); `UNIQUE NULLS NOT DISTINCT` in PG 15+
CHECK	Boolean expression is not false	Passes when the expression is NULL!
NOT NULL	Column has a value	Cheapest constraint, use liberally
DEFAULT	Value when omitted	Not a constraint, but part of column definition
EXCLUDE	No two rows “overlap”	PG-specific, e.g. no overlapping bookings

Foreign Key ON DELETE Actions

Action	When parent row is deleted	Typical use
NO ACTION (default)	Error if children exist (checked at end of statement)	Safe default
RESTRICT	Error immediately, cannot be deferred	`orders.user_id` — never lose orders
CASCADE	Delete children too	`order_items.order_id` — items die with the order
SET NULL	Set child FK to NULL	`users.referred_by` — keep user, forget referrer
SET DEFAULT	Set child FK to its default	Rare

-- RESTRICT in action: user 1 has orders
-- DELETE FROM users WHERE id = 1;
--   ERROR: update or delete on table "users" violates foreign key constraint "orders_user_id_fkey"
 
-- CASCADE in action: deleting an order removes its items
BEGIN;
DELETE FROM orders WHERE id = 5;
SELECT count(*) AS items_left_for_order_5 FROM order_items WHERE order_id = 5;   -- 0
ROLLBACK;

UNIQUE, CHECK and NULL Gotchas

CREATE TABLE nulls_demo (
    a int UNIQUE,
    b int UNIQUE NULLS NOT DISTINCT,    -- PG 15+
    c int CHECK (c > 0)
);
INSERT INTO nulls_demo (a, b, c) VALUES (NULL, NULL, NULL);   -- ok: CHECK on NULL passes
INSERT INTO nulls_demo (a, b)    VALUES (NULL, 1);             -- ok: a allows many NULLs
-- INSERT INTO nulls_demo (b) VALUES (NULL);
--   ERROR: duplicate key value violates unique constraint "nulls_demo_b_key"
SELECT count(*) FROM nulls_demo;   -- 2

Composite and Partial Uniqueness

-- One active coupon per code, but many expired ones allowed
CREATE UNIQUE INDEX one_live_coupon_per_code ON coupons (code) WHERE valid_to IS NULL;
 
-- Composite PK on a join table
CREATE TABLE product_tags (
    product_id bigint REFERENCES products(id) ON DELETE CASCADE,
    tag        text,
    PRIMARY KEY (product_id, tag)
);

EXCLUDE Constraint (Postgres-only)

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_bookings (
    room   int NOT NULL,
    during tstzrange NOT NULL,
    EXCLUDE USING gist (room WITH =, during WITH &&)   -- same room AND overlapping time = rejected
);
INSERT INTO room_bookings VALUES (1, '[2025-01-01 10:00+00, 2025-01-01 11:00+00)');
INSERT INTO room_bookings VALUES (1, '[2025-01-01 11:00+00, 2025-01-01 12:00+00)');   -- ok, touches
-- INSERT INTO room_bookings VALUES (1, '[2025-01-01 10:30+00, 2025-01-01 11:30+00)');
--   ERROR: conflicting key value violates exclusion constraint

Generated Columns

ALTER TABLE order_items
    ADD COLUMN line_total numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED;
SELECT order_id, product_id, line_total FROM order_items WHERE order_id = 1;
ALTER TABLE order_items DROP COLUMN line_total;

Inspecting Structure

-- psql: \d products   \dt   \di   \d+ orders
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'products'
ORDER BY ordinal_position;
 
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;

Interview Questions

“serial vs identity?”: Identity is SQL standard, owned by the column, ALWAYS blocks accidental manual IDs
“text or varchar(255)?”: Same performance in Postgres; use text unless a length rule is a real business rule
“Why did my CHECK let a NULL through?”: CHECK only rejects false; NULL is “unknown” → add NOT NULL
“Does a FK create an index?”: No — index the child column yourself or deletes on the parent scan the child table
“DELETE vs TRUNCATE?”: TRUNCATE is O(1), skips row triggers, takes an ACCESS EXCLUSIVE lock

Summary

DDL: CREATE / ALTER / DROP / TRUNCATE, and all of it is transactional in Postgres
Types: bigint identity IDs, numeric for money, text over varchar, timestamptz over timestamp, jsonb over json
Constraints: Push data rules into the database — PK, FK with the right ON DELETE, UNIQUE, CHECK, NOT NULL
Gotchas: NULL passes CHECK, UNIQUE allows many NULLs, FKs are not indexed automatically
Migrations: NOT VALID + VALIDATE, lock_timeout, and avoid table rewrites on hot tables

Kaushik's Notes

Explorer

DDL Data Types and Constraints

Overview

DDL Commands

CREATE TABLE

ALTER TABLE

DROP and TRUNCATE

Transactional DDL

Safe Migrations on Big Tables

Data Types

Integers and IDs

serial vs IDENTITY

UUID

Text: text vs varchar(n) vs char(n)

Dates and Times

Boolean, JSONB, Arrays, Enums

Constraints

Constraint Types

Foreign Key ON DELETE Actions

UNIQUE, CHECK and NULL Gotchas

Composite and Partial Uniqueness

EXCLUDE Constraint (Postgres-only)

Generated Columns

Inspecting Structure

Interview Questions

Summary

Graph View

Table of Contents

Backlinks