Tables

Show table

\d TABLE_NAME

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

Show details

\d+ TABLE_NAME

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

List tables from current schema

\dt

// List tables from current schema (psql)

List tables from all schemas

\dt *.*

// List tables from all schemas (psql)

List tables for a schema

\dt <name-of-schema>.*

// 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

// 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__';

// Show table indexes (SQL)

Collects statistics about the contents of tables

ANALYZE [__table__]

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

With no parameter, ANALYZE examines every table in the current database

Adding comment on table/column

Comment on table employee is 'Stores employee records';

// Comment on table (SQL)

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

// Comment on column (SQL)

Approximate table row count / table cardinality

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

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