PostgreSQL Command Cheat Sheet: Essential Reference for Databases, Tables, and Users

PostgreSQL is an advanced, open-source Object-Relational Database (ORDBMS) renowned for its stability, extensibility (supporting features like user-defined types and table inheritance), and strict adherence to the SQL standard. This cheat sheet provides a quick reference for fundamental DDL (Data Definition Language) and DML (Data Manipulation Language) commands used for managing core PostgreSQL objects.

---

I. PostgreSQL Database Management (DDL)

These commands operate at the highest hierarchical level, managing entire database instances. They are typically executed via the psql client or directly in SQL.

Database Commands

Action	Syntax
Create Database	CREATE DATABASE db_name;
Drop Database	DROP DATABASE db_name;
Access/Connect (psql)	\c db_name;
List All Databases (psql)	\l
Rename Database	ALTER DATABASE old_db_name RENAME TO new_db_name;
Change Database Owner	ALTER DATABASE db_name OWNER TO {new_owner_name | current_user | session_user};
Alter Configuration	ALTER DATABASE db_name SET configuration_parameter = value;

---

II. PostgreSQL Schema Management

Schemas are namespaces within a database, used to organize objects (tables, functions, etc.) and prevent naming conflicts. They simplify access control and logical grouping of objects.

Schema Commands

Action	Syntax
Create Schema	CREATE SCHEMA [IF NOT EXISTS] new_schema;
Drop Schema	DROP SCHEMA schema_name [CASCADE | RESTRICT];
Rename Schema	ALTER SCHEMA schema_name RENAME TO new_schema_name;
Change Schema Owner	ALTER SCHEMA schema_name OWNER TO {new_owner_name | SESSION_USER | CURRENT_USER};
Show All Schemas (psql)	\dn

---

III. PostgreSQL Table Management (DDL & DML)

These commands manage the definition (DDL) and manipulation (DML) of table structures and data.

Table DDL & DML Commands

Action	Syntax
Create Table	CREATE TABLE [IF NOT EXISTS] tab_name (column_name DATATYPE column_contraint);
Drop Table	DROP TABLE [IF EXISTS] tab_name [CASCADE | RESTRICT];
Rename Table	ALTER TABLE tab_name RENAME TO new_tab_name;
Add Column	ALTER TABLE tab_name ADD COLUMN new_col_name data_type constraint;
Drop Column	ALTER TABLE tab_name DROP COLUMN col_name;
Rename Column	ALTER TABLE tab_name RENAME COLUMN old_col_name TO new_col_name;
Fetch All Data (DML)	SELECT * FROM tab_name;
Insert Data (DML)	INSERT INTO tab_name(col_list) VALUES (value_list);
Delete Specific Records (DML)	DELETE FROM tab_name WHERE condition;
List Tables (psql)	\dt

---

IV. PostgreSQL Views and Indexes

Views simplify complex queries and provide a layer of abstraction, while Indexes speed up data retrieval performance.

Views and Indexes Commands

Action	Syntax
Create Standard View	CREATE OR REPLACE VIEW viewName AS query;
Create Materialized View	CREATE MATERIALIZED VIEW viewName AS query WITH DATA;
Refresh Materialized View	REFRESH MATERIALIZED VIEW CONCURRENTLY viewName;
Drop Standard View	DROP VIEW viewName;
Create Index	CREATE [UNIQUE] INDEX indexName ON tableName (column_list);
Drop Index	DROP INDEX indexName;

---

V. PostgreSQL User and Role Management (DCL)

Roles and Users are crucial for managing database access control and privileges (Data Control Language).

User and Role Commands

Action	Syntax
Create User/Role	CREATE USER user_name WITH option; -- (e.g., LOGIN, CREATEDB, PASSWORD '*')
Create Superuser	CREATE USER user_name WITH SUPERUSER;
Drop User	DROP USER [IF EXISTS] user_name;
Rename User	ALTER USER user_name RENAME TO new_user_name;
Change Password/Options	ALTER USER user_name WITH PASSWORD 'updated_password' VALID UNTIL 'expiry_date';
Find User Details (SQL)	SELECT usename, usesysid FROM pg_user;
List Users (psql)	\du

---

Frequently Asked Questions (FAQ) on PostgreSQL Commands

Q: What is the difference between CASCADE and RESTRICT when dropping objects?

A: The CASCADE option automatically drops all objects that depend on the object being dropped (e.g., dropping a schema with CASCADE will drop all tables in it). The RESTRICT option (default behavior) prevents the drop operation if any other object depends on it, forcing you to manually remove dependencies first.

Q: What are psql meta-commands (like \c or \dt)?

A: Meta-commands (starting with a backslash \) are commands executed within the psql interactive terminal, not sent directly to the PostgreSQL server as SQL. They are used for administrative functions like connecting to a database (\c), listing tables (\dt), or showing user permissions (\du).

Q: Why should I use a Materialized View instead of a regular View?

A: A Materialized View (MV) stores the query results physically on disk, making subsequent reads faster because the query doesn't need to be re-executed. A regular View runs the underlying query every time it is accessed. MVs are ideal for reporting or slow, complex queries where data can be slightly stale, requiring manual or scheduled refresh (using REFRESH MATERIALIZED VIEW).