PostgreSQL Commands: Complete Cheat Sheet With Examples

PostgreSQL commands fall into two distinct categories: psql meta-commands (processed locally by the terminal client, starting with a backslash \) and standard PostgreSQL SQL Commands (processed by the server and ending with a semicolon ;).

Talk to a consultant

What Are PostgreSQL Commands?

PostgreSQL commands fall into two distinct categories: psql meta-commands (processed locally by the terminal client, starting with a backslash \) and standard PostgreSQL SQL Commands (processed by the server and ending with a semicolon ;).

These commands allow users to manage databases, tables, users, roles, and the data itself. They include both SQL statements like SELECT, INSERT, UPDATE, and DELETE, and meta-commands in the psql command-line interface (such as \dt for listing tables).

PostgreSQL commands are fundamental for database creation, modification, data retrieval, security, and maintenance.
This structured reference guide highlights the most frequently used commands for database administration, navigation, and data manipulation.

This structured reference guide highlights the most frequently used commands for database administration, navigation, and data manipulation.

How to Connect to PostgreSQL

To connect to a PostgreSQL database, you typically use the psql command-line client. The basic syntax is:

psql -U app_user -d company_db

1	psql -U app_user -d company_db

For example:

psql --username=app_user --dbname=company_db

1	psql --username=app_user --dbname=company_db

If the database is running on a different server, specify the host and port:

psql -h db.example.com -p 5432 -U app_user -d company_db

1	psql -h db.example.com -p 5432 -U app_user -d company_db

After running the command, PostgreSQL prompts you for a password if authentication is required. Once connected, you can execute SQL statements and psql meta-commands directly from the terminal.

You can also connect using a connection string:

psql "postgresql://app_user:StrongPassword123@localhost:5432/company_db"

1	psql "postgresql://app_user:StrongPassword123@localhost:5432/company_db"

After a successful connection, the psql prompt appears:

mydatabase=#

1	mydatabase=#

From there, you can start managing databases, creating tables, querying data, and running administrative commands. To exit the session, use:

\q

Understanding PostgreSQL Commands

Basic psql Commands

The psql client includes meta-commands that help you navigate and manage PostgreSQL databases. These commands begin with a backslash (\) and are executed directly by psql, not by the PostgreSQL server.

List available databases:

\l

Connect to a different database:

\c db_name

1	\c db_name

List all tables in the current database:

\dt

\dt

Describe the structure of a table:

\d your_table

1	\d your_table

List schemas:

\dn

\dn

Display available commands:

\?

Get help for SQL commands:

\h

Exit the psql session:

\q

These commands are useful for exploring database objects and managing sessions without writing SQL queries.

PostgreSQL Database Commands

Database commands are used to create, modify, and remove databases.

Create a new database:

CREATE DATABASE my_store_db;

1	CREATE DATABASE my_store_db;

List all databases:

\l

Connect to a database:

\c my_store_db;

1	\c my_store_db;

Rename a database:

ALTER DATABASE my_store_db RENAME TO newdatabase;

1	ALTER DATABASE my_store_db RENAME TO newdatabase;

Delete a database:

DROP DATABASE my_store_db;

1	DROP DATABASE my_store_db;

View information about the current connection:

\conninfo

\conninfo

These commands help administrators organize and manage multiple databases on a PostgreSQL server.

PostgreSQL Table Commands

Table commands are used to create and manage database tables.

Create a table:

CREATE TABLE team_members (
member_id SERIAL PRIMARY KEY,
full_name VARCHAR(120),
role_name VARCHAR(80)
);

CREATE TABLE team_members (

member_id SERIAL PRIMARY KEY,

full_name VARCHAR(120),

role_name VARCHAR(80)

);

View table definitions:

\d team_members;

1	\d team_members;

Add a column:

ALTER TABLE team_members
ADD COLUMN work_email VARCHAR(255);

1 2	ALTER TABLE team_members ADD COLUMN work_email VARCHAR(255);

Rename a table:

ALTER TABLE team_members
RENAME TO company_staff;

1 2	ALTER TABLE team_members RENAME TO company_staff;

Remove a column:

ALTER TABLE company_staff
DROP COLUMN work_email;

1 2	ALTER TABLE company_staff DROP COLUMN work_email;

Delete a table:

DROP TABLE company_staff;

1	DROP TABLE company_staff;

These commands define how data is structured and stored within a database.

PostgreSQL Data Query Commands

Data query commands retrieve information from tables.

Retrieve all rows:

SELECT *
FROM staff_members;

