PostgreSQL reference

PostgreSQL cheat sheet

Copy working examples for SELECT, filters, joins, inserts, updates, CTEs, window functions, JSONB, arrays, indexes, and routine database administration. The examples use PostgreSQL syntax and common sample tables such as users, orders, and products.

SELECT

Retrieve data from tables

Select All Columns

Retrieve all columns from a table

SELECT * FROM users;

Select Specific Columns

Retrieve only the columns you need

SELECT name, email, created_at
FROM users;

Select Distinct Values

Remove duplicate rows from results

SELECT DISTINCT country
FROM users;

Column Aliases

Rename columns in output using AS

SELECT
  first_name AS "First Name",
  last_name AS "Last Name",
  email AS "Email Address"
FROM users;

Concatenate Columns

Combine multiple columns into one

SELECT
  first_name || ' ' || last_name AS full_name,
  email
FROM users;

CASE Expression

Conditional logic in SELECT

SELECT
  name,
  price,
  CASE
    WHEN price < 10 THEN 'Budget'
    WHEN price < 50 THEN 'Standard'
    ELSE 'Premium'
  END AS price_tier
FROM products;

Handle NULL Values

Return first non-null value

SELECT
  name,
  COALESCE(nickname, name) AS display_name,
  COALESCE(phone, 'No phone') AS phone
FROM users;

NULLIF Function

Return NULL if two values are equal

SELECT
  name,
  NULLIF(status, 'inactive') AS active_status
FROM users;

WHERE

Filter rows with conditions

Equal Comparison

Filter rows matching exact value

SELECT * FROM users
WHERE status = 'active';

Comparison Operators

Use >, <, >=, <=, <> for comparisons

SELECT * FROM products
WHERE price >= 100
  AND price <= 500;

BETWEEN Range

Filter values within a range (inclusive)

SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

IN List

Match any value in a list

SELECT * FROM users
WHERE country IN ('USA', 'Canada', 'UK');

LIKE Pattern Matching

Match patterns with wildcards

-- % matches any sequence
SELECT * FROM users WHERE email LIKE '%@gmail.com';
 
-- _ matches single character
SELECT * FROM products WHERE sku LIKE 'ABC-___';

ILIKE (Case Insensitive)

Pattern matching ignoring case

SELECT * FROM users
WHERE name ILIKE '%john%';

NULL Checks

Check for NULL or NOT NULL

-- Find rows with NULL
SELECT * FROM users WHERE phone IS NULL;
 
-- Find rows without NULL
SELECT * FROM users WHERE phone IS NOT NULL;

AND / OR Logic

Combine multiple conditions

SELECT * FROM users
WHERE (status = 'active' OR status = 'pending')
  AND created_at > '2024-01-01';

NOT Operator

Negate conditions

SELECT * FROM users
WHERE NOT (status = 'deleted' OR status = 'banned');

EXISTS Subquery

Check if subquery returns rows

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
);

ANY / SOME Operator

Compare against any subquery value

-- Equal to any value
SELECT * FROM products
WHERE price = ANY(SELECT price FROM featured_products);
 
-- Greater than any (same as > MIN)
SELECT * FROM products
WHERE price > ANY(SELECT price FROM budget_products);

ALL Operator

Compare against all subquery values

-- Greater than all values
SELECT * FROM products
WHERE price > ALL(SELECT price FROM budget_products);
 
-- Not equal to all (exclude all matches)
SELECT * FROM users
WHERE id <> ALL(SELECT user_id FROM banned_users);

ORDER & LIMIT

Sort and paginate results

Sort Ascending

Sort results A-Z, smallest to largest

SELECT * FROM users
ORDER BY created_at ASC;

Sort Descending

Sort results Z-A, largest to smallest

SELECT * FROM products
ORDER BY price DESC;

Multi-Column Sort

Sort by multiple columns

SELECT * FROM users
ORDER BY country ASC, created_at DESC;

NULL Ordering

Control where NULLs appear

