PostgreSQL Cheat Sheet

Connection and Basic Commands

Connect to PostgreSQL

# Connect as postgres user
sudo -u postgres psql

# Connect to specific database
psql -d database_name

# Connect with user and host
psql -U username -h hostname -d database_name

# Connect with all options
psql -h hostname -p 5432 -U username -d database_name

# Connect and execute command
psql -U username -d database_name -c "SELECT * FROM table"

# Execute SQL file
psql -U username -d database_name -f file.sql

# Exit psql
\q
exit

psql Meta-Commands

-- Help
\?              -- List all commands
\h              -- SQL help
\h CREATE TABLE -- Help on specific command

-- Database info
\l              -- List all databases
\l+             -- List with sizes and details
\c database     -- Connect to database
\conninfo       -- Current connection info

-- Table info
\dt             -- List tables
\dt+            -- List tables with sizes
\dt schema.*    -- List tables in schema
\d table        -- Describe table
\d+ table       -- Detailed table description
\di             -- List indexes
\dv             -- List views
\df             -- List functions
\dn             -- List schemas
\du             -- List users/roles

-- Display
\x              -- Toggle expanded display
\x auto         -- Auto expanded display
\timing         -- Toggle query timing

-- Output
\o filename     -- Send output to file
\o              -- Reset output to stdout

-- Execution
\i filename     -- Execute commands from file
\e              -- Edit query in editor

-- Variables
\set            -- List variables
\set var value  -- Set variable

Service Management

systemctl Commands (Linux)

# Check status
sudo systemctl status postgresql
sudo systemctl status postgresql@14-main  # Specific version

# Start service
sudo systemctl start postgresql

# Stop service
sudo systemctl stop postgresql

# Restart service
sudo systemctl restart postgresql

# Reload configuration (without restart)
sudo systemctl reload postgresql

# Enable service (start on boot)
sudo systemctl enable postgresql

# Disable service (don't start on boot)
sudo systemctl disable postgresql

# Check if service is enabled
sudo systemctl is-enabled postgresql

# Check if service is active
sudo systemctl is-active postgresql

Service Commands (Ubuntu/Debian)

# Using service command
sudo service postgresql status
sudo service postgresql start
sudo service postgresql stop
sudo service postgresql restart
sudo service postgresql reload

# Using pg_ctlcluster (Debian/Ubuntu specific)
pg_lsclusters                              # List clusters
sudo pg_ctlcluster 14 main start          # Start specific cluster
sudo pg_ctlcluster 14 main stop           # Stop specific cluster
sudo pg_ctlcluster 14 main restart        # Restart specific cluster
sudo pg_ctlcluster 14 main reload         # Reload config
sudo pg_ctlcluster 14 main status         # Check status

pg_ctl Commands (Direct Control)

# Initialize database cluster
initdb -D /var/lib/postgresql/data

# Start PostgreSQL
pg_ctl start -D /var/lib/postgresql/data
pg_ctl start -D /var/lib/postgresql/data -l logfile

# Stop PostgreSQL
pg_ctl stop -D /var/lib/postgresql/data
pg_ctl stop -D /var/lib/postgresql/data -m fast      # Fast shutdown
pg_ctl stop -D /var/lib/postgresql/data -m immediate # Immediate shutdown

# Restart PostgreSQL
pg_ctl restart -D /var/lib/postgresql/data

# Reload configuration
pg_ctl reload -D /var/lib/postgresql/data

# Check status
pg_ctl status -D /var/lib/postgresql/data

Check PostgreSQL Version

# From command line
psql --version
postgres --version

# From within psql
SELECT version();

# Check server version
psql -c "SELECT version();"

# Show all version info
SHOW server_version;
SHOW server_version_num;

Configuration Files

# Location of configuration files
psql -c "SHOW config_file;"              # postgresql.conf
psql -c "SHOW hba_file;"                 # pg_hba.conf
psql -c "SHOW data_directory;"           # Data directory

