PostgreSQL Quick Commands Cheat Sheet

Connect

psql -U username -d database_name

List Databases

\l

Create Database

CREATE DATABASE mydb;

Connect to a Database

\c mydb

---

Schema Commands

List Schemas

\dn

Current Schema

SELECT current_schema();

Show Search Path

SHOW search_path;

Create Schema

CREATE SCHEMA sales;

Create Schema (If Not Exists)

CREATE SCHEMA IF NOT EXISTS sales;

Rename Schema

ALTER SCHEMA sales RENAME TO analytics;

Change Current Search Path

SET search_path TO sales;

Set Multiple Schemas in Search Path

SET search_path TO sales, public;

Create Table in a Specific Schema

CREATE TABLE sales.orders (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(10,2)
);

Query a Table from a Schema

SELECT *
FROM sales.orders;

Move Table to Another Schema

ALTER TABLE public.orders
SET SCHEMA sales;

Drop Schema

DROP SCHEMA sales;

Drop Schema with All Objects

DROP SCHEMA sales CASCADE;

Drop table in a namespace

DROP TABLE IF EXISTS namespace.app_name_table_name CASCADE;

---

Table Commands

List Tables

\dt

List Tables in All Schemas

\dt *.*

List Tables in a Specific Schema

\dt sales.*

Describe a Table

\d users

Describe a Schema Table

\d sales.orders

Create Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert Data

INSERT INTO users (name, email)
VALUES ('Alice', '[email protected]');

Select Data

SELECT * FROM users;

Filter Rows

SELECT *
FROM users
WHERE name = 'Alice';

Sort Results

SELECT *
FROM users
ORDER BY created_at DESC;

Limit Results

SELECT *
FROM users
LIMIT 10;

Update Data

UPDATE users
SET email = '[email protected]'
WHERE id = 1;

Delete Data

DELETE FROM users
WHERE id = 1;

Add a Column

ALTER TABLE users
ADD COLUMN age INT;

Rename a Column

ALTER TABLE users
RENAME COLUMN age TO user_age;

Drop a Column

ALTER TABLE users
DROP COLUMN user_age;

Rename a Table

ALTER TABLE users
RENAME TO app_users;

Drop a Table

DROP TABLE users;

Drop a Database

DROP DATABASE mydb;

---

Query Commands

Count Rows

SELECT COUNT(*)
FROM users;

Group By

SELECT country, COUNT(*)
FROM users
GROUP BY country;

Join Tables

SELECT u.name, o.order_date
FROM users u
JOIN orders o
ON u.id = o.user_id;

---

Index Commands

Create Index

CREATE INDEX idx_users_email
ON users(email);

Create Unique Index

CREATE UNIQUE INDEX idx_email
ON users(email);

List Indexes

\di

Drop Index

DROP INDEX idx_users_email;

---

Transaction Commands

Start Transaction

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;

---

Helpful psql Meta Commands

Command	Description
\l	List databases
\c dbname	Connect to database
\dn	List schemas
\dt	List tables
\dt *.*	List tables in all schemas
\dt schema.*	List tables in a schema
\d table	Describe table
\d schema.table	Describe schema table
\di	List indexes
\dv	List views
\df	List functions
\du	List roles/users
\?	psql help
\h	SQL command help
\q	Exit psql

---

Common Data Types

Type	Description
SERIAL	Auto-increment integer
INT	Integer
BIGINT	Large integer
VARCHAR(n)	Variable-length string
TEXT	Unlimited text
BOOLEAN	TRUE/FALSE
DATE	Date
TIME	Time
TIMESTAMP	Date & Time
NUMERIC(p,s)	Exact decimal
UUID	Universally unique identifier
JSONB	Binary JSON

---

Tip: SQL statements end with a semicolon (;). Commands beginning with \ are psql meta-commands and do not require a semicolon.