SELECT * FROM users
ORDER BY phone NULLS LAST;
 
-- Or put NULLs first
SELECT * FROM users
ORDER BY phone NULLS FIRST;

LIMIT Results

Return only first N rows

SELECT * FROM products
ORDER BY created_at DESC
LIMIT 10;

OFFSET for Pagination

Skip rows for pagination

-- Page 1 (rows 1-10)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;
 
-- Page 2 (rows 11-20)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;
 
-- Page 3 (rows 21-30)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

FETCH FIRST (SQL Standard)

Standard SQL alternative to LIMIT

SELECT * FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS ONLY;

Aggregates

COUNT, SUM, AVG, MIN, MAX

COUNT All Rows

Count total number of rows

SELECT COUNT(*) AS total_users
FROM users;

COUNT Distinct

Count unique values

SELECT COUNT(DISTINCT country) AS countries
FROM users;

SUM Values

Calculate total of numeric column

SELECT SUM(amount) AS total_revenue
FROM orders
WHERE status = 'completed';

AVG Average

Calculate average of numeric column

SELECT AVG(price) AS average_price
FROM products;

MIN and MAX

Find smallest and largest values

SELECT
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive
FROM products;

Aggregate with FILTER

Apply conditions to aggregates

SELECT
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'pending') AS pending
FROM orders;

GROUP BY

Group rows and aggregate

Basic Grouping

Group rows by column value

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

Group by Multiple Columns

Create groups from column combinations

SELECT
  country,
  status,
  COUNT(*) AS user_count
FROM users
GROUP BY country, status;

HAVING Filter

Filter groups after aggregation

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

ROLLUP Subtotals

Generate subtotals and grand total

SELECT
  country,
  city,
  COUNT(*) AS user_count
FROM users
GROUP BY ROLLUP(country, city);

CUBE All Combinations

Generate all grouping combinations

SELECT
  country,
  status,
  COUNT(*) AS user_count
FROM users
GROUP BY CUBE(country, status);

JOINs

Combine data from multiple tables

INNER JOIN

Return matching rows from both tables

SELECT
  users.name,
  orders.id AS order_id,
  orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN

All rows from left, matching from right

SELECT
  users.name,
  orders.id AS order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

RIGHT JOIN

Matching from left, all from right

SELECT
  users.name,
  orders.id AS order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

FULL OUTER JOIN

All rows from both tables

SELECT
  users.name,
  orders.id AS order_id
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

CROSS JOIN

Cartesian product of all rows

SELECT
  colors.name AS color,
  sizes.name AS size
FROM colors
CROSS JOIN sizes;

Self JOIN

Join table to itself

SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Multiple JOINs

Join more than two tables

SELECT
  users.name,
  orders.id AS order_id,
  products.name AS product
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

JOIN with USING

Shorthand when column names match

SELECT users.name, orders.total
FROM users
INNER JOIN orders USING (user_id);

NATURAL JOIN

Auto-join on matching column names

-- Joins on all columns with same name
SELECT * FROM orders
NATURAL JOIN order_items;
 
-- Be careful: joins on ALL matching names
-- Prefer explicit JOIN conditions

Subqueries

Nested queries within queries

Subquery in WHERE

Use query result as filter condition

SELECT * FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

Subquery with IN

Match against subquery results

SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE total > 1000
);

Subquery in FROM

Use subquery as a derived table

SELECT
  country,
  avg_order
FROM (
  SELECT
    u.country,
    AVG(o.total) AS avg_order
  FROM users u
  JOIN orders o ON u.id = o.user_id
  GROUP BY u.country
) AS country_stats
WHERE avg_order > 500;

Scalar Subquery in SELECT

Return single value in column

SELECT
  name,
  price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

Correlated Subquery

Subquery referencing outer query

SELECT * FROM products p
WHERE price > (
  SELECT AVG(price)
  FROM products
  WHERE category_id = p.category_id
);

LATERAL Join