1 2	SELECT * FROM staff_members;

Retrieve specific columns:

SELECT full_name, team_name
FROM staff_members;

1 2	SELECT full_name, team_name FROM staff_members;

Filter results:

SELECT *
FROM staff_members
WHERE team_name = 'Marketing';

SELECT *

FROM staff_members

WHERE team_name = 'Marketing';

Sort results:

SELECT *
FROM staff_members
ORDER BY full_name ASC;

SELECT *

FROM staff_members

ORDER BY full_name ASC;

Limit returned rows:

SELECT *
FROM staff_members
LIMIT 5;

SELECT *

FROM staff_members

LIMIT 5;

Aggregate data:

SELECT team_name, COUNT(*) AS total_staff
FROM staff_members
GROUP BY team_name;

SELECT team_name, COUNT(*) AS total_staff

FROM staff_members

GROUP BY team_name;

These commands are used to search, analyze, and report on stored data.

PostgreSQL Data Modification Commands

Data modification commands add, update, and remove records.

Insert a new row:

INSERT INTO staff_members (full_name, team_name)
VALUES ('Jane Doe', 'Operations');

1 2	INSERT INTO staff_members (full_name, team_name) VALUES ('Jane Doe', 'Operations');

Update existing data:

UPDATE staff_members
SET team_name = 'Finance'
WHERE member_id = 3;

UPDATE staff_members

SET team_name = 'Finance'

WHERE member_id = 3;

Delete rows:

DELETE FROM staff_members
WHERE member_id = 3;

1 2	DELETE FROM staff_members WHERE member_id = 3;

Remove all rows from a table:

TRUNCATE TABLE staff_members;

1	TRUNCATE TABLE staff_members;

Insert data returned by a query:

INSERT INTO archived_staff_members
SELECT *
FROM staff_members
WHERE team_name = 'Operations';

INSERT INTO archived_staff_members

SELECT *

FROM staff_members

WHERE team_name = 'Operations';

These commands allow users to maintain accurate and up-to-date information within PostgreSQL databases.

PostgreSQL User and Role Commands

PostgreSQL uses roles to manage authentication and permissions. A role can function as a user, a group, or both.

Create a new role:

CREATE ROLE reporting_team;

1	CREATE ROLE reporting_team;

Create a user with a password:

CREATE USER dashboard_user
WITH PASSWORD 'StrongPass_2026';

1 2	CREATE USER dashboard_user WITH PASSWORD 'StrongPass_2026';

Grant privileges on a database:

GRANT CONNECT
ON DATABASE company_db
TO dashboard_user;

GRANT CONNECT

ON DATABASE company_db

TO dashboard_user;

Grant privileges on a table:

GRANT SELECT, UPDATE
ON staff_members
TO dashboard_user;

GRANT SELECT, UPDATE

ON staff_members

TO dashboard_user;

Assign a role to another user:

GRANT reporting_team TO dashboard_user;

1	GRANT reporting_team TO dashboard_user;

Change a user’s password:

ALTER USER dashboard_user
WITH PASSWORD 'UpdatedPass_2026';

1 2	ALTER USER dashboard_user WITH PASSWORD 'UpdatedPass_2026';

Remove a role or user:

DROP ROLE dashboard_user;

1	DROP ROLE dashboard_user;

List roles:

\du

\du

These commands help control access to database resources and enforce security policies.

PostgreSQL Index Commands

Indexes improve query performance by allowing PostgreSQL to locate data more efficiently.

Create an index:

CREATE INDEX idx_staff_full_name
ON staff_members(full_name);

1 2	CREATE INDEX idx_staff_full_name ON staff_members(full_name);

Create a unique index:

CREATE UNIQUE INDEX idx_staff_work_email
ON staff_members(work_email);

1 2	CREATE UNIQUE INDEX idx_staff_work_email ON staff_members(work_email);

View indexes for a table:

\d staff_members;

1	\d staff_members;

Remove an index:

DROP INDEX idx_staff_full_name;

1	DROP INDEX idx_staff_full_name;

Create a multi-column index:

CREATE INDEX idx_staff_team_name
ON staff_members(team_name, full_name);

1 2	CREATE INDEX idx_staff_team_name ON staff_members(team_name, full_name);

Rebuild an index:

REINDEX INDEX idx_staff_full_name;

1	REINDEX INDEX idx_staff_full_name;

Indexes can significantly speed up searches, joins, and sorting operations, but they also require storage and maintenance during data updates.

PostgreSQL Schema Commands

