Home Alternatives Time-series basics Data Center & Infrastructure Telemetry Postgres basics Postgres guides Postgres best practices

Postgres Cheat Sheet

By Tiger Data Team

Updated at Sep 8, 2026

Postgres Cheat Sheet

Every essential Postgres command, all in one place. Whether you're listing and creating databases, inspecting tables, managing users, building indexes, or running maintenance tasks, this reference has you covered, straight from the psql terminal or your favorite SQL client.

Think of a Postgres cheat sheet as your quick-reference companion: a handy guide to the most common psql meta-commands (shortcuts like \dt or \l that start with a backslash) and SQL statements you'll reach for again and again to manage PostgreSQL databases, tables, users, indexes, and configuration, all from the command line.

Databases

PostgreSQL database management relies on a small set of psql meta-commands and SQL statements. You list databases with \l, create them with CREATE DATABASE, and switch between them with \c <dbname>. These commands work in any psql session and apply to both local and remote PostgreSQL servers.

List PostgreSQL databases

\l

List all databases using \l (or \list) in psql.

\l+

List all databases using \l+ with more details, including description, tablespace, and DB size, in psql.

Help on CREATE DATABASE command syntax

\h CREATE DATABASE

Display help on SQL command syntax (for example, CREATE DATABASE) in psql.

Create database

CREATE DATABASE mytest;

Creates a new database called mytest. By default, the owner is the current login user.

\c test
You are now connected to database "test" as user "postgres".

Connect to a PostgreSQL database called test as the postgres user in psql.

Tables

Table inspection in psql uses \d and its variants to show structure, constraints, and disk size. You can also export data to CSV, check indexes, and add comments directly from the terminal.

Show table

\d TABLE_NAME

Show table definition including indexes, constraints, and triggers in psql.

Show details

\d+ TABLE_NAME

Show a more detailed table definition including description (comments) and physical disk size in psql.

List tables from current schema

\dt

List tables from the current schema in psql.

List tables from all schemas

\dt *.*

List tables from all schemas in psql.

List tables for a schema

\dt <name-of-schema>.*

List the tables in a specific schema in psql.

Copy table data to CSV file

\copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV

Export a table as CSV in psql to the current directory.

Check indexes for a table using SQL

SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND
schemaname='__schema_name__';

Show table indexes using SQL.

Collect statistics about table contents

ANALYZE [__table_name__]

Analyze a table and store the results in the pg_statistic system catalog. With no parameter, ANALYZE examines every table in the current database (‘table_name’ in square brackets means optional argument).

Add a comment on a table or column

COMMENT ON TABLE employee IS 'Stores employee records';

Add a comment on a table using SQL.

COMMENT ON COLUMN employee.emp_ssn IS 'Employee Social Security Number';

Add a comment on a column using SQL.

Approximate table row count

SELECT reltuples AS card FROM pg_class WHERE relname = '<table_name>';

Use this for fast (but not exact) counts from tables. It's helpful when a table has millions or billions of records and you want an estimated row count quickly.

Connecting

Connecting to PostgreSQL involves SSH access to the host, switching to the postgres system user, and entering the psql terminal. Once inside, you can check client and server versions and switch between databases.

$ ssh -l postgres 200.34.22.75
postgres@200.34.22.75's password:
Linux localhost 5.10.0-28-amd64  #1 SMP Debian 5.10.209-2 (2024-01-31) x86_64

root@localhost:~# su - postgres

Learn how to test your PostgreSQL connection.

Enter PostgreSQL terminal

postgres@localhost:~$ psql
psql (17.2)
Type "help" for help.
postgres=#

Enter the PostgreSQL command line via the psql client.

Connect to a database

\c test

You are now connected to database "test" as user "postgres".

Connect to a PostgreSQL database called test as the postgres user in psql.

Check psql client version

$ psql -V
psql (PostgreSQL) 17.2

Check the psql client version.

Check Postgres server version

select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

Check the Postgres server version using SQL.

Connection pooling tip. For applications managing many concurrent connections, pair your PostgreSQL setup with a connection pooler such as PgBouncer. Each PostgreSQL connection forks a new process and consumes memory, so pooling is a recommended production practice at scale.

Queries

PostgreSQL queries cover table creation, data insertion, conditional selects, and safe updates wrapped in transactions. The examples below use a simple employee table to demonstrate each pattern.

Create a new table

CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,        -- AUTO_INCREMENT integer, as primary key
  emp_name VARCHAR(50) NOT NULL,
  emp_salary NUMERIC(9,2) NOT NULL
);

Creates a new table using SQL.

Modern alternative. SERIAL is the traditional shorthand for an auto-incrementing integer. In PostgreSQL 10 and later, the SQL-standard equivalent is GENERATED ALWAYS AS IDENTITY. For example, emp_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY. Both work, and GENERATED ALWAYS AS IDENTITY is preferred for new schemas.

Display table

\d employee
                                    Table "public.employee"
Column      |         Type          |                         Modifiers
------------+-----------------------+-----------------------------------------------------------
emp_id     | integer               | not null default nextval('employee_emp_id_seq'::regclass)
emp_name   | character varying(50) | not null
emp_salary | numeric(9,2)          | not null

Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)

Display a table definition in psql.

Insert query

INSERT INTO employee (emp_name, emp_salary) VALUES
('John', 5000),
('Jack', 4568.0),
('Robert',7500.50);

Insert records into a table using SQL.

Conditional select query

select * from employee where emp_salary >= 5000;
 emp_id | emp_name | emp_salary