Subquery can reference preceding tables

SELECT
  u.name,
  recent.order_date,
  recent.total
FROM users u
CROSS JOIN LATERAL (
  SELECT order_date, total
  FROM orders
  WHERE user_id = u.id
  ORDER BY order_date DESC
  LIMIT 3
) AS recent;

NOT EXISTS

Check subquery returns no rows

-- Users with no orders
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
);

CTEs

Common Table Expressions (WITH)

Basic CTE

Define a named subquery

WITH active_users AS (
  SELECT * FROM users
  WHERE status = 'active'
)
SELECT * FROM active_users
WHERE created_at > '2024-01-01';

Multiple CTEs

Chain multiple CTEs together

WITH
  active_users AS (
    SELECT * FROM users WHERE status = 'active'
  ),
  user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
  )
SELECT
  u.name,
  COALESCE(o.order_count, 0) AS orders
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id;

Recursive CTE

Query hierarchical or tree data

WITH RECURSIVE org_chart AS (
  -- Base case: top-level (no manager)
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
 
  UNION ALL
 
  -- Recursive case
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

Generate Number Series

Create sequence with recursive CTE

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers;

Window Functions

ROW_NUMBER, RANK, LAG, LEAD

ROW_NUMBER

Assign sequential numbers to rows

SELECT
  name,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;

RANK vs DENSE_RANK

Handle ties differently

SELECT
  name,
  price,
  RANK() OVER (ORDER BY price DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

PARTITION BY

Window function per group

SELECT
  category,
  name,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS category_rank
FROM products;

LAG and LEAD

Access previous/next row values

SELECT
  order_date,
  total,
  LAG(total) OVER (ORDER BY order_date) AS prev_total,
  LEAD(total) OVER (ORDER BY order_date) AS next_total
FROM orders;

Running Total

Cumulative sum with SUM window

SELECT
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

Moving Average

Average over sliding window

SELECT
  order_date,
  total,
  AVG(total) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7day
FROM orders;

FIRST_VALUE / LAST_VALUE

Get first or last value in window

SELECT
  name,
  price,
  FIRST_VALUE(name) OVER (ORDER BY price DESC) AS most_expensive,
  LAST_VALUE(name) OVER (
    ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS cheapest
FROM products;

NTILE Buckets

Divide rows into N equal groups

SELECT
  name,
  price,
  NTILE(4) OVER (ORDER BY price) AS quartile
FROM products;

String Functions

Text manipulation functions

CONCAT / ||

Join strings together

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  first_name || ' ' || last_name AS full_name_alt
FROM users;

UPPER / LOWER

Change case

SELECT
  UPPER(name) AS uppercase,
  LOWER(email) AS lowercase,
  INITCAP(name) AS titlecase
FROM users;

SUBSTRING

Extract part of string

SELECT
  SUBSTRING(phone FROM 1 FOR 3) AS area_code,
  SUBSTRING(email FROM '@(.+)$') AS domain
FROM users;

TRIM / LTRIM / RTRIM

Remove whitespace or characters

SELECT
  TRIM('  hello  ') AS trimmed,
  LTRIM('  hello  ') AS left_trimmed,
  RTRIM('  hello  ') AS right_trimmed,
  TRIM(BOTH '-' FROM '--hello--') AS custom_trim
FROM users;

REPLACE

Replace substring with another

SELECT
  REPLACE(phone, '-', '') AS phone_digits,
  REPLACE(name, 'Mr. ', '') AS name_no_title
FROM users;

SPLIT_PART

Split string and get part

SELECT
  email,
  SPLIT_PART(email, '@', 1) AS username,
  SPLIT_PART(email, '@', 2) AS domain
FROM users;

LENGTH / CHAR_LENGTH

Get string length

SELECT
  name,
  LENGTH(name) AS name_length,
  CHAR_LENGTH(name) AS char_count
FROM users;

POSITION / STRPOS

Find substring position

SELECT
  email,
  POSITION('@' IN email) AS at_position,
  STRPOS(email, '@') AS at_position_alt
FROM users;

Date & Time

Date and time functions

Current Date/Time

Get current timestamp

SELECT
  CURRENT_DATE AS today,
  CURRENT_TIME AS now_time,
  CURRENT_TIMESTAMP AS now_full,
  NOW() AS now_func;

EXTRACT Parts

Get specific date/time parts

SELECT
  created_at,
  EXTRACT(YEAR FROM created_at) AS year,
  EXTRACT(MONTH FROM created_at) AS month,
  EXTRACT(DAY FROM created_at) AS day,
  EXTRACT(HOUR FROM created_at) AS hour
FROM orders;

DATE_TRUNC

Truncate to specified precision

SELECT
  created_at,
  DATE_TRUNC('month', created_at) AS month_start,
  DATE_TRUNC('year', created_at) AS year_start,
  DATE_TRUNC('hour', created_at) AS hour_start
FROM orders;

Date Arithmetic

Add or subtract intervals

SELECT
  NOW() AS now,
  NOW() + INTERVAL '1 day' AS tomorrow,
  NOW() - INTERVAL '1 week' AS last_week,
  NOW() + INTERVAL '3 months' AS three_months
FROM orders;

AGE Function

Calculate difference between dates

SELECT
  name,
  birth_date,
  AGE(birth_date) AS age,
  AGE(NOW(), hire_date) AS tenure
FROM employees;

TO_CHAR Formatting

Format date as string

SELECT
  created_at,
  TO_CHAR(created_at, 'YYYY-MM-DD') AS iso_date,
  TO_CHAR(created_at, 'Mon DD, YYYY') AS pretty_date,
  TO_CHAR(created_at, 'HH24:MI:SS') AS time_24h
FROM orders;

Generate Date Series

Create sequence of dates

SELECT date::date
FROM generate_series(
  '2024-01-01'::date,
  '2024-12-31'::date,
  '1 month'::interval
) AS date;

JSON

JSON data manipulation

Access JSON Fields

Use -> and ->> operators

SELECT
  data->'name' AS name_json,          -- returns JSON
  data->>'name' AS name_text,         -- returns text
  data->'address'->>'city' AS city    -- nested access
FROM users;

JSON Path

Use #> and #>> for path access

SELECT
  data #> '{address,city}' AS city_json,
  data #>> '{address,city}' AS city_text,
  data #>> '{tags,0}' AS first_tag
FROM users;

JSONB Containment

Check if JSON contains value

-- Contains this JSON?
SELECT * FROM products
WHERE data @> '{"status": "active"}';
 
-- Is contained by?
SELECT * FROM products
WHERE '{"status": "active", "type": "book"}' @> data;

Build JSON Objects

Create JSON from columns

SELECT
  json_build_object(
    'id', id,
    'name', name,
    'email', email
  ) AS user_json
FROM users;

JSON Aggregation

Aggregate rows into JSON array

SELECT
  category,
  json_agg(name) AS product_names,
  json_agg(json_build_object('name', name, 'price', price)) AS products
FROM products
GROUP BY category;

JSONB_SET Update

Update nested JSON value

UPDATE users
SET data = jsonb_set(
  data,
  '{address,city}',
  '"New York"'
)
WHERE id = 1;

INSERT / UPDATE / DELETE

Modify data in tables

Basic INSERT

Insert a single row

INSERT INTO users (name, email, status)
VALUES ('John Doe', 'john@example.com', 'active');

Insert Multiple Rows

Insert several rows at once

INSERT INTO users (name, email)
VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Carol', 'carol@example.com');

INSERT with RETURNING

Get inserted data back

INSERT INTO users (name, email)
VALUES ('John', 'john@example.com')
RETURNING id, name, created_at;

INSERT from SELECT

Insert rows from a query

INSERT INTO users_archive (name, email)
SELECT name, email
FROM users
WHERE status = 'inactive';

UPSERT (ON CONFLICT)

Insert or update if exists

INSERT INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
    email = EXCLUDED.email;

INSERT or Ignore

Skip if already exists

INSERT INTO users (email, name)
VALUES ('john@example.com', 'John')
ON CONFLICT (email) DO NOTHING;

Basic UPDATE

Update rows matching condition

UPDATE users
SET status = 'inactive',
    updated_at = NOW()
WHERE last_login < '2024-01-01';

UPDATE with RETURNING

Get updated rows back

UPDATE products
SET price = price * 1.10
WHERE category = 'electronics'
RETURNING id, name, price;

UPDATE with FROM

Update using another table

UPDATE orders
SET status = 'shipped'
FROM shipments
WHERE orders.id = shipments.order_id
  AND shipments.shipped_at IS NOT NULL;

Basic DELETE

Delete rows matching condition

DELETE FROM users
WHERE status = 'deleted'
  AND updated_at < NOW() - INTERVAL '30 days';

DELETE with RETURNING

Get deleted rows back

DELETE FROM sessions
WHERE expires_at < NOW()
RETURNING id, user_id;

TRUNCATE Table

Delete all rows quickly

-- Fast delete all rows (resets sequences)
TRUNCATE TABLE logs;
 
-- Truncate multiple tables
TRUNCATE TABLE orders, order_items
RESTART IDENTITY CASCADE;

Tables & DDL

CREATE, ALTER, DROP structures

CREATE TABLE

Create a new table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE IF NOT EXISTS

Create table only if missing

CREATE TABLE IF NOT EXISTS logs (
  id SERIAL PRIMARY KEY,
  message TEXT,
  level VARCHAR(20),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE AS

Create table from query

CREATE TABLE active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

Add Column

Add a new column to table

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);
 
-- With default value
ALTER TABLE users
ADD COLUMN is_verified BOOLEAN DEFAULT false;

Drop Column

Remove a column from table

ALTER TABLE users
DROP COLUMN phone;
 
-- Drop if exists
ALTER TABLE users
DROP COLUMN IF EXISTS temp_field;

Rename Column / Table

Rename columns or tables

-- Rename column
ALTER TABLE users
RENAME COLUMN name TO full_name;
 
-- Rename table
ALTER TABLE users
RENAME TO customers;

Change Column Type

Modify column data type

ALTER TABLE products
ALTER COLUMN price TYPE NUMERIC(10,2);
 
-- With conversion
ALTER TABLE users
ALTER COLUMN age TYPE INTEGER
USING age::INTEGER;

Set / Drop Default

Change column default value

-- Set default
ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'pending';
 
-- Remove default
ALTER TABLE users
ALTER COLUMN status DROP DEFAULT;

DROP TABLE

Delete a table

DROP TABLE users;
 
-- Drop if exists
DROP TABLE IF EXISTS temp_users;
 
-- Drop with dependent objects
DROP TABLE orders CASCADE;

CREATE INDEX

Create index for faster queries

-- Basic index
CREATE INDEX idx_users_email ON users(email);
 
-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);
 
-- Multi-column index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

DROP INDEX

Remove an index

DROP INDEX idx_users_email;
 
DROP INDEX IF EXISTS idx_temp;

CREATE VIEW

Create a virtual table

CREATE VIEW active_orders AS
SELECT o.id, u.name, o.total, o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'active';
 
-- Then query like a table
SELECT * FROM active_orders;

Constraints

PRIMARY KEY, FOREIGN KEY, UNIQUE

PRIMARY KEY

Unique identifier for rows

-- Inline definition
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);
 
-- Named constraint
CREATE TABLE products (
  id INTEGER,
  sku VARCHAR(50),
  CONSTRAINT pk_products PRIMARY KEY (id)
);

FOREIGN KEY

Reference another table

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total DECIMAL(10,2)
);
 