# Common locations
# Ubuntu/Debian: /etc/postgresql/14/main/
# RHEL/CentOS: /var/lib/pgsql/14/data/
# macOS (Homebrew): /usr/local/var/postgres/

# Edit main configuration
sudo nano /etc/postgresql/14/main/postgresql.conf

# Edit client authentication
sudo nano /etc/postgresql/14/main/pg_hba.conf

# After editing configs, reload
sudo systemctl reload postgresql

Logs

# View logs
sudo tail -f /var/log/postgresql/postgresql-14-main.log

# On Ubuntu/Debian
sudo journalctl -u postgresql
sudo journalctl -u postgresql -f        # Follow logs
sudo journalctl -u postgresql --since today

# Check log location from PostgreSQL
psql -c "SHOW log_directory;"
psql -c "SHOW log_filename;"

Auto-start Configuration

# Enable PostgreSQL to start on boot
sudo systemctl enable postgresql

# Disable auto-start
sudo systemctl disable postgresql

# Check auto-start status
sudo systemctl is-enabled postgresql

# Ubuntu/Debian specific
sudo update-rc.d postgresql enable
sudo update-rc.d postgresql disable

Multiple PostgreSQL Versions

# List all installed versions (Ubuntu/Debian)
pg_lsclusters

# Start specific version
sudo systemctl start postgresql@14-main
sudo systemctl start postgresql@15-main

# Stop specific version
sudo systemctl stop postgresql@14-main

# Switch default version
sudo pg_createcluster 15 main
sudo pg_dropcluster 14 main

Database Operations

Create and Manage Databases

-- Create database
CREATE DATABASE database_name;

-- Create with owner
CREATE DATABASE database_name OWNER username;

-- Create with encoding
CREATE DATABASE database_name
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

-- List all databases
\l
SELECT datname FROM pg_database;

-- Show current database
SELECT current_database();

-- Drop database
DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

-- Rename database
ALTER DATABASE old_name RENAME TO new_name;

-- Change owner
ALTER DATABASE database_name OWNER TO new_owner;

-- Database size
SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

-- Single database size
SELECT pg_size_pretty(pg_database_size('database_name'));

Schema Management

Create and Manage Schemas

-- Create schema
CREATE SCHEMA schema_name;
CREATE SCHEMA IF NOT EXISTS schema_name;

-- Create schema with owner
CREATE SCHEMA schema_name AUTHORIZATION username;

-- List schemas
\dn
SELECT schema_name FROM information_schema.schemata;

-- Set search path
SET search_path TO schema_name, public;
SHOW search_path;

-- Drop schema
DROP SCHEMA schema_name;
DROP SCHEMA schema_name CASCADE;  -- Drop with all objects

-- Rename schema
ALTER SCHEMA old_name RENAME TO new_name;

Table Operations

Create Tables

-- Basic table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with constraints
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER DEFAULT 1 CHECK (quantity > 0),
    price NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Create table with check constraint
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0),
    category VARCHAR(50),
    CONSTRAINT unique_name_category UNIQUE (name, category)
);

-- Create table from query
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE condition;

-- Create table like another (structure only)
CREATE TABLE new_table (LIKE old_table INCLUDING ALL);

-- Temporary table
CREATE TEMPORARY TABLE temp_table (
    id INTEGER,
    name VARCHAR(50)
);

-- Unlogged table (faster, no crash recovery)
CREATE UNLOGGED TABLE logs (
    id SERIAL,
    message TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Alter Tables

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 18;

-- Drop column
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;

-- Rename column
ALTER TABLE users RENAME COLUMN old_name TO new_name;

-- Change column type
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);

-- Set default
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- Drop default
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- Set not null
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Drop not null
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT check_quantity CHECK (quantity > 0);

-- Drop constraint
ALTER TABLE users DROP CONSTRAINT unique_email;

-- Add foreign key
ALTER TABLE orders
    ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) REFERENCES users(id);

