PostgreSQL Cheat Sheet

General Commands

• Connect to PostgreSQL Database

  psql -d database_name -U user_name
  

• List Databases

  \l
  

• Switch Database

  \c database_name
  

• List Tables

  \dt
  

• Describe Table Structure

  \d table_name
  

• List All Schemas

  \dn
  

• List All Stored Procedures/Functions

  \df
  

• List All Views

  \dv
  

• Exit psql

  \q
  

Database Operations

• Create Database

  CREATE DATABASE database_name;
  

• Drop Database

  DROP DATABASE IF EXISTS database_name;
  

User and Permissions

• Create User

  CREATE USER user_name WITH PASSWORD 'password';
  

• Grant All Privileges on Database

  GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name;
  

Table Operations

• Create Table

  CREATE TABLE table_name (
      column_name1 data_type1,
      column_name2 data_type2,
      ...
  );
  

• Drop Table

  DROP TABLE IF EXISTS table_name;
  

• Add a Column to Table

  ALTER TABLE table_name ADD COLUMN column_name data_type;
  

• Drop a Column from Table

  ALTER TABLE table_name DROP COLUMN column_name;
  

• Rename Table

  ALTER TABLE table_name RENAME TO new_table_name;
  

Data Manipulation

• Insert Data

  INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
  

• Update Data

  UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
  

• Delete Data

  DELETE FROM table_name WHERE condition;
  

Querying Data

• Select Query

  SELECT column1, column2, ... FROM table_name;
  

• Select with Condition

  SELECT * FROM table_name WHERE condition;
  

• Aggregate Functions

  SELECT COUNT(*), MAX(column1), MIN(column1), AVG(column1) FROM table_name;
  

• Join Tables

  SELECT * FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;
  

• Group By

  SELECT column, COUNT(*) FROM table_name GROUP BY column;
  

• Order By

  SELECT * FROM table_name ORDER BY column ASC|DESC;