--------+----------+------------
    1   | John     |    5000.00
    3   | Robert   |    7500.50
(2 rows)

Select data based on a filter condition (for example, emp_salary >= 5000) using SQL.

Conditional update query (safe update)

BEGIN;
  UPDATE employee SET emp_salary = 6000 WHERE emp_name = 'John';
COMMIT;

Update a record based on a condition (for example, update emp_salary for employee John) using SQL.

Records aren't committed to the database unless you issue a COMMIT. You can undo updates by issuing ROLLBACK instead of COMMIT.

Functions

Create a new function

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Create a function to add two integers using SQL.

This function takes two integers as parameters. IMMUTABLE means the function can't modify the database and always returns the same result when given the same argument values.

Calling a function

select add(5,9);
 add
-----
  14
(1 row)

Call a function using SQL.

List functions

\df
                        List of functions
 Schema | Name | Result data type | Argument data types |  Type
--------+------+------------------+---------------------+--------
 public | add  | integer          | integer, integer    | normal
(1 row)

Display all functions in psql.

\df+

Display all functions with additional information, including owner, source code, and description, in psql.

Edit a function

\ef <function_name>

Edit a function in the default editor in psql.

Views

Listing PostgreSQL views helps you understand database structure, optimize queries, analyze dependencies, document schemas, and maintain security and access control.

List views

\dv

List views from the current schema in psql.

\dv *.*

List views from all schemas in psql.

Users

User management in PostgreSQL covers setting passwords, listing roles, and logging in with specific credentials. The pg_user catalog and \du meta-command give you a full picture of who has access and what privileges they hold.

Set or reset a Postgres user password

\password username

Set or reset a password for a PostgreSQL database user in psql.

For example, to change the password for the current user postgres:

\password postgres
Enter new password: xxxx
Enter it again: xxxx

Show all users

select * from pg_user;

Display PostgreSQL database users using SQL.

\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 testrole  |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Display PostgreSQL database roles in psql.

$ psql -U testuser mytest
Password for user testuser: ......
psql (17.2)
Type "help" for help.

Indexes

Indexes in PostgreSQL speed up query execution by letting the planner avoid full table scans. You can create, inspect, and drop indexes using a combination of SQL statements and psql meta-commands.

Create a new index on a table

create index idx_employee_emp_name on employee using btree (emp_name asc);

Create a new index on the emp_name column of the employee table using SQL.

This index specifies btree as the index method and uses asc to store the index key column data in ascending order.

View indexes of a table

\d employee
postgres=# \d employee;
                                    Table "public.employee"
   Column   |         Type          |                         Modifiers
------------+-----------------------+-----------------------------------------------------------
 emp_id     | integer               | not null default nextval('employee_emp_id_seq'::regclass)
 emp_name   | character varying(50) | not null
 emp_salary | numeric(9,2)          | not null
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
    "idx_employee_emp_name" btree (emp_name)

List indexes of a table along with the table definition in psql.

List all indexes

\di
                      List of relations
 Schema |         Name          | Type  |  Owner   |  Table
--------+-----------------------+-------+----------+----------
 public | employee_pkey         | index | postgres | employee
 public | idx_employee_emp_name | index | postgres | employee
(2 rows)

List all indexes from all tables in psql.

Indexes on TimescaleDB hypertables use the same CREATE INDEX syntax as regular PostgreSQL tables. By default, TimescaleDB automatically creates an index on the time column when you create a hypertable, but any other indexes (e.g., on a device or sensor ID) still need to be created explicitly, just as they would on a regular table. Behind the scenes, TimescaleDB applies each index across all chunks (the partitions that make up a hypertable), including new ones created in the future. Try Tiger Cloud for free today.

Drop an index from a table

drop index idx_employee_emp_name;

Drop an existing index from a table using SQL.

List installed extensions

\dx

List all installed PostgreSQL extensions in psql.

\dx+

List extensions with additional detail in psql.

Constraints

Postgres constraints are rules enforced on data columns within a table to maintain data integrity and prevent the insertion of invalid data. PostgreSQL supports primary key, unique, check, and foreign key constraints, each enforcing a different aspect of data validity.

Create a table with primary and unique constraints

CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,
  emp_name VARCHAR(50) NOT NULL,
  emp_ssn VARCHAR (30) NOT NULL UNIQUE,
  emp_salary NUMERIC(9,2) NOT NULL
);

Creates a new table with primary and unique key constraints using SQL.

A Primary Key Constraint enforces the uniqueness of a column or set of columns, ensuring each row in a table is uniquely identified. A Unique Constraint ensures all values in a column or set of columns are distinct, except for null values.

Avoid duplicate records

INSERT INTO employee (emp_name, emp_ssn, emp_salary) values ('Rohit', '1234', 5000.0);
INSERT 0 1

INSERT INTO employee (emp_name, emp_ssn, emp_salary) values ('Mason', '1234', 7500.0);

ERROR:  duplicate key value violates unique constraint "employee_emp_ssn_key"
DETAIL:  Key (emp_ssn)=(1234) already exists.

Insert records into a table with unique key constraints specified using SQL.

This table uses emp_id as the primary key column and a unique constraint on emp_ssn to prevent duplicate social security numbers from being entered.

Create a table with a check constraint

CREATE TABLE orders(
  ord_no integer,
  ord_date date,
  ord_qty numeric,
  ord_amount numeric CHECK (ord_amount>0)
);

Creates a new table with a check constraint specified using SQL.

insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (1, '2019-08-29', 1, 10);
INSERT 0 1

insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (2, '2019-08-29', 1, 0);
ERROR:  new row for relation "orders" violates check constraint "orders_ord_amount_check"
DETAIL:  Failing row contains (2, 2019-08-29, 1, 0).

Insert records into a table with check constraints specified using SQL.

A Check Constraint verifies that all values in a column or set of columns satisfy a specified condition or expression. The check constraint on ord_amount > 0 means any record with ord_amount <= 0 will fail to insert.

Define a relation between two tables using a foreign key constraint

CREATE TABLE IF NOT EXISTS department (
  dept_id SERIAL PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,
  emp_name VARCHAR(50) NOT NULL,
  emp_ssn VARCHAR (30) NOT NULL UNIQUE,
  emp_salary NUMERIC(9,2) NOT NULL,
  emp_dept_id INTEGER REFERENCES department (dept_id)    -- Foreign Key
);

Creates the department and employee tables and defines a relation between an employee and a department using a foreign key (REFERENCES) in SQL.

A Foreign Key Constraint establishes a link between data in two tables, enforcing referential integrity by preserving the relationships between the linked tables.

Check constraints on a table

\d employee;
                                     Table "public.employee"
   Column    |         Type          |                         Modifiers
-------------+-----------------------+-----------------------------------------------------------
 emp_id      | integer               | not null default nextval('employee_emp_id_seq'::regclass)
. . .
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
    "employee_emp_ssn_key" UNIQUE CONSTRAINT, btree (emp_ssn)
Foreign-key constraints:
    "employee_emp_dept_id_fkey" FOREIGN KEY (emp_dept_id) REFERENCES department(dept_id)

Display constraints on a table using the \d option in psql.

Identifiers

PostgreSQL provides a rich set of operators and functions for working with strings and numbers. The examples below cover concatenation, math operators, string manipulation, and quoting conventions.

String concatenate operator

select 'Gordon' || ' ' || 'Moore' As fullName;
   fullname
--------------
 Gordon Moore
(1 row)

Concatenates two or more strings using ||. This operator works on table columns as well, for example select first_name || ' ' || last_name As fullName from person.

Square and cube root operators

select |/25 As sqrt;
 sqrt
------
   5
(1 row)

Square root operator.

select ||/125 As cubert;
 cubert
------
   5
(1 row)

Cube root operator.

Factorial function

select factorial(5) As factorial;
 factorial
-----------
    120
(1 row)

The factorial(n) function replaced the deprecated ! postfix operator, which was removed in PostgreSQL 14.

Binary complement operator

select ~60 As compl;
  compl
----------
   -61
(1 row)

Binary 2's complement. This operator flips bits, so if A = 60 (binary 0011 1100), then ~A = 1100 0011.

String lower and upper functions

select lower('Rohit Kumawat') As lowerCase, upper('Rohit Kumawat') As upperCase;
   lowercase   |   uppercase
---------------+---------------
 rohit kumawat | ROHIT KUMAWAT
(1 row)

Postgres lower() and upper() functions using SQL.

Number of characters in a string

select char_length('Arizona') as num_chars;
 num_chars
-----------
     7
(1 row)

Count the number of characters in a string using SQL.

Location of a specified substring

select position('pan' in 'japan') As pos;
 pos
-----
  3
(1 row)

Find the location of a specified substring using SQL (uses 1-based indexing).

Extract a substring

select substring('postgres' from 3 for 3) As sub_string;
 sub_string
------------
   stg
(1 row)

Extract a substring from postgres starting at the third character for a length of three characters using SQL.

Insert a newline in SQL output

postgres=# select 'line 1'||E'\n'||'line 2' As newline;
 newline
---------
 line 1 +
 line 2
(1 row)

Insert a new line using E'\n'. You can also use the chr() function. The E prefix enables escape string constants, which support sequences including \b (backspace), \f (form feed), \n (newline), \r (carriage return), and \t (tab).

Quote identifier

UPDATE "my table" SET "a&b" = 0;

Double quotes act as delimited identifiers, allowing table or column names that would otherwise be invalid, such as those containing spaces or ampersands. Double quotes also escape reserved keywords in PostgreSQL. Quoting also makes identifiers case-sensitive, so "my table" and “My Table” are treated as different names.

Dollar-quoted string constant

select $$Maria's dogs$$ As col;
 col
--------------------
 Maria's dogs
(1 row)

Use dollar-quoted string constants instead of single quotes when a string contains many single quotes or backslashes. PostgreSQL treats everything between the $$ delimiters as a literal string.

Toggle query timing

\timing

Toggle the display of how long each SQL statement takes to execute in psql. Useful for quick performance checks without running EXPLAIN.

Maintenance

Garbage collect (reclaim storage)

VACUUM [__Table__]

vacuum(verbose, analyze) employee;
INFO:  vacuuming "public.employee"
INFO:  scanned index "employee_pkey" to remove 1 row versions
. . .
sample, 1 estimated total rows
VACUUM

Use the VACUUM command to reclaim storage from deleted rows in the employee table using SQL.

Table rows that are deleted or made obsolete by an update aren't physically removed from their table until a VACUUM command runs. Run VACUUM periodically, especially on frequently-updated tables. The ‘verbose’ option prints a detailed vacuum activity report for each table, and ‘analyze’ option updates statistics for the table.

Gather statistics

ANALYZE [__table__]

analyze verbose employee;
INFO:  analyzing "public.employee"
INFO:  "employee": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
ANALYZE

Analyze a table and store the results in the pg_statistic system catalog using SQL.