-- Rename table
ALTER TABLE old_name RENAME TO new_name;

-- Change owner
ALTER TABLE table_name OWNER TO new_owner;

-- Set tablespace
ALTER TABLE table_name SET TABLESPACE tablespace_name;

Show Table Information

-- List tables
\dt
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- Describe table
\d table_name
\d+ table_name  -- More details

-- Show table size
SELECT
    pg_size_pretty(pg_total_relation_size('table_name'));

-- All tables with sizes
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Show indexes
\di
SELECT indexname FROM pg_indexes WHERE tablename = 'table_name';

-- Show constraints
SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'table_name'::regclass;

Drop Tables

-- Drop table
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

-- Drop multiple tables
DROP TABLE table1, table2, table3;

-- Drop with cascade
DROP TABLE table_name CASCADE;

-- Truncate table
TRUNCATE TABLE table_name;
TRUNCATE TABLE table_name RESTART IDENTITY;  -- Reset sequences
TRUNCATE TABLE table_name CASCADE;  -- Truncate related tables

Data Manipulation

INSERT

-- Insert single row
INSERT INTO users (username, email)
VALUES ('john', '[email protected]');

-- Insert multiple rows
INSERT INTO users (username, email) VALUES
    ('alice', '[email protected]'),
    ('bob', '[email protected]'),
    ('charlie', '[email protected]');

-- Insert returning
INSERT INTO users (username, email)
VALUES ('dave', '[email protected]')
RETURNING id, created_at;

-- Insert from select
INSERT INTO new_users (username, email)
SELECT username, email FROM users WHERE created_at > '2024-01-01';

-- Insert on conflict (upsert)
INSERT INTO users (id, username, email)
VALUES (1, 'john', '[email protected]')
ON CONFLICT (id) DO UPDATE SET
    username = EXCLUDED.username,
    email = EXCLUDED.email;

-- Insert on conflict do nothing
INSERT INTO users (username, email)
VALUES ('john', '[email protected]')
ON CONFLICT (username) DO NOTHING;

SELECT

-- Basic select
SELECT * FROM users;
SELECT username, email FROM users;

-- Where clause
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE age > 18 AND status = 'active';

-- Like and ilike (case-insensitive)
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE email ILIKE '%@GMAIL.COM';

-- Similar to (regex)
SELECT * FROM users WHERE username SIMILAR TO '(john|jane)%';

-- Regular expressions
SELECT * FROM users WHERE username ~ '^[A-Z]';  -- Case-sensitive
SELECT * FROM users WHERE username ~* '^[a-z]'; -- Case-insensitive

-- IN operator
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE status IN ('active', 'pending');

-- Between
SELECT * FROM orders WHERE price BETWEEN 10 AND 100;

-- Null checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- Order by
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, created_at DESC;
SELECT * FROM users ORDER BY created_at DESC NULLS LAST;

-- Limit and offset
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Distinct
SELECT DISTINCT status FROM orders;
SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC;

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT status) FROM orders;

UPDATE

-- Basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET username = 'newname', email = '[email protected]'
WHERE id = 1;

-- Update with returning
UPDATE users SET status = 'inactive' WHERE id = 1
RETURNING *;

-- Update with calculation
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- Update from another table
UPDATE users u
SET status = 'verified'
FROM profiles p
WHERE u.id = p.user_id AND p.verified = true;

-- Update with current timestamp
UPDATE users SET updated_at = NOW() WHERE id = 1;

DELETE

-- Delete specific rows
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2020-01-01';

-- Delete with returning
DELETE FROM users WHERE id = 1 RETURNING *;

-- Delete with using clause
DELETE FROM order_items
USING orders
WHERE order_items.order_id = orders.id
AND orders.status = 'cancelled';

Joins

Types of Joins

