All Purpose Postgres Queries and Commands

A running list of all-purpose, Postgres-specific queries and CLI commands for general data analysis and organization. Most of these are designed for Postgres' psql interface.

Make a local copy of the database

First, create a new database on your machine using createdb db_name. Then:

pg_dump -h host1 dbname | psql -h host2 dbname

Reference.

Print all table names in a database

SELECT table_name FROM information_schema.tables WHERE table_schema ='public';

You can also use \dt, but this prints out extra columns.

Print all column names in a table

SELECT column_name FROM information_schema.columns WHERE table_name='YOUR TABLE NAME HERE';

You can also use \d + [TABLE NAME], but this prints out extra columns.

Print the data types for each column in a table

SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema='public';

You can also change the public value to be whatever you want.

View all Postgres commands

\?

Disable paging (print full query output)

\pset pager off

Configure default startup file

Postgres reads and executes commands from this file after it connects to the database, but before accepting normal commands. Add commands to it to specify your default preferences. The global file is located in the system configuration directory, which can be found by running:

pg_config --sysconfdir

On a Mac, this should point you to a directory named postgresql. If no directory exists with this name, create it, cd to it, and create a text file named psqlrc. See the psql manpage for more info.