ANALYZE gathers statistics for the query planner to create the most efficient query execution plans. Accurate statistics help the planner choose the most appropriate query plan, improving query processing speed. With no table name specified, ANALYZE examines every table in the current database. The verbose option prints a detailed analyze activity report for each table.

Monitoring

Monitoring active PostgreSQL sessions helps you identify long-running queries, idle connections, and blocking processes before they affect application performance.

Session monitor

SELECT
    pid,
    datname,
    usename,
    application_name,
    client_hostname,
    state,
    client_port,
    backend_start,
    query_start,
    query
FROM pg_stat_activity;

Monitor Postgres sessions using SQL.

Key columns to know:

pid: The backend process ID
datname: The database name
usename: The user running the query
application_name: The client application name
state: The state of the session (active, waiting, idle, and so on)
query: The query being executed

Cancel a running query

SELECT pg_cancel_backend(pid);

Cancel a running query by providing its pid. This is useful for terminating long-running queries using SQL.

Biggest PostgreSQL tables and indexes by size

SELECT
  nspname || '.' || relname AS "Object Name", relkind As "Object Type",
  pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;

List the top 20 largest tables and indexes, excluding catalog tables, using SQL.

Backup

Database backup with default options

$ pg_dump mydb > mydb.bak.sql

Create a backup for a database called mydb in a plain-text SQL script file (mydb.bak.sql) using pg_dump.

Backups in TimescaleDB are fully automated. Learn how TimescaleDB handles database backups and disaster recovery.

Database backup with customized options

$ pg_dump -c -C -F p -f mydb.bak.sql mydb

Creates a backup for a database called mydb in plain text format with drop and create database commands included in the output file mydb.bak.sql using pg_dump.

Backup options:

-c/ --clean: output commands to clean (drop) database objects before writing commands to create them
-C/--create: begin output with a CREATE DATABASE command and reconnect to the created database
-F: format of the output (p means plain SQL output, c means custom archive format suitable for pg_restore)
-f: backup output file name

Remote backup

$ pg_dump -h <remote_host> -p <port> -U <user> -f mydb.bak mydb

Run pg_dump on the client computer to back up data on a remote Postgres server. Use -h to specify the IP address of your remote host and -p to identify the port on which PostgreSQL is listening.

All databases backup

$ pg_dumpall > alldb.bak.sql

Back up all databases along with database roles and cluster-wide information using pg_dumpall.

Restore

Restore from a backup file (.sql)

$ psql -U username -d db_name -f filename.sql

Restore a plain-text backup (.sql) generated by pg_dump or pg_dumpall using the psql utility. Use -d to specify the target database. You can omit -d if the file already includes its own connection commands, as with pg_dump -C or pg_dumpall output.

Restore from a custom archive backup file (.bak)

$ pg_restore -d db_name /path/to/your/file/db_name.bak -c -U db_user

Restore a custom archive backup (.bak) using the pg_restore utility. The target database must already exist, since` -c` alone drops and recreates objects within it, but doesn't create the database itself.

We put together a guide to help you restore your PostgreSQL database.

Configuration

Stop, start, and restart the PostgreSQL service

service postgresql stop

Stop the PostgreSQL service through the root user on Linux.

service postgresql start

Start the PostgreSQL service through the root user on Linux.

service postgresql restart

Restart the PostgreSQL service through the root user on Linux.

If you're running from a non-root user, prefix your command with sudo, and make sure the non-root user is already in the sudoers list. Some distributors don't provide these commands, so check your distribution's documentation.

Display configuration parameters

show all;

List all current runtime configuration parameters in psql.

Display configuration parameters using SQL

select * from pg_settings;

List all current runtime configuration parameters using SQL, with additional details including descriptions.

Show the current setting for max_connections

SELECT current_setting('max_connections');
current_setting
-----------------
 100
(1 row)

Display the current value set for the max_connections parameter using SQL.

Show the PostgreSQL config file location

show config_file;
config_file
------------------------------------------
 /etc/postgresql/17/main/postgresql.conf
(1 row)

Show the PostgreSQL configuration file location in psql.

The PostgreSQL configuration files are stored in the directory shown by the command above. The main configuration file is postgresql.conf. Find out how to connect using pg_service.conf.

Display contents of the Postgres config file

postgres@localhost:~$ less /etc/postgresql/17/main/postgresql.conf

 . . . .
data_directory = '/var/lib/postgresql/17/main'         # use data in another directory

hba_file = '/etc/postgresql/17/main/pg_hba.conf' # host-based authentication file

ident_file = '/etc/postgresql/17/main/pg_ident.conf' # ident configuration file

listen_addresses = '*'                  # what IP address(es) to listen on,
                                        # comma-separated list of addresses,
                                        # defaults to 'localhost', use '*' for all
port = 5432
. . . .

Key directives in postgresql.conf:

data_directory, which tells Postgres where the database files are stored
hba_file, which points to the host-based authentication file
port, which sets the TCP port number (default is 5432)

Keep your Postgres skills sharp

This cheat sheet covers the commands you'll reach for most often across databases, tables, queries, indexes, constraints, maintenance, monitoring, backup, restore, and configuration. Bookmark it and return whenever you need a quick reference.

For production PostgreSQL workloads, pairing these commands with a managed service removes the operational overhead of backups, connection pooling, and scaling. Try Tiger Cloud free to see how TimescaleDB handles the infrastructure so you can focus on your data.

Frequently asked questions

What is the difference between \l and \l+ in psql?

\l lists all databases with their names, owners, encodings, and collation settings. \l+ adds extra columns showing the tablespace, size, and description (comments) for each database. Use \l+ when you need to compare database sizes or check which tablespace a database is using.

