PostgreSQL Cheatsheet
#
PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language. It is known for its reliability, feature robustness, and performance.
General Commands
#
| Command/Option | Example | Description |
|---|
psql -U <user> -d <database> | psql -U postgres -d mydb | Connect to a PostgreSQL database with a specific user |
psql -U <user> -d <database> -h <host> | psql -U postgres -d mydb -h localhost | Connect to a PostgreSQL database on a specific host |
\q | \q | Exit the psql command-line interface |
Database Management
#
| Command/Option | Example | Description |
|---|
CREATE DATABASE <db_name>; | CREATE DATABASE mydb; | Create a new database |
DROP DATABASE <db_name>; | DROP DATABASE mydb; | Delete a database |
\l | \l | List all databases |
Table Management
#
| Command/Option | Example | Description |
|---|
\c <db_name> | \c mydb | Switch to a specific database |
CREATE TABLE <table_name> (column_definitions); | CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100)); | Create a new table |
DROP TABLE <table_name>; | DROP TABLE users; | Delete a table |
\dt | \dt | List all tables in the current database |
Data Manipulation
#
| Command/Option | Example | Description |
|---|
INSERT INTO <table> (columns) VALUES (values); | INSERT INTO users (name) VALUES ('John Doe'); | Insert new data into a table |
SELECT * FROM <table>; | SELECT * FROM users; | Retrieve all data from a table |
UPDATE <table> SET column = value WHERE condition; | UPDATE users SET name = 'Jane Doe' WHERE id = 1; | Update existing data in a table |
DELETE FROM <table> WHERE condition; | DELETE FROM users WHERE id = 1; | Delete data from a table |
Query Filtering and Sorting
#
| Command/Option | Example | Description |
|---|
SELECT column1, column2 FROM <table> WHERE condition; | SELECT name FROM users WHERE id = 1; | Retrieve specific data based on a condition |
| `SELECT * FROM ORDER BY column [ASC | DESC];` | SELECT * FROM users ORDER BY name ASC; |
Indexing
#
| Command/Option | Example | Description |
|---|
CREATE INDEX <index_name> ON <table> (column); | CREATE INDEX idx_name ON users (name); | Create an index on a column |
DROP INDEX <index_name>; | DROP INDEX idx_name; | Delete an index |
User Management
#
| Command/Option | Example | Description |
|---|
CREATE USER <username> WITH PASSWORD '<password>'; | CREATE USER myuser WITH PASSWORD 'mypassword'; | Create a new PostgreSQL user |
DROP USER <username>; | DROP USER myuser; | Delete a PostgreSQL user |
GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <username>; | GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser; | Grant privileges to a user |
REVOKE ALL PRIVILEGES ON DATABASE <db_name> FROM <username>; | REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser; | Revoke privileges from a user |
Backup and Restore
#
| Command/Option | Example | Description |
|---|
pg_dump -U <user> -d <db_name> > <file.sql> | pg_dump -U postgres -d mydb > mydb_backup.sql | Backup a database to a SQL file |
psql -U <user> -d <db_name> < <file.sql> | psql -U postgres -d mydb < mydb_backup.sql | Restore a database from a SQL file |
Server Status and Configuration
#
| Command/Option | Example | Description |
|---|
SHOW ALL; | SHOW ALL; | Show all configuration parameters |
SHOW <parameter>; | SHOW max_connections; | Show the value of a specific configuration parameter |
\watch <seconds> | \watch 5 | Continuously execute the previous query every specified number of seconds |
This cheatsheet covers the most commonly used PostgreSQL commands and options, helping you manage databases, tables, data, indexing, user permissions, backups, and server status effectively.