-- With ON DELETE action
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER REFERENCES posts(id)
    ON DELETE CASCADE,
  content TEXT
);

UNIQUE Constraint

Prevent duplicate values

-- Single column
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);
 
-- Multiple columns
CREATE TABLE memberships (
  user_id INTEGER,
  group_id INTEGER,
  UNIQUE (user_id, group_id)
);

NOT NULL

Require a value

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL
);

CHECK Constraint

Validate column values

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2) CHECK (price > 0),
  quantity INTEGER CHECK (quantity >= 0)
);
 
-- Named check
CREATE TABLE users (
  age INTEGER,
  CONSTRAINT valid_age CHECK (age >= 0 AND age < 150)
);

DEFAULT Value

Set automatic default

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  status VARCHAR(20) DEFAULT 'draft',
  views INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

Add Constraint to Existing Table

Add constraints with ALTER

-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id);
 
-- Add unique constraint
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);
 
-- Add check constraint
ALTER TABLE products
ADD CONSTRAINT positive_price CHECK (price > 0);

Drop Constraint

Remove a constraint

ALTER TABLE orders
DROP CONSTRAINT fk_orders_user;
 
ALTER TABLE users
DROP CONSTRAINT IF EXISTS unique_email;

Set Operations

UNION, INTERSECT, EXCEPT

