psql -U username -d database | Connect to database |
psql -h host -p 5432 -U user -d db | Connect with host/port |
psql "postgresql://user:pass@host:5432/db" | Connect with URI |
\q | Quit psql |
\c dbname | Connect to another database |
\password | Change password |
\l | List databases |
\dt | List tables |
\dt+ | List tables with size |
\d tablename | Describe table |
\d+ tablename | Describe table with details |
\dn | List schemas |
\di | List indexes |
\dv | List views |
\df | List functions |
\du | List users/roles |
\x | Toggle expanded display |
\timing | Toggle query timing |
SELECT * FROM table_name; | Select all columns |
SELECT col1, col2 FROM table WHERE condition; | Select with condition |
SELECT DISTINCT column FROM table; | Select unique values |
SELECT * FROM table ORDER BY col ASC/DESC; | Order results |
SELECT * FROM table LIMIT 10 OFFSET 20; | Pagination |
SELECT * FROM table WHERE col LIKE '%pattern%'; | Pattern matching |
SELECT * FROM table WHERE col ILIKE '%pattern%'; | Case-insensitive LIKE |
SELECT * FROM table WHERE col IN (1, 2, 3); | IN clause |
SELECT * FROM table WHERE col BETWEEN 1 AND 10; | BETWEEN range |
SELECT * FROM table WHERE col IS NULL; | NULL check |
SELECT COUNT(*) FROM table; | Count rows |
SELECT SUM(column) FROM table; | Sum values |
SELECT AVG(column) FROM table; | Average value |
SELECT MIN(column), MAX(column) FROM table; | Min and max |
SELECT category, COUNT(*) FROM table GROUP BY category; | Group by |
SELECT category, COUNT(*) FROM table GROUP BY category HAVING COUNT(*) > 5; | Group with filter |
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id; | Inner join |
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id; | Left outer join |
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.t1_id; | Right outer join |
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id; | Full outer join |
SELECT * FROM t1 CROSS JOIN t2; | Cross join (cartesian) |
SELECT * FROM t1 JOIN t2 USING (common_col); | Join using common column |
SELECT * FROM t1 NATURAL JOIN t2; | Natural join |
INSERT INTO table (col1, col2) VALUES ('val1', 'val2'); | Insert single row |
INSERT INTO table (col1, col2) VALUES ('a', 1), ('b', 2); | Insert multiple rows |
INSERT INTO table (col1, col2) VALUES ('val', 'val')
RETURNING *; | Insert and return |
INSERT INTO t1 SELECT * FROM t2 WHERE condition; | Insert from select |
INSERT INTO table (col1) VALUES ('val')
ON CONFLICT (col1) DO NOTHING; | Insert or ignore |
INSERT INTO table (col1, col2) VALUES ('val1', 1)
ON CONFLICT (col1) DO UPDATE SET col2 = EXCLUDED.col2; | Upsert |
UPDATE table SET col1 = 'value' WHERE condition; | Update rows |
UPDATE table SET col1 = 'val1', col2 = 'val2' WHERE id = 1; | Update multiple columns |
UPDATE t1 SET col = t2.val FROM t2 WHERE t1.id = t2.id; | Update from another table |
UPDATE table SET col = col + 1 WHERE id = 1 RETURNING *; | Update and return |
DELETE FROM table WHERE condition; | Delete rows |
DELETE FROM table WHERE id IN (SELECT id FROM t2); | Delete with subquery |
TRUNCATE TABLE table_name; | Delete all rows (fast) |
TRUNCATE TABLE table_name RESTART IDENTITY; | Truncate and reset sequence |
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
); | Create table with columns |
CREATE TABLE IF NOT EXISTS table_name (...); | Create if not exists |
CREATE TABLE child (
id SERIAL PRIMARY KEY,
parent_id INT REFERENCES parent(id) ON DELETE CASCADE
); | Foreign key constraint |
CREATE TABLE t (CHECK (col > 0)); | Check constraint |
ALTER TABLE t ADD COLUMN col VARCHAR(50); | Add column |
ALTER TABLE t DROP COLUMN col; | Drop column |
ALTER TABLE t ALTER COLUMN col TYPE INTEGER; | Change column type |
ALTER TABLE t ALTER COLUMN col SET NOT NULL; | Add NOT NULL |
ALTER TABLE t ALTER COLUMN col SET DEFAULT value; | Set default value |
ALTER TABLE t RENAME COLUMN old TO new; | Rename column |
ALTER TABLE old_name RENAME TO new_name; | Rename table |
DROP TABLE table_name; | Drop table |
DROP TABLE IF EXISTS table_name CASCADE; | Drop with dependencies |
CREATE INDEX idx_name ON table(column); | Create index |
CREATE UNIQUE INDEX idx ON table(col); | Create unique index |
CREATE INDEX idx ON table(col1, col2); | Composite index |
CREATE INDEX idx ON table(col) WHERE condition; | Partial index |
CREATE INDEX CONCURRENTLY idx ON table(col); | Non-blocking index |
DROP INDEX index_name; | Drop index |
REINDEX TABLE table_name; | Rebuild indexes |
SELECT col, ROW_NUMBER() OVER (ORDER BY col) FROM t; | Row number |
SELECT col, RANK() OVER (PARTITION BY cat ORDER BY val DESC) FROM t; | Rank within partition |
SELECT col, LAG(col) OVER (ORDER BY date) FROM t; | Previous row value |
SELECT col, LEAD(col) OVER (ORDER BY date) FROM t; | Next row value |
SELECT col, SUM(val) OVER (ORDER BY date) AS running_total FROM t; | Running total |
SELECT col, AVG(val) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM t; | Moving average |
WITH cte AS (
SELECT * FROM table WHERE condition
)
SELECT * FROM cte; | Common Table Expression |
WITH RECURSIVE tree AS (
SELECT id, parent_id, name FROM t WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.parent_id, t.name FROM t
JOIN tree ON t.parent_id = tree.id
)
SELECT * FROM tree; | Recursive CTE |
SELECT * FROM (SELECT * FROM t WHERE col > 5) AS subq; | Derived table |
SELECT * FROM t WHERE col IN (SELECT col FROM t2); | Subquery in WHERE |
SELECT data->'key' FROM table; | Get JSON field (as JSON) |
SELECT data->>'key' FROM table; | Get JSON field (as text) |
SELECT data#>'{nested,key}' FROM table; | Get nested JSON path |
SELECT * FROM t WHERE data @> '{"key": "val"}'; | JSON contains |
SELECT jsonb_agg(column) FROM table; | Aggregate to JSON array |
SELECT * FROM jsonb_each('{"a":1,"b":2}'); | Expand JSON object |
BEGIN;
UPDATE ... ;
COMMIT; | Basic transaction |
BEGIN;
UPDATE ... ;
ROLLBACK; | Rollback transaction |
BEGIN;
SAVEPOINT sp1;
UPDATE ... ;
ROLLBACK TO sp1;
COMMIT; | Savepoint |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; | Set isolation level |
CREATE USER username WITH PASSWORD 'pass'; | Create user |
CREATE ROLE rolename; | Create role |
GRANT SELECT, INSERT ON table TO user; | Grant privileges |
GRANT ALL PRIVILEGES ON DATABASE db TO user; | Grant all on database |
REVOKE INSERT ON table FROM user; | Revoke privilege |
ALTER USER username WITH PASSWORD 'newpass'; | Change password |
DROP USER username; | Drop user |
pg_dump dbname > backup.sql | Backup database |
pg_dump -Fc dbname > backup.dump | Custom format backup |
pg_dump -t table dbname > table.sql | Backup single table |
pg_dumpall > all_dbs.sql | Backup all databases |
psql dbname < backup.sql | Restore from SQL |
pg_restore -d dbname backup.dump | Restore from dump |
EXPLAIN SELECT * FROM table; | Show query plan |
EXPLAIN ANALYZE SELECT * FROM table; | Execute and show timing |
VACUUM table_name; | Reclaim storage |
VACUUM ANALYZE table_name; | Vacuum and update stats |
ANALYZE table_name; | Update statistics |
SELECT pg_size_pretty(pg_database_size('dbname')); | Database size |
SELECT pg_size_pretty(pg_table_size('table')); | Table size |