Schemas organize database objects into logical namespaces. They help separate applications, users, or environments within the same database.

Create a schema:

CREATE SCHEMA reporting;

1	CREATE SCHEMA reporting;

List schemas:

\dn

\dn

Create a table in a schema:

CREATE TABLE reporting.monthly_reports (
report_id SERIAL PRIMARY KEY,
created_on DATE
);

CREATE TABLE reporting.monthly_reports (

report_id SERIAL PRIMARY KEY,

created_on DATE

);

Set the default schema:

SET search_path TO reporting;

1	SET search_path TO reporting;

Rename a schema:

ALTER SCHEMA reporting
RENAME TO reporting_archive;

1 2	ALTER SCHEMA reporting RENAME TO reporting_archive;

Delete a schema:

DROP SCHEMA reporting;

1	DROP SCHEMA reporting;

Delete a schema and all its objects:

DROP SCHEMA reporting_archive CASCADE;

1	DROP SCHEMA reporting_archive CASCADE;

Schemas provide a way to group related database objects and avoid naming conflicts.

PostgreSQL Backup and Restore Commands

PostgreSQL provides command-line utilities for creating backups and restoring databases.

Create a backup using pg_dump:

pg_dump --username=db_admin --dbname=company_db --file=company_db_backup.sql

1	pg_dump --username=db_admin --dbname=company_db --file=company_db_backup.sql

Create a compressed custom-format backup:

pg_dump -U db_admin -Fc company_db > company_db.dump

1	pg_dump -U db_admin -Fc company_db > company_db.dump

Restore a SQL backup:

psql --username=db_admin --dbname=company_db --file=company_db_backup.sql

1	psql --username=db_admin --dbname=company_db --file=company_db_backup.sql

Restore a custom-format backup:

pg_restore --username=db_admin --dbname=company_db company_db.dump

1	pg_restore --username=db_admin --dbname=company_db company_db.dump

Back up all databases:

pg_dumpall --username=db_admin --file=cluster_backup.sql

1	pg_dumpall --username=db_admin --file=cluster_backup.sql

Restore all databases:

psql --username=db_admin --file=cluster_backup.sql

1	psql --username=db_admin --file=cluster_backup.sql

Regular backups are essential for disaster recovery, migration, and protecting against data loss.

PostgreSQL Transaction Commands

Transactions allow multiple operations to be executed as a single unit of work. They help maintain data consistency and integrity.

Start a transaction:

BEGIN;

BEGIN;

Execute one or more statements:

UPDATE wallet_accounts
SET current_balance = current_balance - 250
WHERE account_id = 101;

UPDATE wallet_accounts
SET current_balance = current_balance + 250
WHERE account_id = 202;

COMMIT;

UPDATE wallet_accounts

SET current_balance = current_balance - 250

WHERE account_id = 101;

UPDATE wallet_accounts

SET current_balance = current_balance + 250

WHERE account_id = 202;

COMMIT;

Cancel all changes made during the transaction:

ROLLBACK;

ROLLBACK;

Create a savepoint:

SAVEPOINT payment_checkpoint;

1	SAVEPOINT payment_checkpoint;

Roll back to a savepoint:

ROLLBACK TO SAVEPOINT payment_checkpoint;

1	ROLLBACK TO SAVEPOINT payment_checkpoint;

Transactions ensure that operations either complete successfully as a group or leave the database unchanged if an error occurs.

Tips from the expert

Perry Clark

Professional Services Consultant

Perry Clark is a seasoned open source consultant with NetApp. Perry is passionate about delivering high-quality solutions and has a strong background in various open source technologies and methodologies, making him a valuable asset to any project.

In my experience, here are tips that can help you better manage and use PostgreSQL commands:

Use \x auto for wide query results: Large rows containing JSON, arrays, or many columns become much easier to read. Keep this enabled in your psql profile to improve day-to-day troubleshooting.
Master \gexec for dynamic administration: Generate SQL with a query and execute the results immediately. This is extremely useful for bulk maintenance tasks across multiple tables or schemas.
Use RETURNING to reduce round trips: Many developers run an INSERT followed by a SELECT to retrieve generated values. INSERT ... RETURNING eliminates the extra query and reduces application latency.
Create indexes concurrently in production: Standard CREATE INDEX can block writes. Use CREATE INDEX CONCURRENTLY when adding indexes to busy production tables to avoid service interruptions.
Use \watch as a lightweight monitoring tool: Any query can become a real-time dashboard. This is invaluable during deployments, incident investigations, and performance tuning sessions.

