PostgreSQL

An extension of A Precis of SQL but now with specific regard to more realistic backend usage.

General

Create

CREATE object_type object_name [... options];

Object Types

Object Type	Meaning	Example
TABLE PostgreSQL	Creates a new database table	CREATE TABLE users (id SERIAL, name TEXT);
TYPE PostgreSQL	Defines a custom enum or composite type	CREATE TYPE mood AS ENUM ('happy', 'sad');
INDEX PostgreSQL	Creates an index to speed queries	CREATE INDEX idx_users_name ON users(name);
POLICY PostgreSQL	Row-level security policy	CREATE POLICY user_policy ON users FOR SELECT USING (id = auth.uid());
TRIGGER	Executes code automatically on database events	CREATE TRIGGER update_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_modified_column();
DATABASE	Creates a new database	CREATE DATABASE my_database;
VIEW	Creates a virtual table (a query stored as a table)	CREATE VIEW user_names AS SELECT name FROM users;
FUNCTION	Defines reusable functions	CREATE FUNCTION add_nums(a int, b int) RETURNS int AS $$ SELECT a+b; $$ LANGUAGE SQL;
SCHEMA	Defines a namespace to organize tables	CREATE SCHEMA analytics;
EXTENSION	Installs an external PostgreSQL extension	CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Table

Usage:

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
);

Index

An index is a data structure (usually B-tree) allowing for quick row selection without scanning every row. Great if a column is frequently queried, if the table is large, or column is used for foreign keys. Good examples are email, username, or user_id.

Usage:

CREATE INDEX index_name ON table_name(column_name);

Type

Similar to C (and others), allows the definition of a custom data type beyond the primitive types. They are either 1) enum types or 2) composite types (like a struct).

Usage:

CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TYPE address AS (
  street TEXT,
  city TEXT,
  zipcode INTEGER
);

Policy

TODO

Constraints

Rules defined on columns or table to enforce certain data specifications

Constraint	Meaning
PRIMARY KEY	Ensures a unique identifier for each row.	id SERIAL PRIMARY KEY
FOREIGN KEY	Ensures relational integrity (links rows across tables).	user_id INTEGER REFERENCES users(id)
UNIQUE	Ensures all column values are distinct.	email TEXT UNIQUE
NOT NULL	Ensures a column always has a value (never empty).	price NUMERIC NOT NULL
CHECK	Ensures column values meet certain conditions.	age INT CHECK (age >= 18)
DEFAULT	Assigns a default value if none provided during insertion.	status TEXT DEFAULT 'pending'
Example:

CREATE TABLE customers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  username TEXT UNIQUE NOT NULL,
  email TEXT UNIQUE NOT NULL,
  age INTEGER CHECK (age > 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Primary Key

Identifies each row uniquely, cannot have NULL values, and automatically indexed. Great for identifying a specific row and guaranteeing uniqueness, for example employee_id. A table cannot have multiple primary keys.

Foreign Key

Simply put: A foreign key is a column that refers to a primary key in some other table. In other words, a relationship between tables, ensures values in one table match values in the corresponding table.

Unique

Each value in a column must be distinct, with exception to NULL values. This automatically creates an index.

Not Null

Disallows NULL values in a column, meaning a column must always have data.

Check

Validates column data against specified criteria.

Default

Automatically assigns a value if none is provided.