Postgres CheatSheet

Cheatsheet containing Postgres Commands you need, Missing Anything? Kindly add a response or comment will add that 😄.

Show table

Show table definition including indexes, constraints & triggers (psql)

\d TABLE_NAME

Describe a Table

More detailed table definition including description and physical disk size (psql)

\d+

List tables from current schema

List tables from the current schema (psql)

\dt 

List tables from all schemas

List tables from all schemas (psql)

\dt *.*

List tables for a schema

List the tables in a specific schema (psql)

\dt <name-of-schema>.*

Copy table data to CSV file

Export a table as CSV (psql)

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

Check indexes for a table using sql

Show table indexes (SQL)

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

Collects statistics about the contents of tables

Analyze a table and store the results in the pg_statistic system catalog (SQL)

ANALYZE [__table__]

Adding comment on table/column

Comment on table (SQL)

Comment on table employee is 'Stores employee records';

Comment on column (SQL)

Comment on column employee.ssn is 'Employee Social Security Number';

Approximate Table Row count / Table Cardinality

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

Check Max Connections [1] [2] [3]

SHOW max_connections;

Get the Current Number of Connections [1]

SELECT sum(numbackends) FROM pg_stat_database;

Explain Code [1] [2] [3]

EXPLAIN SELECT * FROM post LIMIT 50;

Count Total Rows in a Table [1] [2]

SELECT count(*) AS exact_count FROM myschema.mytable;

Get Sizes of all databases [1]

\l+

Get the Size of a Single Database

\l+ <database_name>

More Reading

Efficient Use of PostgreSQL Indexes

ActiveRecord: size vs count