-- Inner join
SELECT u.username, o.id AS order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Left join
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Right join
SELECT u.username, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Full outer join
SELECT u.username, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Cross join
SELECT u.username, p.product_name
FROM users u
CROSS JOIN products p;

-- Self join
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- Lateral join (like apply in SQL Server)
SELECT u.*, recent_orders.*
FROM users u
LEFT JOIN LATERAL (
    SELECT * FROM orders o
    WHERE o.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 5
) recent_orders ON true;

Aggregate Functions

Common Aggregates

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT email) FROM users;

-- Sum
SELECT SUM(price) FROM orders;
SELECT SUM(quantity * price) AS total FROM order_items;

-- Average
SELECT AVG(price) FROM products;
SELECT AVG(age) FROM users WHERE status = 'active';

-- Min/Max
SELECT MIN(price), MAX(price) FROM products;

-- String aggregation
SELECT STRING_AGG(username, ', ' ORDER BY username) FROM users;

-- Array aggregation
SELECT ARRAY_AGG(username ORDER BY username) FROM users;

-- JSON aggregation
SELECT JSON_AGG(row_to_json(users)) FROM users;
SELECT JSON_OBJECT_AGG(id, username) FROM users;

-- Group by
SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT user_id, SUM(price) FROM orders GROUP BY user_id;

-- Having
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- Window functions
SELECT
    username,
    salary,
    AVG(salary) OVER () AS avg_salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- Grouping sets
SELECT category, brand, SUM(sales)
FROM products
GROUP BY GROUPING SETS ((category, brand), (category), ());

User and Role Management

Create and Manage Users

-- Create user
CREATE USER username WITH PASSWORD 'password';
CREATE USER username WITH ENCRYPTED PASSWORD 'password';

-- Create role
CREATE ROLE rolename;
CREATE ROLE rolename WITH LOGIN PASSWORD 'password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
GRANT SELECT, INSERT, UPDATE ON table_name TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;

-- Grant on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO username;

-- Grant role
GRANT rolename TO username;

-- Revoke privileges
REVOKE INSERT, UPDATE ON table_name FROM username;
REVOKE ALL ON DATABASE database_name FROM username;

-- Show grants
\du
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';

-- Alter user
ALTER USER username WITH PASSWORD 'newpassword';
ALTER USER username WITH SUPERUSER;
ALTER USER username WITH NOSUPERUSER;
ALTER USER username RENAME TO newname;

-- Drop user
DROP USER username;
DROP USER IF EXISTS username;

-- List users
\du
SELECT usename FROM pg_user;

-- Show user permissions
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'table_name';

Backup and Restore

pg_dump

# Backup single database
pg_dump database_name > backup.sql
pg_dump -U username database_name > backup.sql

# Backup with custom format (compressed)
pg_dump -Fc database_name > backup.dump

# Backup specific tables
pg_dump -t table_name database_name > table_backup.sql
pg_dump -t 'schema.table*' database_name > tables_backup.sql

# Backup schema only
pg_dump -s database_name > schema.sql

# Backup data only
pg_dump -a database_name > data.sql

# Backup with verbose output
pg_dump -v database_name > backup.sql

# Backup all databases
pg_dumpall > all_backup.sql

# Backup only globals (users, roles)
pg_dumpall --globals-only > globals.sql

# Remote backup
pg_dump -h remote_host -U username database_name > backup.sql

Restore

# Restore SQL dump
psql database_name < backup.sql
psql -U username -d database_name -f backup.sql

# Restore custom format
pg_restore -d database_name backup.dump

# Restore with verbose output
pg_restore -v -d database_name backup.dump

# Restore specific table
pg_restore -t table_name -d database_name backup.dump

# Restore and clean first
pg_restore -c -d database_name backup.dump

# Parallel restore (faster)
pg_restore -j 4 -d database_name backup.dump

# Create database and restore
createdb new_database
pg_restore -d new_database backup.dump

Indexes

Create and Manage Indexes

