PostgreSQL Guide
Complete PostgreSQL reference with commands, functions, and practical examples.
🗄️ Database Management
Create Database
CREATE DATABASE database_name;
Creates a new database with the specified name.
-- Example
CREATE DATABASE myapp;
CREATE DATABASE test_db WITH ENCODING 'UTF8';
Drop Database
DROP DATABASE database_name;
Permanently deletes a database and all its data.
-- Example
DROP DATABASE old_db;
DROP DATABASE IF EXISTS temp_db;
Connect to Database
\c database_name
Connects to a specific database in psql.
-- Example
\c myapp
\c postgres
List Databases
\l
Lists all databases in the PostgreSQL server.
-- Alternative
SELECT datname FROM pg_database;
📋 Table Operations
Create Table
CREATE TABLE table_name (column_definitions);
Creates a new table with specified columns and constraints.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);
Drop Table
DROP TABLE table_name;
Permanently deletes a table and all its data.
-- Example
DROP TABLE old_table;
DROP TABLE IF EXISTS temp_table;
Alter Table
ALTER TABLE table_name action;
Modifies the structure of an existing table.
-- Add column
ALTER TABLE users ADD COLUMN age INTEGER;
-- Drop column
ALTER TABLE users DROP COLUMN age;
-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;
List Tables
\dt
Lists all tables in the current database.
-- Alternative
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public';
💾 Data Manipulation (DML)
INSERT
INSERT INTO table_name VALUES (values);
Inserts new rows into a table.
-- Single row
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- Multiple rows
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');
SELECT
SELECT columns FROM table_name WHERE condition;
Retrieves data from one or more tables.
-- All columns
SELECT * FROM users;
-- Specific columns
SELECT name, email FROM users WHERE age > 18;
-- With ordering
SELECT * FROM users ORDER BY created_at DESC;
UPDATE
UPDATE table_name SET column = value WHERE condition;
Modifies existing rows in a table.
-- Update single row
UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- Update multiple columns
UPDATE users SET name = 'John Doe', age = 30 WHERE id = 1;
DELETE
DELETE FROM table_name WHERE condition;
Removes rows from a table.
-- Delete specific row
DELETE FROM users WHERE id = 1;
-- Delete with condition
DELETE FROM users WHERE created_at < '2023-01-01';
🔗 Joins
INNER JOIN
SELECT * FROM table1 INNER JOIN table2 ON condition;
Returns only matching rows from both tables.
SELECT u.name, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;
LEFT JOIN
SELECT * FROM table1 LEFT JOIN table2 ON condition;
Returns all rows from left table and matching rows from right table.
SELECT u.name, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;
RIGHT JOIN
SELECT * FROM table1 RIGHT JOIN table2 ON condition;
Returns all rows from right table and matching rows from left table.
SELECT u.name, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;
FULL OUTER JOIN
SELECT * FROM table1 FULL OUTER JOIN table2 ON condition;
Returns all rows from both tables, with NULLs for non-matching rows.
SELECT u.name, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;
📊 Aggregate Functions
COUNT
SELECT COUNT(*) FROM table_name;
Counts the number of rows or non-NULL values.
-- Count all rows
SELECT COUNT(*) FROM users;
-- Count non-NULL values
SELECT COUNT(email) FROM users;
-- Count with condition
SELECT COUNT(*) FROM users WHERE age > 18;
SUM
SELECT SUM(column) FROM table_name;
Calculates the sum of numeric values in a column.
-- Sum all values
SELECT SUM(price) FROM products;
-- Sum with condition
SELECT SUM(amount) FROM orders WHERE status = 'completed';
AVG
SELECT AVG(column) FROM table_name;
Calculates the average of numeric values in a column.
-- Average value
SELECT AVG(age) FROM users;
-- Average with rounding
SELECT ROUND(AVG(price), 2) FROM products;
MIN/MAX
SELECT MIN(column), MAX(column) FROM table_name;
Finds the minimum and maximum values in a column.
-- Min and max values
SELECT MIN(price), MAX(price) FROM products;
-- Min and max with condition
SELECT MIN(created_at), MAX(created_at) FROM users;
📝 String Functions
CONCAT
SELECT CONCAT(string1, string2, ...);
Concatenates multiple strings together.
-- Concatenate strings
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- With separator
SELECT CONCAT_WS(' - ', title, author) FROM books;
UPPER/LOWER
SELECT UPPER(column), LOWER(column) FROM table_name;
Converts strings to uppercase or lowercase.
-- Convert case
SELECT UPPER(name) FROM users;
SELECT LOWER(email) FROM users;
-- Case-insensitive search
SELECT * FROM users WHERE LOWER(name) = 'john';
SUBSTRING
SELECT SUBSTRING(string FROM start FOR length);
Extracts a substring from a string.
-- Extract substring
SELECT SUBSTRING(email FROM 1 FOR 5) FROM users;
-- Extract domain
SELECT SUBSTRING(email FROM '@' FOR 50) FROM users;
LENGTH
SELECT LENGTH(column) FROM table_name;
Returns the length of a string in characters.
-- String length
SELECT name, LENGTH(name) AS name_length FROM users;
-- Filter by length
SELECT * FROM users WHERE LENGTH(name) > 10;
📅 Date Functions
NOW()
SELECT NOW();
Returns the current date and time.
-- Current timestamp
SELECT NOW();
-- Insert with current time
INSERT INTO logs (message, created_at) VALUES ('User login', NOW());
DATE_TRUNC
SELECT DATE_TRUNC('unit', timestamp);
Truncates a timestamp to specified precision.
-- Truncate to day
SELECT DATE_TRUNC('day', created_at) FROM users;
-- Truncate to month
SELECT DATE_TRUNC('month', created_at) FROM users;
EXTRACT
SELECT EXTRACT(field FROM timestamp);
Extracts a specific field from a timestamp.
-- Extract year
SELECT EXTRACT(YEAR FROM created_at) FROM users;
-- Extract month
SELECT EXTRACT(MONTH FROM created_at) FROM users;
AGE
SELECT AGE(timestamp1, timestamp2);
Calculates the age between two timestamps.
-- Age from now
SELECT AGE(NOW(), created_at) FROM users;
-- Age between dates
SELECT AGE('2023-12-31', '2023-01-01');
⚡ Indexes
CREATE INDEX
CREATE INDEX index_name ON table_name (column);
Creates an index to improve query performance.
-- Single column index
CREATE INDEX idx_user_email ON users (email);
-- Composite index
CREATE INDEX idx_user_name_age ON users (name, age);
-- Unique index
CREATE UNIQUE INDEX idx_user_email_unique ON users (email);
DROP INDEX
DROP INDEX index_name;
Removes an index from the database.
-- Drop index
DROP INDEX idx_user_email;
-- Drop if exists
DROP INDEX IF EXISTS idx_old_index;
List Indexes
\di
Lists all indexes in the current database.
-- Alternative query
SELECT indexname, tablename FROM pg_indexes
WHERE schemaname = 'public';
Partial Index
CREATE INDEX ... WHERE condition;
Creates an index on a subset of rows.
-- Index only active users
CREATE INDEX idx_active_users ON users (email)
WHERE status = 'active';
-- Index only recent orders
CREATE INDEX idx_recent_orders ON orders (created_at)
WHERE created_at > '2023-01-01';
👁️ Views
CREATE VIEW
CREATE VIEW view_name AS SELECT ...;
Creates a virtual table based on a query result.
-- Simple view
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';
-- Complex view with joins
CREATE VIEW user_posts AS
SELECT u.name, p.title, p.created_at
FROM users u JOIN posts p ON u.id = p.user_id;
DROP VIEW
DROP VIEW view_name;
Removes a view from the database.
-- Drop view
DROP VIEW active_users;
-- Drop if exists
DROP VIEW IF EXISTS old_view;
List Views
\dv
Lists all views in the current database.
-- Alternative query
SELECT viewname FROM pg_views
WHERE schemaname = 'public';
Materialized View
CREATE MATERIALIZED VIEW ... AS SELECT ...;
Creates a view that stores the query result physically.
-- Create materialized view
CREATE MATERIALIZED VIEW user_stats AS
SELECT COUNT(*) as total_users, AVG(age) as avg_age
FROM users;
-- Refresh materialized view
REFRESH MATERIALIZED VIEW user_stats;
🔄 Transactions
BEGIN
BEGIN;
Starts a new transaction block.
-- Start transaction
BEGIN;
-- Multiple operations
INSERT INTO users (name) VALUES ('John');
UPDATE users SET age = 25 WHERE name = 'John';
COMMIT;
COMMIT
COMMIT;
Commits the current transaction, making changes permanent.
-- Commit transaction
BEGIN;
INSERT INTO users (name) VALUES ('Alice');
COMMIT; -- Changes are now permanent
ROLLBACK
ROLLBACK;
Rolls back the current transaction, undoing all changes.
-- Rollback transaction
BEGIN;
INSERT INTO users (name) VALUES ('Bob');
ROLLBACK; -- Changes are undone
SAVEPOINT
SAVEPOINT name;
Creates a savepoint within a transaction.
-- Create savepoint
BEGIN;
INSERT INTO users (name) VALUES ('Charlie');
SAVEPOINT sp1;
INSERT INTO users (name) VALUES ('David');
ROLLBACK TO sp1; -- Rollback to savepoint
💾 Backup & Restore
pg_dump
pg_dump database_name > backup.sql
Creates a backup of a database to a SQL file.
# Backup entire database
pg_dump myapp > backup.sql
# Backup with custom format
pg_dump -Fc myapp > backup.dump
# Backup specific schema
pg_dump -n public myapp > schema.sql
pg_restore
pg_restore -d database_name backup.dump
Restores a database from a backup file.
# Restore from custom format
pg_restore -d myapp backup.dump
# Restore with clean
pg_restore -c -d myapp backup.dump
# Restore specific tables
pg_restore -t users -d myapp backup.dump
psql restore
psql -d database_name -f backup.sql
Restores a database from a SQL file.
# Restore from SQL file
psql -d myapp -f backup.sql
# Restore with verbose output
psql -d myapp -f backup.sql -v ON_ERROR_STOP=1
pg_dumpall
pg_dumpall > all_databases.sql
Backs up all databases in a PostgreSQL cluster.
# Backup all databases
pg_dumpall > all_databases.sql
# Backup only globals
pg_dumpall -g > globals.sql
# Backup with roles
pg_dumpall -r > roles.sql