# PostgreSQL Cheat Sheet for MySQL Developers

> A comprehensive PostgreSQL reference for developers coming from MySQL.

# Table of Contents

- Connecting
- Databases
- Users & Roles
- Permissions
- Tables
- Columns
- Constraints
- Indexes
- CRUD
- Joins
- Transactions
- Views
- Functions
- Extensions
- Backup & Restore
- Monitoring
- Docker Commands
- psql Meta Commands
- PostgreSQL vs MySQL Command Mapping
---

# 1. Connecting

## Connect locally

```bash
psql -U admin
```

Connect to a specific database

```bash
psql -U admin -d mydb
```

Connect using host

```bash
psql -h localhost -p 5432 -U admin -d mydb
```

Connection URI

```text
postgresql://admin:password@localhost:5432/mydb
```

---

# 2. Databases

## List all databases

Inside psql

```sql
\l
```

or

```sql
\list
```

Using SQL

```sql
SELECT datname FROM pg_database;
```

---

## Create database

```sql
CREATE DATABASE mydb;
```

With owner

```sql
CREATE DATABASE mydb OWNER app_user;
```

With encoding

```sql
CREATE DATABASE mydb
ENCODING 'UTF8';
```

---

## Rename database

```sql
ALTER DATABASE mydb RENAME TO production_db;
```

---

## Delete database

```sql
DROP DATABASE mydb;
```

Force delete (Postgres 13+)

```sql
DROP DATABASE mydb WITH (FORCE);
```

---

## Switch database

```sql
\c mydb
```

---

## Current database

```sql
SELECT current_database();
```

---

# 3. Users (Roles)

PostgreSQL doesn't really have "users".

Everything is a ROLE.

A role may or may not have login permission.

---

Create user

```sql
CREATE USER app_user
WITH PASSWORD 'secret';
```

Equivalent

```sql
CREATE ROLE app_user
LOGIN
PASSWORD 'secret';
```

---

Create superuser

```sql
CREATE ROLE admin
LOGIN
SUPERUSER
PASSWORD 'password';
```

---

List users

```sql
\du
```

or

```sql
SELECT rolname
FROM pg_roles;
```

---

Delete user

```sql
DROP ROLE app_user;
```

---

Change password

```sql
ALTER USER app_user
PASSWORD 'newpassword';
```

---

# 4. Permissions

Grant database access

```sql
GRANT ALL PRIVILEGES
ON DATABASE mydb
TO app_user;
```

Grant connect only

```sql
GRANT CONNECT
ON DATABASE mydb
TO app_user;
```

Grant schema access

```sql
GRANT USAGE
ON SCHEMA public
TO app_user;
```

Grant all tables

```sql
GRANT ALL PRIVILEGES
ON ALL TABLES IN SCHEMA public
TO app_user;
```

Grant sequences

```sql
GRANT ALL PRIVILEGES
ON ALL SEQUENCES IN SCHEMA public
TO app_user;
```

Revoke

```sql
REVOKE ALL
ON DATABASE mydb
FROM app_user;
```

---

# 5. Schemas

List schemas

```sql
\dn
```

Create schema

```sql
CREATE SCHEMA analytics;
```

Drop schema

```sql
DROP SCHEMA analytics;
```

Drop with everything

```sql
DROP SCHEMA analytics CASCADE;
```

---

# 6. Tables

List tables

```sql
\dt
```

List all tables

```sql
\dt *.*
```

Describe table

```sql
\d users
```

Create table

```sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);
```

Rename table

```sql
ALTER TABLE users
RENAME TO customers;
```

Delete table

```sql
DROP TABLE users;
```

Delete if exists

```sql
DROP TABLE IF EXISTS users;
```

---

# 7. Columns

Add column

```sql
ALTER TABLE users
ADD COLUMN age INT;
```

Rename column

```sql
ALTER TABLE users
RENAME COLUMN age TO user_age;
```

Change type

```sql
ALTER TABLE users
ALTER COLUMN age
TYPE BIGINT;
```

Drop column

```sql
ALTER TABLE users
DROP COLUMN age;
```

---

# 8. Constraints

Primary key

```sql
PRIMARY KEY
```

Foreign key

```sql
FOREIGN KEY(user_id)
REFERENCES users(id)
```

Unique

```sql
UNIQUE(email)
```

Check

```sql
CHECK(age >= 18)
```

Not null

```sql
NOT NULL
```

---

# 9. Indexes

Create

```sql
CREATE INDEX idx_users_email
ON users(email);
```

Unique