UNION

Combine results, remove duplicates

SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;

UNION ALL

Combine results, keep duplicates

SELECT product_id, 'sale' AS type FROM sales
UNION ALL
SELECT product_id, 'return' AS type FROM returns;

INTERSECT

Rows in both queries

SELECT user_id FROM premium_users
INTERSECT
SELECT user_id FROM active_users;

EXCEPT

Rows in first but not second

SELECT user_id FROM all_users
EXCEPT
SELECT user_id FROM banned_users;

ORDER with Set Operations

Sort combined results

(SELECT name, 'customer' AS type FROM customers)
UNION
(SELECT name, 'supplier' AS type FROM suppliers)
ORDER BY name;

Type Casting

Convert between data types

:: Operator

PostgreSQL cast shorthand

SELECT
  '123'::INTEGER AS num,
  '2024-01-15'::DATE AS date,
  123::TEXT AS str,
  '3.14'::NUMERIC AS decimal;

CAST Function

Standard SQL casting

SELECT
  CAST('123' AS INTEGER) AS num,
  CAST('2024-01-15' AS DATE) AS date,
  CAST(price AS INTEGER) AS rounded
FROM products;

Numeric Conversions

Convert between number types

SELECT
  1234.567::INTEGER AS truncated,    -- 1234
  1234.567::NUMERIC(10,1) AS rounded, -- 1234.6
  '99.99'::DECIMAL(5,2) AS decimal;

