# Connect to database
psql -h localhost -U username -d database_name
psql postgres://username:password@localhost:5432/database_name
# psql meta-commands
\l # List databases
\c dbname # Connect to database
\dt # List tables
\dt+ # List tables with size
\d tablename # Describe table
\di # List indexes
\dv # List views
\df # List functions
\du # List users/roles
\dn # List schemas
\dx # List extensions
\x # Toggle expanded display
\timing # Toggle query timing
\i filename.sql # Execute SQL file
\o filename # Output results to file
\q # Quit psql
-- Create database
CREATE DATABASE myapp;
CREATE DATABASE myapp
WITH OWNER = myuser
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8';
-- Drop database
DROP DATABASE IF EXISTS myapp;
-- Rename database
ALTER DATABASE oldname RENAME TO newname;
-- Change owner
ALTER DATABASE myapp OWNER TO newowner;
-- List databases
SELECT datname FROM pg_database WHERE datistemplate = false;
-- Create user
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';
-- Create superuser
CREATE USER admin WITH SUPERUSER PASSWORD 'adminpass';
-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO myuser;
GRANT USAGE ON SCHEMA myschema TO myuser;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO myuser;
-- Revoke privileges
REVOKE ALL ON DATABASE myapp FROM myuser;
-- Alter user
ALTER USER myuser WITH PASSWORD 'newpassword';
ALTER USER myuser CREATEDB;
-- Drop user
DROP USER IF EXISTS myuser;
-- List users
SELECT usename FROM pg_user;
\du
-- Create schema
CREATE SCHEMA myschema;
CREATE SCHEMA myschema AUTHORIZATION myuser;
-- Set search path
SET search_path TO myschema, public;
ALTER DATABASE myapp SET search_path TO myschema, public;
-- Drop schema
DROP SCHEMA myschema CASCADE;
-- List schemas
SELECT schema_name FROM information_schema.schemata;
-- Numeric types
SMALLINT -- 2 bytes, -32768 to 32767
INTEGER -- 4 bytes, -2147483648 to 2147483647
BIGINT -- 8 bytes, large integers
SERIAL -- Auto-incrementing integer
BIGSERIAL -- Auto-incrementing bigint
DECIMAL(precision, scale) -- Exact numeric
NUMERIC(precision, scale) -- Exact numeric
REAL -- 4 bytes, floating point
DOUBLE PRECISION -- 8 bytes, floating point
-- Text types
CHAR(n) -- Fixed length
VARCHAR(n) -- Variable length with limit
TEXT -- Unlimited length
-- Date/Time types
DATE -- Date only
TIME -- Time only
TIMESTAMP -- Date and time
TIMESTAMPTZ -- Timestamp with timezone
INTERVAL -- Time interval
-- Other types
BOOLEAN -- true/false
UUID -- Universally unique identifier
JSON -- JSON data
JSONB -- Binary JSON (indexed)
ARRAY -- Array of any type
BYTEA -- Binary data
INET -- IP address
CIDR -- Network address
MACADDR -- MAC address
-- Create table with JSONB
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
attributes JSONB
);
-- Insert JSON data
INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "ram": 16, "tags": ["electronics", "computers"]}');
-- Query JSON fields
SELECT attributes->>'brand' FROM products; -- Get as text
SELECT attributes->'ram' FROM products; -- Get as JSON
SELECT attributes#>>'{tags,0}' FROM products; -- Nested path as text
SELECT attributes @> '{"brand": "Dell"}' FROM products; -- Contains
-- JSONB operators
-> -- Get JSON object field by key
->> -- Get JSON object field as text
#> -- Get JSON object at path
#>> -- Get JSON object at path as text
@> -- Contains
<@ -- Contained by
? -- Key exists
?| -- Any key exists
?& -- All keys exist
|| -- Concatenate
-- Update JSONB
UPDATE products
SET attributes = attributes || '{"color": "silver"}'
WHERE id = 1;
UPDATE products
SET attributes = jsonb_set(attributes, '{ram}', '32')
WHERE id = 1;
-- Remove key from JSONB
UPDATE products
SET attributes = attributes - 'color'
WHERE id = 1;
-- JSONB index
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
CREATE INDEX idx_products_brand ON products USING BTREE ((attributes->>'brand'));
-- Create table with array
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title VARCHAR(200),
tags TEXT[]
);
-- Insert array data
INSERT INTO posts (title, tags) VALUES
('PostgreSQL Tips', ARRAY['postgres', 'database', 'sql']),
('Docker Guide', '{"docker", "containers"}');
-- Query arrays
SELECT * FROM posts WHERE 'postgres' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];
SELECT * FROM posts WHERE tags && ARRAY['postgres', 'mysql'];
-- Array functions
array_append(tags, 'new_tag')
array_remove(tags, 'old_tag')
array_cat(array1, array2)
array_length(tags, 1)
unnest(tags) -- Expand array to rows
-- Update array
UPDATE posts SET tags = array_append(tags, 'tutorial') WHERE id = 1;
UPDATE posts SET tags = array_remove(tags, 'sql') WHERE id = 1;
-- Create tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;
-- Update search vector
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);
-- Create GIN index
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);
-- Search queries
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & tutorial');
SELECT * FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgres tutorial');
-- Rank results
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query
ORDER BY rank DESC;
-- Highlight matches
SELECT ts_headline('english', content, to_tsquery('postgres'))
FROM articles;
-- Auto-update trigger
CREATE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_search_vector();
-- B-tree (default, for equality and range)
CREATE INDEX idx_users_email ON users(email);
-- Hash (for equality only)
CREATE INDEX idx_users_email_hash ON users USING HASH(email);
-- GIN (for arrays, JSONB, full-text)
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
-- GiST (for geometric, full-text, range)
CREATE INDEX idx_locations ON places USING GIST(location);
-- BRIN (for large sorted tables)
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);
-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Covering index (INCLUDE)
CREATE INDEX idx_users_email_include ON users(email) INCLUDE (name, created_at);
-- Concurrent index (no table lock)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Reindex
REINDEX INDEX idx_users_email;
REINDEX TABLE users;
-- Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Rollback
BEGIN;
DELETE FROM users WHERE id = 1;
ROLLBACK;
-- Savepoints
BEGIN;
INSERT INTO users (name) VALUES ('John');
SAVEPOINT my_savepoint;
INSERT INTO users (name) VALUES ('Jane');
ROLLBACK TO my_savepoint;
COMMIT;
-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Advisory locks
SELECT pg_advisory_lock(123);
SELECT pg_advisory_unlock(123);
SELECT pg_try_advisory_lock(123);
-- Row-level locking
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;
-- Explain query plan
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM users;
-- Table statistics
ANALYZE users;
-- Vacuum (cleanup dead rows)
VACUUM users;
VACUUM FULL users;
VACUUM ANALYZE users;
-- Table size
SELECT pg_size_pretty(pg_total_relation_size('users'));
SELECT pg_size_pretty(pg_table_size('users'));
SELECT pg_size_pretty(pg_indexes_size('users'));
-- Database size
SELECT pg_size_pretty(pg_database_size('myapp'));
-- Active queries
SELECT pid, query, state, query_start
FROM pg_stat_activity
WHERE state = 'active';
-- Kill query
SELECT pg_cancel_backend(pid); -- Graceful
SELECT pg_terminate_backend(pid); -- Force
-- Table statistics
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_analyze
FROM pg_stat_user_tables;
-- Index usage
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes;
# Dump database
pg_dump -U username -d database_name > backup.sql
pg_dump -U username -d database_name -F c > backup.dump # Custom format
pg_dump -U username -d database_name -F d -j 4 -f backup_dir # Parallel
# Dump specific table
pg_dump -U username -d database_name -t tablename > table_backup.sql
# Dump schema only
pg_dump -U username -d database_name --schema-only > schema.sql
# Dump data only
pg_dump -U username -d database_name --data-only > data.sql
# Restore from SQL
psql -U username -d database_name < backup.sql
# Restore from custom format
pg_restore -U username -d database_name backup.dump
# Restore parallel
pg_restore -U username -d database_name -j 4 backup.dump
# Dump all databases
pg_dumpall -U username > all_databases.sql
-- List available extensions
SELECT * FROM pg_available_extensions;
-- List installed extensions
SELECT * FROM pg_extension;
-- Install extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "hstore";
-- Drop extension
DROP EXTENSION IF EXISTS "uuid-ossp";
-- Common extensions
-- uuid-ossp: UUID generation
SELECT uuid_generate_v4();
-- pgcrypto: Cryptographic functions
SELECT crypt('password', gen_salt('bf'));
SELECT encode(digest('data', 'sha256'), 'hex');
-- pg_trgm: Trigram similarity
SELECT similarity('word', 'wrod');
CREATE INDEX idx_name_trgm ON users USING GIN(name gin_trgm_ops);
SELECT * FROM users WHERE name % 'john'; -- Fuzzy match
-- Generate UUID
SELECT gen_random_uuid(); -- PostgreSQL 13+
SELECT uuid_generate_v4(); -- With uuid-ossp extension
-- Get current timestamp
SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;
-- Date arithmetic
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '1 month';
SELECT DATE_TRUNC('month', NOW());
SELECT EXTRACT(YEAR FROM NOW());
-- String functions
SELECT CONCAT_WS(' ', first_name, last_name);
SELECT LEFT(name, 10);
SELECT RIGHT(name, 5);
SELECT SPLIT_PART('a,b,c', ',', 2); -- Returns 'b'
SELECT STRING_AGG(name, ', ') FROM users;
-- Generate series
SELECT generate_series(1, 10);
SELECT generate_series('2024-01-01'::date, '2024-12-31'::date, '1 month');
-- Random
SELECT random();
SELECT floor(random() * 100 + 1); -- Random 1-100
SELECT * FROM users ORDER BY random() LIMIT 1;
-- Conditional aggregation
SELECT
COUNT(*) FILTER (WHERE status = 'active') as active_count,
COUNT(*) FILTER (WHERE status = 'inactive') as inactive_count
FROM users;
-- DISTINCT ON (first row per group)
SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, created_at DESC;
-- RETURNING clause
INSERT INTO users (name) VALUES ('John') RETURNING id, created_at;
UPDATE users SET name = 'Jane' WHERE id = 1 RETURNING *;
DELETE FROM users WHERE id = 1 RETURNING *;