How do you create a read-only user in PostgreSQL?

Create the user with CREATE USER readonly_user WITH PASSWORD 'yourpassword';, then grant connection access with GRANT CONNECT ON DATABASE mydb TO readonly_user;, and grant schema usage and select privileges with GRANT USAGE ON SCHEMA public TO readonly_user; followed by GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;. This gives the user read access without write permissions. Note that this grants access only to tables that exist at the time you run it. To automatically extend read access to tables created in the future, also run ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;.

What is the difference between VACUUM and ANALYZE in PostgreSQL?

VACUUM reclaims storage occupied by dead row versions left behind by updates and deletes. ANALYZE collects statistics about table contents and stores them in pg_statistic so the query planner can choose efficient execution plans. Running VACUUM ANALYZE together handles both tasks in a single pass.

How do you check which queries are currently running in PostgreSQL?

Query the pg_stat_activity view with SELECT pid, usename, state, query FROM pg_stat_activity WHERE state = 'active';. This returns the process ID, username, session state, and the SQL text of every active query. You can then cancel a specific query using SELECT pg_cancel_backend(pid); with the relevant process ID.

What is the difference between a primary key and a unique constraint in PostgreSQL?

A primary key enforces uniqueness and also creates a NOT NULL constraint on the column, and each table can have only one primary key. A unique constraint enforces uniqueness but allows null values (multiple nulls are permitted), and a table can have multiple unique constraints. Use a primary key for the main row identifier and unique constraints for other columns that must not repeat.

When should you use pg_dump versus pg_dumpall?

Use pg_dump to back up a single database, which gives you a portable file you can restore to any PostgreSQL server. Use pg_dumpall when you need to back up every database in a cluster along with global objects such as roles and tablespaces. For most application backups, pg_dump is the right choice.

How do you find the largest tables in a PostgreSQL database?

Query pg_class joined with pg_namespace and use pg_size_pretty(pg_relation_size(C.oid)) to format the sizes. Filter out pg_catalog and information_schema to exclude system objects, then order by pg_relation_size(C.oid) DESC and limit to the top results. The monitoring section of this cheat sheet includes the full query.

What does the IMMUTABLE keyword mean in a PostgreSQL function?

IMMUTABLE tells PostgreSQL that the function always returns the same result for the same input values and never modifies the database. The query planner can use this guarantee to pre-evaluate the function at planning time rather than executing it once per row, which can significantly speed up queries that call the function repeatedly.

How do you safely update records in PostgreSQL without risking data loss?

Wrap your UPDATE statement in an explicit transaction by issuing BEGIN before the update and COMMIT after you've verified the result. If something looks wrong, issue ROLLBACK instead of COMMIT to undo all changes made since BEGIN. This pattern prevents partial updates from being committed to the database.

Postgres Cheat Sheet

By Tiger Data Team

Updated at Sep 8, 2026

Postgres Cheat Sheet

Databases

List PostgreSQL databases

\l

List all databases using \l (or \list) in psql.

\l+

List all databases using \l+ with more details, including description, tablespace, and DB size, in psql.

Help on CREATE DATABASE command syntax

\h CREATE DATABASE

Display help on SQL command syntax (for example, CREATE DATABASE) in psql.

Create database

CREATE DATABASE mytest;

Creates a new database called mytest. By default, the owner is the current login user.

\c test
You are now connected to database "test" as user "postgres".

Connect to a PostgreSQL database called test as the postgres user in psql.

Tables

Table inspection in psql uses \d and its variants to show structure, constraints, and disk size. You can also export data to CSV, check indexes, and add comments directly from the terminal.

Show table

\d TABLE_NAME

Show table definition including indexes, constraints, and triggers in psql.

Show details

\d+ TABLE_NAME

Show a more detailed table definition including description (comments) and physical disk size in psql.

List tables from current schema

\dt

List tables from the current schema in psql.

List tables from all schemas

\dt *.*

List tables from all schemas in psql.

List tables for a schema

\dt <name-of-schema>.*

List the tables in a specific schema in psql.

Copy table data to CSV file

\copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV

Export a table as CSV in psql to the current directory.

Check indexes for a table using SQL

SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND
schemaname='__schema_name__';

Show table indexes using SQL.

Collect statistics about table contents

ANALYZE [__table_name__]

Add a comment on a table or column

COMMENT ON TABLE employee IS 'Stores employee records';

Add a comment on a table using SQL.

COMMENT ON COLUMN employee.emp_ssn IS 'Employee Social Security Number';

Add a comment on a column using SQL.

Approximate table row count

SELECT reltuples AS card FROM pg_class WHERE relname = '<table_name>';

Use this for fast (but not exact) counts from tables. It's helpful when a table has millions or billions of records and you want an estimated row count quickly.

Connecting

$ ssh -l postgres 200.34.22.75
postgres@200.34.22.75's password:
Linux localhost 5.10.0-28-amd64  #1 SMP Debian 5.10.209-2 (2024-01-31) x86_64

root@localhost:~# su - postgres

Learn how to test your PostgreSQL connection.

Enter PostgreSQL terminal

postgres@localhost:~$ psql
psql (17.2)
Type "help" for help.
postgres=#

Enter the PostgreSQL command line via the psql client.

Connect to a database

\c test

You are now connected to database "test" as user "postgres".

Connect to a PostgreSQL database called test as the postgres user in psql.

Check psql client version

$ psql -V
psql (PostgreSQL) 17.2

Check the psql client version.

Check Postgres server version

select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

Check the Postgres server version using SQL.

Queries