Date/Time Conversions

Convert date and time types

SELECT
  NOW()::DATE AS today,
  NOW()::TIME AS current_time,
  '2024-01-15 10:30:00'::TIMESTAMP AS ts,
  EXTRACT(EPOCH FROM NOW())::INTEGER AS unix;

Boolean Conversions

Convert to/from boolean

SELECT
  'true'::BOOLEAN,
  'yes'::BOOLEAN,
  '1'::BOOLEAN,
  1::BOOLEAN,
  true::INTEGER;  -- 1

JSON Conversions

Convert to/from JSON

SELECT
  '{"name": "John"}'::JSON,
  '{"name": "John"}'::JSONB,
  row_to_json(users.*) FROM users;

Numeric Functions

ROUND, ABS, CEIL, FLOOR

ROUND

Round to decimal places

SELECT
  ROUND(3.14159) AS whole,         -- 3
  ROUND(3.14159, 2) AS two_dec,    -- 3.14
  ROUND(1234.5, -2) AS hundreds;   -- 1200

CEIL / FLOOR

Round up or down

SELECT
  CEIL(4.2) AS ceiling,   -- 5
  FLOOR(4.8) AS floor,    -- 4
  CEIL(-4.2) AS neg_ceil, -- -4
  FLOOR(-4.8) AS neg_fl;  -- -5