Common PostgreSQL Command Examples

Create a Database, Table, and Insert Data

The following example creates a database, creates a table, and inserts a record.

1. Create a database:

CREATE DATABASE project_db;

1	CREATE DATABASE project_db;

2. Connect to the database:

\c project_db;

1	\c project_db;

3. Create a table:

CREATE TABLE staff_records (
  staff_id SERIAL PRIMARY KEY,
  full_name VARCHAR(120),
  team_name VARCHAR(60)
);

CREATE TABLE staff_records (

staff_id SERIAL PRIMARY KEY,

full_name VARCHAR(120),

team_name VARCHAR(60)

);

4. Insert data:

INSERT INTO staff_records (full_name, team_name)
VALUES ('John Smith', 'Technology');

1 2	INSERT INTO staff_records (full_name, team_name) VALUES ('John Smith', 'Technology');

5. Verify the inserted record:

SELECT *
FROM staff_records;

1 2	SELECT * FROM staff_records;

This workflow demonstrates the basic steps required to start working with a new PostgreSQL database.

Query and Update Records

Once data exists in a table, you can retrieve and modify it using SELECT and UPDATE statements.

1. Query all records:

SELECT *
FROM team_members;

1 2	SELECT * FROM team_members;

2. Filter records with a condition:

SELECT *
FROM team_members
WHERE team = 'Product';

SELECT *

FROM team_members

WHERE team = 'Product';

3. Update a record:

UPDATE team_members
SET team = 'Operations'
WHERE full_name = 'John McCain';

UPDATE team_members

SET team = 'Operations'

WHERE full_name = 'John McCain';

4. Verify the update:

SELECT *
FROM team_members
WHERE full_name = ‘John McCain’;

Using a WHERE clause is important because it limits the update to specific rows.

Delete Records Safely

When deleting data, always review the affected rows before running a DELETE statement.

1. Check which records will be removed:

SELECT *
FROM team_members
WHERE member_id = 5;

SELECT *

FROM team_members

WHERE member_id = 5;

2. Delete a specific record:

DELETE FROM team_members
WHERE member_id = 5;

1 2	DELETE FROM team_members WHERE member_id = 5;

3. Confirm the deletion:

SELECT *
FROM team_members
WHERE member_id = 5;

SELECT *

FROM team_members

WHERE member_id = 5;

You can also run the deletion inside a transaction to verify the results before committing:

BEGIN;

DELETE FROM team_members
WHERE member_id = 5;

SELECT *
FROM team_members;

ROLLBACK;

BEGIN;

DELETE FROM team_members

WHERE member_id = 5;

SELECT *

FROM team_members;

ROLLBACK;

Using transactions and precise WHERE clauses helps prevent accidental data loss.

PostgreSQL Commands Best Practices

Here are some of the ways to improve your use of PostgreSQL commands.

1. Always Use WHERE with UPDATE and DELETE

The UPDATE and DELETE commands affect every row in a table when a WHERE clause is omitted. This can lead to accidental data loss or large-scale changes that are difficult to reverse.

Unsafe example:

UPDATE staff_records
SET team_name = 'Operations';

1 2	UPDATE staff_records SET team_name = 'Operations';

Safer approach:

UPDATE staff_records
SET team_name = 'Operations'
WHERE staff_id = 25;

UPDATE staff_records

SET team_name = 'Operations'

WHERE staff_id = 25;

Before running an UPDATE or DELETE, execute a SELECT statement with the same WHERE condition to verify which rows will be affected.

2. Use Transactions for Risky Changes

Transactions allow you to group multiple operations and either commit them together or roll them back if something goes wrong. This is especially useful when modifying large amounts of data.

Example:

BEGIN;

UPDATE staff_records
SET team_name = 'Customer Success'
WHERE team_name = 'Operations';

COMMIT;

BEGIN;

UPDATE staff_records

SET team_name = 'Customer Success'

WHERE team_name = 'Operations';

COMMIT;

If the results are not what you expected, you can cancel the changes before committing:

ROLLBACK;

ROLLBACK;

Using transactions reduces the risk of accidental changes and helps maintain data consistency.

3. Use EXPLAIN Before Optimizing Queries

The EXPLAIN command shows how PostgreSQL plans to execute a query. It helps identify slow operations such as sequential scans, expensive joins, or inefficient sorting.

Example:

EXPLAIN
SELECT *
FROM staff_records
WHERE team_name = 'Technology';

EXPLAIN

SELECT *

