Last Updated: November 21, 2025
Database Commands
\l
List databases
\c dbname
Connect to database
\dt
List tables
\d tablename
Describe table
\du
List users
\q
Quit psql
Data Types
| Type | Description |
|---|---|
INTEGER, BIGINT
|
Whole numbers |
NUMERIC, DECIMAL
|
Exact decimal numbers |
VARCHAR(n)
|
Variable-length string |
TEXT
|
Unlimited length string |
BOOLEAN
|
True/false |
DATE, TIME, TIMESTAMP
|
Date and time |
JSON, JSONB
|
JSON data |
ARRAY
|
Arrays of any type |
Table Operations
-- Create table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Add column
ALTER TABLE users ADD COLUMN age INTEGER;
-- Drop column
ALTER TABLE users DROP COLUMN age;
-- Add constraint
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);
-- Create index
CREATE INDEX idx_email ON users(email);Advanced Queries
-- Window functions
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;
-- Common Table Expressions (CTE)
WITH high_earners AS (
SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners WHERE department = 'Engineering';
-- JSON queries
SELECT data->>'name' as name
FROM users
WHERE data @> '{"active": true}';
-- Array operations
SELECT * FROM posts WHERE tags && ARRAY['tech', 'news'];
💡 Pro Tip:
Use EXPLAIN ANALYZE to understand query performance!