✔ COPIED!
🗄️ DATABASE MANAGEMENT DDL

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';
click to copy

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;
click to copy

Connect to Database

\c database_name

Connects to a specific database in psql.

-- Example \c myapp \c postgres
click to copy

List Databases

\l

Lists all databases in the PostgreSQL server.

-- Alternative SELECT datname FROM pg_database;
click to copy
📋 TABLE OPERATIONS TABLES

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() );
click to copy

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;
click to copy

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;
click to copy

List Tables

\dt

Lists all tables in the current database.

-- Alternative SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
click to copy
💾 DATA MANIPULATION (DML) 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');
click to copy

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;
click to copy

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;
click to copy

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';
click to copy
🔗 JOINS 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;
click to copy

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;
click to copy

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;
click to copy

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;
click to copy
📊 AGGREGATE FUNCTIONS AGGREGATES

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;
click to copy

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';
click to copy

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;
click to copy

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;
click to copy
📝 STRING FUNCTIONS STRINGS

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;
click to copy

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';
click to copy

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;
click to copy

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;
click to copy
📅 DATE FUNCTIONS DATES

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());
click to copy

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;
click to copy

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;
click to copy

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');
click to copy
⚡ INDEXES 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);
click to copy

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;
click to copy

List Indexes

\di

Lists all indexes in the current database.

-- Alternative query SELECT indexname, tablename FROM pg_indexes WHERE schemaname = 'public';
click to copy

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';
click to copy
👁️ VIEWS 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;
click to copy

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;
click to copy

List Views

\dv

Lists all views in the current database.

-- Alternative query SELECT viewname FROM pg_views WHERE schemaname = 'public';
click to copy

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;
click to copy
🔄 TRANSACTIONS 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;
click to copy

COMMIT

COMMIT;

Commits the current transaction, making changes permanent.

-- Commit transaction BEGIN; INSERT INTO users (name) VALUES ('Alice'); COMMIT; -- Changes are now permanent
click to copy

ROLLBACK

ROLLBACK;

Rolls back the current transaction, undoing all changes.

-- Rollback transaction BEGIN; INSERT INTO users (name) VALUES ('Bob'); ROLLBACK; -- Changes are undone
click to copy

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
click to copy
💾 BACKUP & RESTORE BACKUP

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
click to copy

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
click to copy

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
click to copy

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
click to copy