PostgreSQL Cheat Sheet
# PostgreSQL Cheat Sheet
## CRUD
```sql
SELECT * FROM users WHERE id = 1;
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
UPDATE users SET name = 'Bob' WHERE id = 1;
DELETE FROM users WHERE id = 1;
```
## Joins
```sql
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN
```
## Aggregation
```sql
SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
```
## Window Functions
```sql
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as rank,
AVG(salary) OVER (PARTITION BY dept) as avg_dept
FROM employees;
```
## Indexes
```sql
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_ordersε€ε ON orders(user_id, created_at);
```
## Data Types
- SERIAL / BIGSERIAL β auto-increment ID
- VARCHAR(n) / TEXT β strings
- INTEGER / BIGINT / NUMERIC β numbers
- BOOLEAN β true/false
- TIMESTAMP / TIMESTAMPTZ β dates
- JSON / JSONB β JSON storage
- UUID β UUID type
- ARRAY β array column
## psql Commands
```
\dt List tables
\d tablename Describe table
\di List indexes
\c dbname Connect to database
\q Quit
```