PostgreSQL queries cover table creation, data insertion, conditional selects, and safe updates wrapped in transactions. The examples below use a simple employee table to demonstrate each pattern.

Create a new table

CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,        -- AUTO_INCREMENT integer, as primary key
  emp_name VARCHAR(50) NOT NULL,
  emp_salary NUMERIC(9,2) NOT NULL
);

Creates a new table using SQL.

Display table

\d employee
                                    Table "public.employee"
Column      |         Type          |                         Modifiers
------------+-----------------------+-----------------------------------------------------------
emp_id     | integer               | not null default nextval('employee_emp_id_seq'::regclass)
emp_name   | character varying(50) | not null
emp_salary | numeric(9,2)          | not null

Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)

Display a table definition in psql.

Insert query

INSERT INTO employee (emp_name, emp_salary) VALUES
('John', 5000),
('Jack', 4568.0),
('Robert',7500.50);

Insert records into a table using SQL.

Conditional select query

select * from employee where emp_salary >= 5000;
 emp_id | emp_name | emp_salary
--------+----------+------------
    1   | John     |    5000.00
    3   | Robert   |    7500.50
(2 rows)

Select data based on a filter condition (for example, emp_salary >= 5000) using SQL.

Conditional update query (safe update)

BEGIN;
  UPDATE employee SET emp_salary = 6000 WHERE emp_name = 'John';
COMMIT;

Update a record based on a condition (for example, update emp_salary for employee John) using SQL.

Records aren't committed to the database unless you issue a COMMIT. You can undo updates by issuing ROLLBACK instead of COMMIT.

Functions

Create a new function

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Create a function to add two integers using SQL.

This function takes two integers as parameters. IMMUTABLE means the function can't modify the database and always returns the same result when given the same argument values.

Calling a function

select add(5,9);
 add
-----
  14
(1 row)

Call a function using SQL.

List functions

\df
                        List of functions
 Schema | Name | Result data type | Argument data types |  Type
--------+------+------------------+---------------------+--------
 public | add  | integer          | integer, integer    | normal
(1 row)

Display all functions in psql.

\df+

Display all functions with additional information, including owner, source code, and description, in psql.

Edit a function

\ef <function_name>

Edit a function in the default editor in psql.

Views

Listing PostgreSQL views helps you understand database structure, optimize queries, analyze dependencies, document schemas, and maintain security and access control.

List views

\dv

List views from the current schema in psql.

\dv *.*

List views from all schemas in psql.

Users

Set or reset a Postgres user password

\password username

Set or reset a password for a PostgreSQL database user in psql.

For example, to change the password for the current user postgres:

\password postgres
Enter new password: xxxx
Enter it again: xxxx

Show all users

select * from pg_user;

Display PostgreSQL database users using SQL.

\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 testrole  |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Display PostgreSQL database roles in psql.

$ psql -U testuser mytest
Password for user testuser: ......
psql (17.2)
Type "help" for help.

Indexes

Indexes in PostgreSQL speed up query execution by letting the planner avoid full table scans. You can create, inspect, and drop indexes using a combination of SQL statements and psql meta-commands.

Create a new index on a table

create index idx_employee_emp_name on employee using btree (emp_name asc);

Create a new index on the emp_name column of the employee table using SQL.

This index specifies btree as the index method and uses asc to store the index key column data in ascending order.

View indexes of a table

\d employee
postgres=# \d employee;
                                    Table "public.employee"
   Column   |         Type          |                         Modifiers
------------+-----------------------+-----------------------------------------------------------
 emp_id     | integer               | not null default nextval('employee_emp_id_seq'::regclass)
 emp_name   | character varying(50) | not null
 emp_salary | numeric(9,2)          | not null
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
    "idx_employee_emp_name" btree (emp_name)

List indexes of a table along with the table definition in psql.

List all indexes

\di
                      List of relations
 Schema |         Name          | Type  |  Owner   |  Table
--------+-----------------------+-------+----------+----------
 public | employee_pkey         | index | postgres | employee
 public | idx_employee_emp_name | index | postgres | employee
(2 rows)

List all indexes from all tables in psql.

Drop an index from a table

drop index idx_employee_emp_name;

Drop an existing index from a table using SQL.

List installed extensions

\dx

List all installed PostgreSQL extensions in psql.

\dx+

List extensions with additional detail in psql.

Constraints

Create a table with primary and unique constraints

CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,
  emp_name VARCHAR(50) NOT NULL,
  emp_ssn VARCHAR (30) NOT NULL UNIQUE,
  emp_salary NUMERIC(9,2) NOT NULL
);

Creates a new table with primary and unique key constraints using SQL.

Avoid duplicate records

INSERT INTO employee (emp_name, emp_ssn, emp_salary) values ('Rohit', '1234', 5000.0);
INSERT 0 1

INSERT INTO employee (emp_name, emp_ssn, emp_salary) values ('Mason', '1234', 7500.0);

ERROR:  duplicate key value violates unique constraint "employee_emp_ssn_key"
DETAIL:  Key (emp_ssn)=(1234) already exists.

Insert records into a table with unique key constraints specified using SQL.

This table uses emp_id as the primary key column and a unique constraint on emp_ssn to prevent duplicate social security numbers from being entered.

Create a table with a check constraint

CREATE TABLE orders(
  ord_no integer,
  ord_date date,
  ord_qty numeric,
  ord_amount numeric CHECK (ord_amount>0)
);

Creates a new table with a check constraint specified using SQL.

insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (1, '2019-08-29', 1, 10);
INSERT 0 1