TRUNC

Truncate toward zero

SELECT
  TRUNC(4.9) AS truncated,     -- 4
  TRUNC(-4.9) AS neg_trunc,    -- -4
  TRUNC(123.456, 1) AS one_dec; -- 123.4

ABS

Absolute value

SELECT
  ABS(-5) AS positive,     -- 5
  ABS(5) AS still_pos,     -- 5
  ABS(price - 100) AS diff
FROM products;

POWER / SQRT

Exponents and square root

SELECT
  POWER(2, 3) AS eight,       -- 8
  POWER(10, 2) AS hundred,    -- 100
  SQRT(16) AS four,           -- 4
  SQRT(2) AS root2;           -- 1.414...

MOD / %

Remainder after division

SELECT
  MOD(10, 3) AS remainder,    -- 1
  17 % 5 AS mod_operator,     -- 2
  MOD(id, 2) AS is_odd
FROM users;

RANDOM

Generate random numbers

SELECT
  RANDOM() AS zero_to_one,
  FLOOR(RANDOM() * 100) AS zero_to_99,
  FLOOR(RANDOM() * (max - min + 1) + min) AS range;
 
-- Random row
SELECT * FROM users
ORDER BY RANDOM()
LIMIT 1;

SIGN / DIV

Sign and integer division

SELECT
  SIGN(-5) AS negative,   -- -1
  SIGN(0) AS zero,        -- 0
  SIGN(5) AS positive,    -- 1
  DIV(17, 5) AS int_div;  -- 3

Arrays

PostgreSQL array operations

Array Literals

Create array values

SELECT
  ARRAY[1, 2, 3] AS int_array,
  ARRAY['a', 'b', 'c'] AS text_array,
  '{1,2,3}'::INTEGER[] AS cast_array;

Access Array Elements

Get elements by index (1-based)

SELECT
  (ARRAY['a','b','c'])[1] AS first,   -- 'a'
  (ARRAY['a','b','c'])[2:3] AS slice, -- {'b','c'}
  tags[1] AS first_tag
FROM posts;

ANY / ALL with Arrays

Compare against array values

-- Match any value in array
SELECT * FROM users
WHERE status = ANY(ARRAY['active', 'pending']);
 
-- Match all values
SELECT * FROM products
WHERE price > ALL(ARRAY[10, 20, 30]);

Array Contains

Check array containment

-- Contains all elements
SELECT * FROM posts
WHERE tags @> ARRAY['sql', 'database'];
 
-- Is contained by
SELECT * FROM posts
WHERE tags <@ ARRAY['sql', 'database', 'postgresql'];
 
-- Overlaps (has common elements)
SELECT * FROM posts
WHERE tags && ARRAY['sql', 'nosql'];

ARRAY_AGG

Aggregate values into array

SELECT
  category,
  ARRAY_AGG(name) AS product_names,
  ARRAY_AGG(DISTINCT status) AS statuses
FROM products
GROUP BY category;

UNNEST

Expand array to rows

SELECT UNNEST(ARRAY[1, 2, 3]) AS num;
 
-- Expand column array
SELECT id, UNNEST(tags) AS tag
FROM posts;

Array Functions

Common array operations

SELECT
  ARRAY_LENGTH(ARRAY[1,2,3], 1) AS len,    -- 3
  ARRAY_CAT(ARRAY[1,2], ARRAY[3,4]) AS concat, -- {1,2,3,4}
  ARRAY_APPEND(ARRAY[1,2], 3) AS append,   -- {1,2,3}
  ARRAY_REMOVE(ARRAY[1,2,3], 2) AS removed; -- {1,3}

Array Search

Find elements in arrays

SELECT
  ARRAY_POSITION(ARRAY['a','b','c'], 'b') AS pos, -- 2
  'b' = ANY(ARRAY['a','b','c']) AS exists,        -- true
  CARDINALITY(ARRAY[1,2,3]) AS size;              -- 3