```sql
CREATE UNIQUE INDEX idx_email
ON users(email);
```

Drop

```sql
DROP INDEX idx_email;
```

Show indexes

```sql
\di
```

---

# 10. CRUD

Insert

```sql
INSERT INTO users(name,email)
VALUES('John','john@test.com');
```

Insert multiple

```sql
INSERT INTO users(name)
VALUES
('John'),
('Alice'),
('Bob');
```

Select

```sql
SELECT *
FROM users;
```

Where

```sql
SELECT *
FROM users
WHERE age > 20;
```

Update

```sql
UPDATE users
SET age = 25
WHERE id = 1;
```

Delete

```sql
DELETE
FROM users
WHERE id = 1;
```

Count

```sql
SELECT COUNT(*)
FROM users;
```

---

# 11. Transactions

Begin

```sql
BEGIN;
```

Commit

```sql
COMMIT;
```

Rollback

```sql
ROLLBACK;
```

Example

```sql
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;
```

---

# 12. Views

Create

```sql
CREATE VIEW active_users AS
SELECT *
FROM users
WHERE active = true;
```

Delete

```sql
DROP VIEW active_users;
```

---

# 13. Extensions

Show extensions

```sql
\dx
```

Install uuid

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
```

Install pgcrypto

```sql
CREATE EXTENSION pgcrypto;
```

---

# 14. Backup

Entire database

```bash
pg_dump mydb > backup.sql
```

Compressed

```bash
pg_dump -Fc mydb > backup.dump
```

All databases

```bash
pg_dumpall > all.sql
```

Restore SQL

```bash
psql mydb < backup.sql
```

Restore dump

```bash
pg_restore -d mydb backup.dump
```

---

# 15. Monitoring

Current connections

```sql
SELECT *
FROM pg_stat_activity;
```

Current database size

```sql
SELECT pg_size_pretty(
pg_database_size(current_database())
);
```

List database sizes

```sql
SELECT
datname,
pg_size_pretty(pg_database_size(datname))
FROM pg_database;
```

Server version

```sql
SELECT version();
```

Current user

```sql
SELECT current_user;
```

Current time

```sql
SELECT NOW();
```

---

# 16. Docker

Open shell

```bash
docker exec -it postgres bash
```

Open psql

```bash
docker exec -it postgres psql -U admin
```

Connect directly

```bash
docker exec -it postgres psql -U admin -d mydb
```

Backup

```bash
docker exec postgres \
pg_dump -U admin mydb > backup.sql
```

Restore

```bash
docker exec -i postgres \
psql -U admin mydb < backup.sql
```

---

# 17. psql Meta Commands

These only work inside psql.

List databases

```sql
\l
```

Switch database

```sql
\c mydb
```

List tables

```sql
\dt
```

List views

```sql
\dv
```

List schemas

```sql
\dn
```

List indexes

```sql
\di
```

Describe table

```sql
\d users
```

Describe everything

```sql
\d
```

List users

```sql
\du
```

Show extensions

```sql
\dx
```

History

```sql
\s
```

Timing

```sql
\timing
```

Help

```sql
\?
```

SQL help

```sql
\h
```

Quit

```sql
\q
```

---

# 18. PostgreSQL vs MySQL Mapping

| MySQL | PostgreSQL |
|---------|------------|
| SHOW DATABASES; | `\l` or `SELECT datname FROM pg_database;` |
| USE db; | `\c db` |
| SHOW TABLES; | `\dt` |
| DESCRIBE table; | `\d table` |
| SHOW CREATE TABLE | `\d+ table` |
| SHOW PROCESSLIST; | `SELECT * FROM pg_stat_activity;` |
| AUTO_INCREMENT | `SERIAL`, `BIGSERIAL`, or `GENERATED AS IDENTITY` |
| NOW() | NOW() |
| IFNULL() | COALESCE() |
| LIMIT | LIMIT |
| ENGINE=InnoDB | Not needed |
| UNSIGNED | Not supported |
| ENUM | Native ENUM type |
| BOOLEAN | Real BOOLEAN type |
| TEXT | TEXT (no length limit like MySQL's VARCHAR) |

---

# 19. Best Practices

✔ One PostgreSQL server

✔ One database per application

✔ One role per application

✔ Never use the admin user in production

✔ Backup regularly

✔ Use UUIDs or IDENTITY instead of SERIAL for new projects when appropriate

✔ Use indexes on frequently queried columns

✔ Use transactions for multi-step operations

✔ Keep development, staging, and production databases separate

✔ Monitor `pg_stat_activity` and database size as your applications grow