insert into orders(ord_no, ord_date, ord_qty, ord_amount) values (2, '2019-08-29', 1, 0);
ERROR:  new row for relation "orders" violates check constraint "orders_ord_amount_check"
DETAIL:  Failing row contains (2, 2019-08-29, 1, 0).

Insert records into a table with check constraints specified using SQL.

Define a relation between two tables using a foreign key constraint

CREATE TABLE IF NOT EXISTS department (
  dept_id SERIAL PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS employee (
  emp_id SERIAL PRIMARY KEY,
  emp_name VARCHAR(50) NOT NULL,
  emp_ssn VARCHAR (30) NOT NULL UNIQUE,
  emp_salary NUMERIC(9,2) NOT NULL,
  emp_dept_id INTEGER REFERENCES department (dept_id)    -- Foreign Key
);

Creates the department and employee tables and defines a relation between an employee and a department using a foreign key (REFERENCES) in SQL.

A Foreign Key Constraint establishes a link between data in two tables, enforcing referential integrity by preserving the relationships between the linked tables.

Check constraints on a table

\d employee;
                                     Table "public.employee"
   Column    |         Type          |                         Modifiers
-------------+-----------------------+-----------------------------------------------------------
 emp_id      | integer               | not null default nextval('employee_emp_id_seq'::regclass)
. . .
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
    "employee_emp_ssn_key" UNIQUE CONSTRAINT, btree (emp_ssn)
Foreign-key constraints:
    "employee_emp_dept_id_fkey" FOREIGN KEY (emp_dept_id) REFERENCES department(dept_id)

Display constraints on a table using the \d option in psql.

Identifiers

PostgreSQL provides a rich set of operators and functions for working with strings and numbers. The examples below cover concatenation, math operators, string manipulation, and quoting conventions.

String concatenate operator

select 'Gordon' || ' ' || 'Moore' As fullName;
   fullname
--------------
 Gordon Moore
(1 row)

Concatenates two or more strings using ||. This operator works on table columns as well, for example select first_name || ' ' || last_name As fullName from person.

Square and cube root operators

select |/25 As sqrt;
 sqrt
------
   5
(1 row)

Square root operator.

select ||/125 As cubert;
 cubert
------
   5
(1 row)

Cube root operator.

Factorial function

select factorial(5) As factorial;
 factorial
-----------
    120
(1 row)

The factorial(n) function replaced the deprecated ! postfix operator, which was removed in PostgreSQL 14.

Binary complement operator

select ~60 As compl;
  compl
----------
   -61
(1 row)

Binary 2's complement. This operator flips bits, so if A = 60 (binary 0011 1100), then ~A = 1100 0011.

String lower and upper functions

select lower('Rohit Kumawat') As lowerCase, upper('Rohit Kumawat') As upperCase;
   lowercase   |   uppercase
---------------+---------------
 rohit kumawat | ROHIT KUMAWAT
(1 row)

Postgres lower() and upper() functions using SQL.

Number of characters in a string

select char_length('Arizona') as num_chars;
 num_chars
-----------
     7
(1 row)

Count the number of characters in a string using SQL.

Location of a specified substring

select position('pan' in 'japan') As pos;
 pos
-----
  3
(1 row)

Find the location of a specified substring using SQL (uses 1-based indexing).

Extract a substring

select substring('postgres' from 3 for 3) As sub_string;
 sub_string
------------
   stg
(1 row)

Extract a substring from postgres starting at the third character for a length of three characters using SQL.

Insert a newline in SQL output

postgres=# select 'line 1'||E'\n'||'line 2' As newline;
 newline
---------
 line 1 +
 line 2
(1 row)

Quote identifier

UPDATE "my table" SET "a&b" = 0;

Dollar-quoted string constant

select $$Maria's dogs$$ As col;
 col
--------------------
 Maria's dogs
(1 row)

Use dollar-quoted string constants instead of single quotes when a string contains many single quotes or backslashes. PostgreSQL treats everything between the $$ delimiters as a literal string.

Toggle query timing

\timing

Toggle the display of how long each SQL statement takes to execute in psql. Useful for quick performance checks without running EXPLAIN.

Maintenance

Garbage collect (reclaim storage)

VACUUM [__Table__]

vacuum(verbose, analyze) employee;
INFO:  vacuuming "public.employee"
INFO:  scanned index "employee_pkey" to remove 1 row versions
. . .
sample, 1 estimated total rows
VACUUM

Use the VACUUM command to reclaim storage from deleted rows in the employee table using SQL.

Gather statistics

ANALYZE [__table__]

analyze verbose employee;
INFO:  analyzing "public.employee"
INFO:  "employee": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
ANALYZE

Analyze a table and store the results in the pg_statistic system catalog using SQL.

Monitoring

Monitoring active PostgreSQL sessions helps you identify long-running queries, idle connections, and blocking processes before they affect application performance.

Session monitor

SELECT
    pid,
    datname,
    usename,
    application_name,
    client_hostname,
    state,
    client_port,
    backend_start,
    query_start,
    query
FROM pg_stat_activity;

Monitor Postgres sessions using SQL.

Key columns to know:

pid: The backend process ID
datname: The database name
usename: The user running the query
application_name: The client application name
state: The state of the session (active, waiting, idle, and so on)
query: The query being executed

Cancel a running query

SELECT pg_cancel_backend(pid);

Cancel a running query by providing its pid. This is useful for terminating long-running queries using SQL.

Biggest PostgreSQL tables and indexes by size

SELECT
  nspname || '.' || relname AS "Object Name", relkind As "Object Type",
  pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;

List the top 20 largest tables and indexes, excluding catalog tables, using SQL.

Backup

Database backup with default options

$ pg_dump mydb > mydb.bak.sql

Create a backup for a database called mydb in a plain-text SQL script file (mydb.bak.sql) using pg_dump.

Backups in TimescaleDB are fully automated. Learn how TimescaleDB handles database backups and disaster recovery.

Database backup with customized options

$ pg_dump -c -C -F p -f mydb.bak.sql mydb

Creates a backup for a database called mydb in plain text format with drop and create database commands included in the output file mydb.bak.sql using pg_dump.

Backup options:

-c/ --clean: output commands to clean (drop) database objects before writing commands to create them
-C/--create: begin output with a CREATE DATABASE command and reconnect to the created database
-F: format of the output (p means plain SQL output, c means custom archive format suitable for pg_restore)
-f: backup output file name

Remote backup

$ pg_dump -h <remote_host> -p <port> -U <user> -f mydb.bak mydb

Run pg_dump on the client computer to back up data on a remote Postgres server. Use -h to specify the IP address of your remote host and -p to identify the port on which PostgreSQL is listening.

All databases backup

$ pg_dumpall > alldb.bak.sql

Back up all databases along with database roles and cluster-wide information using pg_dumpall.

Restore

Restore from a backup file (.sql)

$ psql -U username -d db_name -f filename.sql

Restore from a custom archive backup file (.bak)

$ pg_restore -d db_name /path/to/your/file/db_name.bak -c -U db_user

We put together a guide to help you restore your PostgreSQL database.

Configuration

Stop, start, and restart the PostgreSQL service

service postgresql stop

Stop the PostgreSQL service through the root user on Linux.

service postgresql start

Start the PostgreSQL service through the root user on Linux.

service postgresql restart

Restart the PostgreSQL service through the root user on Linux.

Display configuration parameters

show all;

List all current runtime configuration parameters in psql.

Display configuration parameters using SQL

select * from pg_settings;

List all current runtime configuration parameters using SQL, with additional details including descriptions.

Show the current setting for max_connections

SELECT current_setting('max_connections');
current_setting
-----------------
 100
(1 row)

Display the current value set for the max_connections parameter using SQL.

Show the PostgreSQL config file location

show config_file;
config_file
------------------------------------------
 /etc/postgresql/17/main/postgresql.conf
(1 row)

Show the PostgreSQL configuration file location in psql.

The PostgreSQL configuration files are stored in the directory shown by the command above. The main configuration file is postgresql.conf. Find out how to connect using pg_service.conf.

Display contents of the Postgres config file

postgres@localhost:~$ less /etc/postgresql/17/main/postgresql.conf

 . . . .
data_directory = '/var/lib/postgresql/17/main'         # use data in another directory

hba_file = '/etc/postgresql/17/main/pg_hba.conf' # host-based authentication file

ident_file = '/etc/postgresql/17/main/pg_ident.conf' # ident configuration file

listen_addresses = '*'                  # what IP address(es) to listen on,
                                        # comma-separated list of addresses,
                                        # defaults to 'localhost', use '*' for all
port = 5432
. . . .

Key directives in postgresql.conf:

data_directory, which tells Postgres where the database files are stored
hba_file, which points to the host-based authentication file
port, which sets the TCP port number (default is 5432)

Keep your Postgres skills sharp

Frequently asked questions

What is the difference between \l and \l+ in psql?

How do you create a read-only user in PostgreSQL?

What is the difference between VACUUM and ANALYZE in PostgreSQL?

How do you check which queries are currently running in PostgreSQL?

What is the difference between a primary key and a unique constraint in PostgreSQL?

When should you use pg_dump versus pg_dumpall?

How do you find the largest tables in a PostgreSQL database?

What does the IMMUTABLE keyword mean in a PostgreSQL function?

How do you safely update records in PostgreSQL without risking data loss?

Postgres Cheat Sheet

Postgres Cheat Sheet

Databases

List PostgreSQL databases

Help on CREATE DATABASE command syntax

Create database

Tables

Show table

Show details

List tables from current schema

List tables from all schemas

List tables for a schema

Copy table data to CSV file

Check indexes for a table using SQL

Collect statistics about table contents

Add a comment on a table or column

Approximate table row count

Connecting

Login using PostgreSQL user

Enter PostgreSQL terminal

Connect to a database

Check psql client version

Check Postgres server version

Queries

Create a new table

Display table

Insert query

Conditional select query

Conditional update query (safe update)

Functions

Create a new function

Calling a function

List functions

Edit a function

Views

List views

Users

Set or reset a Postgres user password

Show all users

Login and enter the PostgreSQL terminal

Indexes

Create a new index on a table

View indexes of a table

List all indexes

Drop an index from a table

List installed extensions

Constraints

Create a table with primary and unique constraints

Avoid duplicate records

Create a table with a check constraint

Define a relation between two tables using a foreign key constraint

Check constraints on a table

Identifiers

String concatenate operator

Square and cube root operators

Factorial function

Binary complement operator

String lower and upper functions

Number of characters in a string

Location of a specified substring

Extract a substring

Insert a newline in SQL output

Quote identifier

Dollar-quoted string constant

Toggle query timing

Maintenance

Garbage collect (reclaim storage)

Gather statistics

Monitoring

Session monitor

Cancel a running query

Biggest PostgreSQL tables and indexes by size

Backup

Database backup with default options

Database backup with customized options

Remote backup

All databases backup

Restore

Restore from a backup file (.sql)

Restore from a custom archive backup file (.bak)