FROM staff_records

WHERE team_name = 'Technology';

To view estimated execution costs and actual execution statistics, use:

EXPLAIN ANALYZE
SELECT *
FROM staff_records
WHERE team_name = 'Technology';

EXPLAIN ANALYZE

SELECT *

FROM staff_records

WHERE team_name = 'Technology';

Reviewing execution plans before making changes helps ensure optimization efforts are focused on real performance bottlenecks.

4. Use Indexes on Frequently Filtered Columns

Indexes improve query performance by reducing the amount of data PostgreSQL must scan. They are most effective on columns that are frequently used in WHERE, JOIN, and ORDER BY clauses.

Create an index:

CREATE INDEX idx_staff_records_team_name
ON staff_records(team_name);

1 2	CREATE INDEX idx_staff_records_team_name ON staff_records(team_name);

For example, if queries often filter by department, an index can significantly reduce query execution time. However, indexes are not free. Each index consumes storage space and adds overhead to INSERT, UPDATE, and DELETE operations. Create indexes only when they provide measurable benefits.

5. Avoid Using the postgres Superuser for Applications

The default postgres account has unrestricted access to the entire PostgreSQL server. Using it for applications increases the risk of accidental changes and security issues.

Instead, create a dedicated user with only the permissions required by the application:

CREATE USER reporting_user
WITH PASSWORD 'StrongPassword_2026';

1 2	CREATE USER reporting_user WITH PASSWORD 'StrongPassword_2026';

Grant only the necessary privileges:

GRANT SELECT, INSERT, UPDATE, DELETE
ON staff_records
TO reporting_user;

GRANT SELECT, INSERT, UPDATE, DELETE

ON staff_records

TO reporting_user;

Following the principle of least privilege limits potential damage if application credentials are compromised and improves overall database security.

Running PostgreSQL in Production with NetApp Instaclustr

The commands in this guide cover the day-to-day work of creating databases, querying and modifying data, managing roles, and maintaining your environment, but running PostgreSQL reliably in production also means handling provisioning, monitoring, high availability, backups, and version upgrades. NetApp Instaclustr offers a fully hosted and managed PostgreSQL service that runs in the cloud or on-premises and delivers a production-ready PostgreSQL cluster backed by 24×7 expert support, letting your teams focus on building applications instead of administering database infrastructure.

Key capabilities of Instaclustr for PostgreSQL:

Fully managed and 100% open source: Instaclustr customizes and optimizes PostgreSQL on all major cloud providers and on-premises data centers, with no proprietary features or lock-in, running in your cloud account or theirs.
Industry-leading 99.99% SLA: Instaclustr’s availability SLAs for PostgreSQL set the service apart and are backed by continuous maintenance and version upgrades.
Multi-region replication: Read replicas can be created in secondary regions for high availability, minimizing latency and maximizing uptime.
PGBouncer connection pooling: A lightweight connection pooler enhances database performance and scalability through efficient connection management and resource optimization.
DevOps-friendly provisioning and monitoring: Provision via console, REST API, or Terraform, and monitor through built-in dashboards or the Prometheus API and other integrations.
Enterprise-grade security and compliance: The platform is SOC2, ISO27001, and ISO27018 certified, meets GDPR requirements, and offers PCI-compliant solutions.
pgvector for AI workloads: Instaclustr supports the pgvector extension, enabling efficient storage and similarity search of high-dimensional vector data so you can power RAG and other AI applications directly within PostgreSQL.

Ready to run PostgreSQL without the operational overhead? Explore Instaclustr’s fully managed PostgreSQL service and spin up a production-ready cluster in minutes.

Spin up a cluster
In minutes

Check it out

PostgreSQL Commands: Complete Cheat Sheet With Examples

What Are PostgreSQL Commands?

How to Connect to PostgreSQL

Understanding PostgreSQL Commands

Basic psql Commands

PostgreSQL Database Commands

PostgreSQL Table Commands

PostgreSQL Data Query Commands

PostgreSQL Data Modification Commands

PostgreSQL User and Role Commands

PostgreSQL Index Commands

PostgreSQL Schema Commands

PostgreSQL Backup and Restore Commands

PostgreSQL Transaction Commands

Tips from the expert

Common PostgreSQL Command Examples

Create a Database, Table, and Insert Data

Query and Update Records

Delete Records Safely

PostgreSQL Commands Best Practices

Running PostgreSQL in Production with NetApp Instaclustr

Spin up a clusterIn minutes

Spin up a cluster
In minutes