psql & Basic Queries (CRUD)

Using the psql CLI

psql is the official Postgres interactive terminal. It ships with every Postgres installation and is the fastest way to explore, debug, and run queries. Mastering a handful of backslash meta-commands will dramatically speed up your workflow.

# Connect to a database
psql -U app_user -d app_db -h localhost -p 5432

# Or using a connection URL
psql "postgresql://app_user:secret@localhost/app_db"

Essential psql Meta-Commands

-- Navigation
l              -- list all databases
c app_db       -- connect (switch) to a database
dt             -- list all tables in current schema
dt schema.*    -- list tables in a specific schema
d  table_name  -- describe table (columns, types, constraints)
di             -- list indexes
dv             -- list views
df             -- list functions

-- History and output
g              -- execute the last query again
e              -- open query in $EDITOR
o file.txt     -- redirect output to a file
	iming on      -- show query execution time

-- Help
?              -- meta-command help
h SELECT       -- SQL syntax help for SELECT
q              -- quit psql

CRUD Operations

The four fundamental database operations are Create, Read, Update, and Delete. Here they are in Postgres SQL:

-- CREATE (INSERT)
-- Single row
INSERT INTO users (name, email)
VALUES ('Linus Torvalds', 'linus@example.com');

-- Multiple rows at once
INSERT INTO users (name, email) VALUES
  ('Ada Lovelace',  'ada@example.com'),
  ('Alan Turing',   'alan@example.com'),
  ('Grace Hopper',  'grace@example.com');

-- INSERT and return the generated ID
INSERT INTO users (name, email)
VALUES ('Margaret Hamilton', 'margaret@example.com')
RETURNING id, created_at;

-- READ (SELECT)
SELECT id, name, email
FROM   users
WHERE  email LIKE '%@example.com'
ORDER  BY created_at DESC
LIMIT  10
OFFSET 0;

-- Use DISTINCT to remove duplicates
SELECT DISTINCT name FROM users;

-- Aggregate: count, sum, avg, min, max
SELECT COUNT(*), MAX(created_at) FROM users;

-- UPDATE
UPDATE users
SET    name = 'Linus B. Torvalds',
       updated_at = NOW()
WHERE  email = 'linus@example.com';

-- UPDATE and return the modified rows
UPDATE users
SET name = 'Ada B. Lovelace'
WHERE id = 1
RETURNING *;

-- DELETE
DELETE FROM users
WHERE email = 'linus@example.com';

-- DELETE with RETURNING (confirm what was removed)
DELETE FROM users
WHERE created_at < NOW() - INTERVAL '1 year'
RETURNING id, email;

Filtering and Sorting Reference

-- Comparison operators
WHERE age >= 18
WHERE status IN ('active', 'pending')
WHERE deleted_at IS NULL
WHERE name ILIKE '%smith%'   -- case-insensitive LIKE

-- Combining conditions
WHERE status = 'active' AND role = 'admin'
WHERE city = 'NYC' OR city = 'LA'

-- Range
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

-- Sorting (can sort on multiple columns)
ORDER BY last_name ASC, first_name ASC

-- Pagination
LIMIT 20 OFFSET 40   -- page 3, 20 rows per page