Tables

Show table

\d TABLE_NAME
Copy

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

Show details

\d+ TABLE_NAME
Copy

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

List tables from current schema

\dt
Copy

// List tables from current schema (psql)

List tables from all schemas

\dt *.*
Copy

// List tables from all schemas (psql)

List tables for a schema

\dt <name-of-schema>.*
Copy

// List the tables in a specific schema (psql)

Copy table data to CSV file

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

// Export a table as CSV (psql)

Check indexes for a table using sql

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

// Show table indexes (SQL)

Collects statistics about the contents of tables

ANALYZE [__table__]
Copy

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

Adding comment on table/column

Comment on table employee is 'Stores employee records';

// Comment on table (SQL)
Copy

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

// Comment on column (SQL)
Copy

Approximate Table Row count / Table Cardinality

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

// Use this to do fast (but not exact) counts from tables. Helpful if table has millions / billions of records and you just want estimated rows quickly. (SQL)