-- Create index
CREATE INDEX idx_username ON users(username);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Create composite index
CREATE INDEX idx_name_email ON users(username, email);

-- Create partial index
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

-- Create expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- Create GiST index (for full-text search)
CREATE INDEX idx_content ON articles USING GiST(to_tsvector('english', content));

-- Create GIN index (for arrays, JSONB)
CREATE INDEX idx_tags ON posts USING GIN(tags);
CREATE INDEX idx_metadata ON documents USING GIN(metadata);

-- Create BRIN index (for large tables with natural ordering)
CREATE INDEX idx_created ON logs USING BRIN(created_at);

-- Concurrent index (doesn't lock table)
CREATE INDEX CONCURRENTLY idx_username ON users(username);

-- Show indexes
\di
SELECT indexname FROM pg_indexes WHERE tablename = 'users';

-- Drop index
DROP INDEX idx_username;
DROP INDEX CONCURRENTLY idx_username;

-- Reindex
REINDEX INDEX idx_username;
REINDEX TABLE users;
REINDEX DATABASE database_name;

Views

Create and Manage Views

-- Create view
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 'active';

-- Create or replace view
CREATE OR REPLACE VIEW user_orders AS
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Materialized view (cached results)
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) AS order_count, SUM(price) AS total_spent
FROM orders
GROUP BY user_id;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW user_stats;
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

-- Create updatable view
CREATE VIEW recent_users AS
SELECT * FROM users WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
WITH CHECK OPTION;

-- List views
\dv
SELECT viewname FROM pg_views WHERE schemaname = 'public';

-- Show view definition
\d+ view_name

-- Drop view
DROP VIEW active_users;
DROP VIEW IF EXISTS active_users CASCADE;

Functions and Stored Procedures

Functions

-- Create function
CREATE OR REPLACE FUNCTION get_user_name(user_id INTEGER)
RETURNS VARCHAR AS $$
BEGIN
    RETURN (SELECT username FROM users WHERE id = user_id);
END;
$$ LANGUAGE plpgsql;

-- Use function
SELECT get_user_name(1);

-- Function returning table
CREATE OR REPLACE FUNCTION get_user_orders(user_id INTEGER)
RETURNS TABLE(order_id INTEGER, price NUMERIC, status VARCHAR) AS $$
BEGIN
    RETURN QUERY
    SELECT id, price, status FROM orders WHERE user_id = user_id;
END;
$$ LANGUAGE plpgsql;

-- Use table function
SELECT * FROM get_user_orders(1);

-- Function with default parameter
CREATE OR REPLACE FUNCTION get_users(status_filter VARCHAR DEFAULT 'active')
RETURNS SETOF users AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE status = status_filter;
END;
$$ LANGUAGE plpgsql;

-- List functions
\df
SELECT proname FROM pg_proc WHERE pronamespace = 'public'::regnamespace;

-- Drop function
DROP FUNCTION get_user_name(INTEGER);

Stored Procedures

