Skip to main content

PostgreSQL Cheatsheet

  • command needed for daily use

15 august 2025

1. Installation & Setup​

On Ubuntu/Debian​

sudo apt update
sudo apt install postgresql postgresql-contrib

2.Start and Enable PostgreSQL Service​

sudo systemctl enable postgresql  # enable first time 
sudo systemctl start postgresql   #start/stop
sudo systemctl status postgresql  # check running status

3.Switching to PostgreSQL User​

  • PostgreSQL runs under a postgres system user:
sudo -i -u postgres
  • Enter the PostgreSQL CLI:
psql
  • You should see:
postgres=#

-Exit psql:

\q

4. basic command​

In linux shell​

psql -h host -U username -d dbname
  • -h host → the server hostname or IP (use localhost if PostgreSQL is on your machine)
  • -U username → PostgreSQL username
  • -d dbname → database name you want to connect to

In psql shell​

\l            -- list databases
\c dbname     -- connect to a database
\dt           -- list tables
\d tablename  -- describe table structure
\du           -- list users/roles
\q            -- quit psql

5.Database & Table Management​

  • Create database
CREATE DATABASE mydb;
  • Drop database
DROP DATABASE mydb;
  • Create table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  • Drop table
DROP TABLE users;
  • Add column
ALTER TABLE users ADD COLUMN age INT;
  • Remove column
ALTER TABLE users DROP COLUMN age;
  • Rename table
ALTER TABLE users RENAME TO app_users;

6. Data Manipulation​

  • Insert data
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
  • Update data
UPDATE users SET name = 'Bob' WHERE id = 1;
  • Delete data
DELETE FROM users WHERE id = 1;
  • Select data
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 2;

7. Query Filters & Operators​

  • WHERE clause
SELECT * FROM users WHERE age > 25;
  • LIKE
SELECT * FROM users WHERE name LIKE 'A%';
  • IN
SELECT * FROM users WHERE id IN (1,2,3);
  • ORDER BY
SELECT * FROM users ORDER BY created_at DESC;
  • LIMIT
SELECT * FROM users LIMIT 5;

8. Data Manipulation​

  • Insert data
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
  • Update data
UPDATE users SET name = 'Bob' WHERE id = 1;
  • Delete data
DELETE FROM users WHERE id = 1;
  • Select data
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 2;

9. Aggregation & Grouping​

  • Count rows
SELECT COUNT(*) FROM users;
  • Sum, Avg, Min, Max
SELECT AVG(age) FROM users;
SELECT SUM(age) FROM users;
SELECT MIN(age), MAX(age) FROM users;
  • Group by
SELECT age, COUNT(*) FROM users GROUP BY age;

10. Joins​

  • Inner join
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;
  • Left join
SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

11. Indexes​

  • Create an index
CREATE INDEX idx_users_name ON users(name);
  • Drop an index
DROP INDEX idx_users_name;

12. Transactions​

  • Start transaction
BEGIN;
  • Commit transaction
COMMIT;
  • Rollback transaction
ROLLBACK;

13. Miscellaneous​

  • Show current user
SELECT current_user;
  • Show version
SELECT version();
  • Describe table
\d+ table_name