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

psql -U admin

Connect to a specific database

psql -U admin -d mydb

Connect using host

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

Connection URI

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

---

2. Databases

List all databases

Inside psql

\l

or

\list

Using SQL

SELECT datname FROM pg_database;

---

Create database

CREATE DATABASE mydb;

With owner

CREATE DATABASE mydb OWNER app_user;

With encoding

CREATE DATABASE mydb
ENCODING 'UTF8';

---

Rename database

ALTER DATABASE mydb RENAME TO production_db;

---

Delete database

DROP DATABASE mydb;

Force delete (Postgres 13+)

DROP DATABASE mydb WITH (FORCE);

---

Switch database

\c mydb

---

Current database

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

CREATE USER app_user
WITH PASSWORD 'secret';

Equivalent

CREATE ROLE app_user
LOGIN
PASSWORD 'secret';

---

Create superuser

CREATE ROLE admin
LOGIN
SUPERUSER
PASSWORD 'password';

---

List users

\du

or

SELECT rolname
FROM pg_roles;

---

Delete user

DROP ROLE app_user;

---

Change password

ALTER USER app_user
PASSWORD 'newpassword';

---

4. Permissions

Grant database access

GRANT ALL PRIVILEGES
ON DATABASE mydb
TO app_user;

Grant connect only

GRANT CONNECT
ON DATABASE mydb
TO app_user;

Grant schema access

GRANT USAGE
ON SCHEMA public
TO app_user;

Grant all tables

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

Grant sequences

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

Revoke

REVOKE ALL
ON DATABASE mydb
FROM app_user;

---

5. Schemas

List schemas

\dn

Create schema

CREATE SCHEMA analytics;

Drop schema

DROP SCHEMA analytics;

Drop with everything

DROP SCHEMA analytics CASCADE;

---

6. Tables

List tables

\dt

List all tables

\dt *.*

Describe table

\d users

Create table

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

Rename table

ALTER TABLE users
RENAME TO customers;

Delete table

DROP TABLE users;

Delete if exists

DROP TABLE IF EXISTS users;

---

7. Columns

Add column

ALTER TABLE users
ADD COLUMN age INT;

Rename column

ALTER TABLE users
RENAME COLUMN age TO user_age;

Change type

ALTER TABLE users
ALTER COLUMN age
TYPE BIGINT;

Drop column

ALTER TABLE users
DROP COLUMN age;

---

8. Constraints

Primary key

PRIMARY KEY

Foreign key

FOREIGN KEY(user_id)
REFERENCES users(id)

Unique

UNIQUE(email)

Check

CHECK(age >= 18)

Not null

NOT NULL

---

9. Indexes

Create

CREATE INDEX idx_users_email
ON users(email);

Unique

CREATE UNIQUE INDEX idx_email
ON users(email);

Drop

DROP INDEX idx_email;

Show indexes

\di

---

10. CRUD

Insert

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

Insert multiple

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

Select

SELECT *
FROM users;

Where

SELECT *
FROM users
WHERE age > 20;

Update

UPDATE users
SET age = 25
WHERE id = 1;

Delete

DELETE
FROM users
WHERE id = 1;

Count

SELECT COUNT(*)
FROM users;

---

11. Transactions

Begin

BEGIN;

Commit

COMMIT;

Rollback

ROLLBACK;

Example

BEGIN;

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

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

COMMIT;

---

12. Views

Create

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

Delete

DROP VIEW active_users;

---

13. Extensions

Show extensions

\dx

Install uuid

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Install pgcrypto

CREATE EXTENSION pgcrypto;

---

14. Backup

Entire database

pg_dump mydb > backup.sql

Compressed

pg_dump -Fc mydb > backup.dump

All databases

pg_dumpall > all.sql

Restore SQL

psql mydb < backup.sql

Restore dump

pg_restore -d mydb backup.dump

---

15. Monitoring

Current connections

SELECT *
FROM pg_stat_activity;

Current database size

SELECT pg_size_pretty(
pg_database_size(current_database())
);

List database sizes

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

Server version

SELECT version();

Current user

SELECT current_user;

Current time

SELECT NOW();

---

16. Docker

Open shell

docker exec -it postgres bash

Open psql

docker exec -it postgres psql -U admin

Connect directly

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

Backup

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

Restore

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

---

17. psql Meta Commands

These only work inside psql.

List databases

\l

Switch database

\c mydb

List tables

\dt

List views

\dv

List schemas

\dn

List indexes

\di

Describe table

\d users

Describe everything

\d

List users

\du

Show extensions

\dx

History

\s

Timing

\timing

Help

\?

SQL help

\h

Quit

\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