-- Create procedure
CREATE OR REPLACE PROCEDURE update_user_status(
    user_id INTEGER,
    new_status VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE users SET status = new_status WHERE id = user_id;
    COMMIT;
END;
$$;

-- Call procedure
CALL update_user_status(1, 'inactive');

-- Drop procedure
DROP PROCEDURE update_user_status(INTEGER, VARCHAR);

Triggers

Create Triggers

-- Create trigger function
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER update_user_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

-- Create audit trigger
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_audit (user_id, action, old_data, new_data, changed_at)
    VALUES (
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        row_to_json(OLD),
        row_to_json(NEW),
        NOW()
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_changes();

-- List triggers
SELECT tgname FROM pg_trigger WHERE tgrelid = 'users'::regclass;

-- Drop trigger
DROP TRIGGER update_user_timestamp ON users;

Transactions

Transaction Control

-- Start transaction
BEGIN;
START TRANSACTION;

-- Commit
COMMIT;

-- Rollback
ROLLBACK;

-- Savepoint
BEGIN;
INSERT INTO users (username) VALUES ('test1');
SAVEPOINT sp1;
INSERT INTO users (username) VALUES ('test2');
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

-- Transaction isolation levels
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Show isolation level
SHOW transaction_isolation;

-- Advisory locks
SELECT pg_advisory_lock(123);
SELECT pg_advisory_unlock(123);

JSON and JSONB

Working with JSON

-- Create table with JSONB
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

-- Insert JSON
INSERT INTO documents (data)
VALUES ('{"name": "John", "age": 30, "city": "NYC"}');

-- Query JSON fields
SELECT data->>'name' AS name FROM documents;
SELECT data->'age' AS age FROM documents;

-- Query nested JSON
SELECT data->'address'->>'city' FROM documents;

-- JSON array
SELECT data->'tags'->>0 FROM documents;

-- Search in JSONB
SELECT * FROM documents WHERE data @> '{"name": "John"}';
SELECT * FROM documents WHERE data->>'age' = '30';

-- Check if key exists
SELECT * FROM documents WHERE data ? 'email';

-- Update JSONB
UPDATE documents
SET data = jsonb_set(data, '{email}', '"[email protected]"')
WHERE id = 1;

-- Remove key from JSONB
UPDATE documents SET data = data - 'age' WHERE id = 1;

-- JSONB operators
-- @> contains
-- <@ is contained by
-- ? key exists
-- ?| any key exists
-- ?& all keys exist

Full-Text Search

Text Search

-- Create text search column
ALTER TABLE articles ADD COLUMN tsv tsvector;

-- Update text search column
UPDATE articles SET tsv = to_tsvector('english', title || ' ' || content);

-- Create GIN index
CREATE INDEX idx_tsv ON articles USING GIN(tsv);

-- Search
SELECT * FROM articles WHERE tsv @@ to_tsquery('english', 'postgresql & database');

-- Search with ranking
SELECT title, ts_rank(tsv, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & database') query
WHERE tsv @@ query
ORDER BY rank DESC;

-- Highlight results
SELECT ts_headline('english', content, to_tsquery('postgresql'))
FROM articles
WHERE tsv @@ to_tsquery('postgresql');

Performance

Query Analysis

-- Explain query
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- Explain analyze (actually runs query)
EXPLAIN ANALYZE SELECT * FROM users WHERE id > 1000;

-- Explain with options
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders;

-- Vacuum
VACUUM;
VACUUM ANALYZE;
VACUUM FULL;  -- Reclaim space, locks table
VACUUM VERBOSE table_name;

-- Analyze
ANALYZE;
ANALYZE table_name;

-- Show table statistics
SELECT * FROM pg_stat_user_tables WHERE relname = 'users';

-- Show index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

-- Show slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Configuration

-- Show configuration
SHOW ALL;
SHOW max_connections;
SHOW shared_buffers;

-- Set configuration
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET shared_buffers = '256MB';

-- Reload configuration
SELECT pg_reload_conf();

-- Show current settings
SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%buffer%';

Partitioning

Table Partitioning

-- Create partitioned table
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    created_at TIMESTAMP,
    price NUMERIC
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- List partitions
SELECT
    schemaname,
    tablename
FROM pg_tables
WHERE tablename LIKE 'orders_%';

Common PostgreSQL Commands

Database Maintenance

# Vacuum database
vacuumdb -d database_name

# Vacuum all databases
vacuumdb --all

# Analyze database
vacuumdb --analyze -d database_name

# Reindex database
reindexdb -d database_name

# Cluster table
psql -c "CLUSTER table_name"

Import/Export

# Export to CSV
psql -d database_name -c "COPY table_name TO '/path/to/file.csv' CSV HEADER"

# Import from CSV
psql -d database_name -c "COPY table_name FROM '/path/to/file.csv' CSV HEADER"

# Export query to CSV
psql -d database_name -c "COPY (SELECT * FROM table WHERE condition) TO '/path/to/file.csv' CSV HEADER"

Best Practices

Security

-- Use strong passwords
ALTER USER username WITH PASSWORD 'StrongP@ssw0rd!';

-- Principle of least privilege
GRANT SELECT ON table_name TO readonly_user;

-- Use SSL connections
-- Edit postgresql.conf: ssl = on

-- Restrict host access (pg_hba.conf)
-- host    database    user    address    method
-- host    all         all     127.0.0.1/32    scram-sha-256

-- Row-level security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_policy ON users
FOR SELECT
USING (id = current_user_id());

Performance Tips

1. Use appropriate indexes - B-tree, GIN, GiST, BRIN
2. Analyze queries - Use EXPLAIN ANALYZE
3. Vacuum regularly - Prevent bloat
4. Use connection pooling - PgBouncer, pgpool
5. Partition large tables - Improve query performance
6. Use JSONB over JSON - Better performance
7. Tune configuration - shared_buffers, work_mem, etc.
8. Monitor pg_stat_statements - Find slow queries
9. Use materialized views - For expensive queries
10. Proper data types - Use appropriate sizes

Data Types

-- Numeric
SMALLINT        -- 2 bytes (-32768 to 32767)
INTEGER         -- 4 bytes
BIGINT          -- 8 bytes
DECIMAL(p,s)    -- Exact numeric
NUMERIC(p,s)    -- Same as DECIMAL
REAL            -- 4 bytes float
DOUBLE PRECISION -- 8 bytes float

-- Serial (auto-increment)
SMALLSERIAL     -- Auto-incrementing SMALLINT
SERIAL          -- Auto-incrementing INTEGER
BIGSERIAL       -- Auto-incrementing BIGINT

-- String
CHAR(n)         -- Fixed length
VARCHAR(n)      -- Variable length
TEXT            -- Unlimited length

-- Date/Time
DATE            -- Date only
TIME            -- Time only
TIMESTAMP       -- Date and time
TIMESTAMPTZ     -- Timestamp with timezone
INTERVAL        -- Time interval

-- Boolean
BOOLEAN         -- TRUE, FALSE, NULL

-- Binary
BYTEA           -- Binary data

-- JSON
JSON            -- JSON data (stored as text)
JSONB           -- Binary JSON (faster, indexable)

-- Arrays
INTEGER[]       -- Array of integers
TEXT[]          -- Array of text

-- UUID
UUID            -- Universally unique identifier

-- Network
INET            -- IP address
CIDR            -- Network address
MACADDR         -- MAC address

-- Geometric
POINT           -- Point on a plane
LINE            -- Infinite line
LSEG            -- Line segment
BOX             -- Rectangular box
CIRCLE          -- Circle

-- Other
ENUM            -- Enumerated type
XML             -- XML data

Troubleshooting

Common Issues

-- Check active connections
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query
FROM pg_stat_activity;

-- Kill connection
SELECT pg_terminate_backend(pid);

-- Check locks
SELECT * FROM pg_locks WHERE NOT granted;

-- Check blocking queries
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- Check database size
SELECT pg_size_pretty(pg_database_size(current_database()));

-- Check table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Replication status
SELECT * FROM pg_stat_replication;

Quick Reference

# Connection
psql -U postgres
psql -d database_name

# Database
CREATE DATABASE db_name;
\c db_name
DROP DATABASE db_name;

# Table
CREATE TABLE table_name (id SERIAL PRIMARY KEY);
\d table_name
DROP TABLE table_name;

# Data
INSERT INTO table VALUES (1, 'value');
SELECT * FROM table WHERE condition;
UPDATE table SET col = 'value' WHERE condition;
DELETE FROM table WHERE condition;

# User
CREATE USER username WITH PASSWORD 'password';
GRANT ALL ON DATABASE db TO username;

# Backup
pg_dump db_name > backup.sql
pg_restore -d db_name backup.dump