CREATE TABLE IF NOT EXISTS products ( id BIGSERIAL PRIMARY KEY, sku VARCHAR(50) NOT NULL UNIQUE, name TEXT NOT NULL, price NUMERIC(10,2) NOT NULL CHECK (price >= 0), category_id BIGINT REFERENCES categories(id) ON DELETE SET NULL, attrs JSONB DEFAULT '{}'::jsonb, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
2. Defining Column Data Types
Category
Postgres
MySQL
Integer
SMALLINT, INT, BIGINT, SERIAL
TINYINT, INT, BIGINT, AUTO_INCREMENT
Decimal
NUMERIC(p,s), REAL, DOUBLE PRECISION
DECIMAL(p,s), FLOAT, DOUBLE
String
VARCHAR(n), TEXT, CHAR(n)
VARCHAR(n), TEXT, CHAR(n)
Date/Time
DATE, TIME, TIMESTAMPTZ, INTERVAL
DATE, TIME, DATETIME, TIMESTAMP
Boolean
BOOLEAN
TINYINT(1) / BOOLEAN
JSON
JSONB (binary), JSON (text)
JSON
UUID
UUID
CHAR(36) / BINARY(16)
Binary
BYTEA
BLOB, VARBINARY
Array
type[]
JSON array
3. Setting Column Constraints
Constraint
Syntax
Effect
NOT NULL
NOT NULL
Column must have a value
DEFAULT
DEFAULT expr
Auto-fill on insert
UNIQUE
UNIQUE
No duplicates (NULLs allowed)
PRIMARY KEY
PRIMARY KEY
UNIQUE + NOT NULL
CHECK
CHECK (expr)
Custom validation rule
FOREIGN KEY
REFERENCES t(c)
Referential integrity
GENERATED
GENERATED ALWAYS AS
Computed column
4. Defining Primary Keys
Approach
Pros
Cons
BIGSERIAL / AUTO_INCREMENT
Compact, sequential
Reveals row counts, hard across shards
UUID v4
Globally unique, no central authority
Random → index bloat
UUID v7 NEW
Time-ordered, index-friendly
Newer standard, lib support varies
Snowflake ID
Sortable, distributed
Needs ID service
Composite Key
Natural for junction tables
Larger FK references
5. Creating Foreign Keys
Action
Behavior on Parent Delete/Update
NO ACTION (default)
Error if child rows exist (deferred check)
RESTRICT
Error immediately
CASCADE
Delete/update child rows
SET NULL
Set FK to NULL (column must allow NULL)
SET DEFAULT
Set FK to DEFAULT value
Example: FK with ON DELETE CASCADE
ALTER TABLE order_items ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ON UPDATE RESTRICT;
6. Adding Check Constraints
Pattern
Example
Range
CHECK (age BETWEEN 0 AND 150)
Enum-like
CHECK (status IN ('open','closed','pending'))
Regex (Postgres)
CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$')
Cross-column
CHECK (end_date > start_date)
JSON validation
CHECK (jsonb_typeof(meta->'tags') = 'array')
7. Creating Schemas and Namespaces
Operation
SQL
Create schema
CREATE SCHEMA sales;
Drop schema
DROP SCHEMA sales CASCADE;
Qualify name
sales.invoices
Set search path
SET search_path TO sales, public;
Grant usage
GRANT USAGE ON SCHEMA sales TO role;
8. Altering Tables
Operation
SQL
Add column
ALTER TABLE t ADD COLUMN c TYPE [DEFAULT v];
Drop column
ALTER TABLE t DROP COLUMN c;
Rename column
ALTER TABLE t RENAME COLUMN a TO b;
Change type
ALTER TABLE t ALTER COLUMN c TYPE NEWTYPE USING expr;
Add constraint
ALTER TABLE t ADD CONSTRAINT name CHECK (...);
Drop constraint
ALTER TABLE t DROP CONSTRAINT name;
Rename table
ALTER TABLE t RENAME TO new_t;
Warning: In Postgres < 11, adding a NOT NULL column with default rewrites the whole table. PG 11+ avoids the rewrite for constant defaults.
9. Dropping Tables
Form
Effect
DROP TABLE t;
Error if dependencies exist
DROP TABLE IF EXISTS t;
No error if missing
DROP TABLE t CASCADE;
Also drops dependent FKs/views
TRUNCATE TABLE t;
Empty rows but keep schema (faster than DELETE)
10. Using Temporary Tables
Scope
Lifetime
SESSION (default)
Until disconnect
TRANSACTION
Until COMMIT/ROLLBACK (ON COMMIT DROP)
LOCAL
Visible only to current session
GLOBAL (SQL Server)
Visible to all sessions, dropped when last session ends
Example: Transaction-scoped Temp Table
BEGIN;CREATE TEMP TABLE staging_orders (LIKE orders INCLUDING ALL) ON COMMIT DROP;COPY staging_orders FROM '/tmp/orders.csv' CSV HEADER;INSERT INTO orders SELECT * FROM staging_orders ON CONFLICT (id) DO NOTHING;COMMIT;