psql Cheatsheet

psql_cheatsheet.md

psql Cheatsheet

Cheatsheet for psql, the command line client for PostgreSQL.

Launch

Do:

# Interactive
PGPASSWORD=thepassword psql -h localhost -p 5432 -U postgres -d postgres

# Launch a SQL script
PGPASSWORD=thepassword psql -h localhost -p 5432 -U postgres -d postgres -f 010-db-set-up.sql

Metacommands

Make your like easier.

Command	Usage
! command	Issue a shell command to the underlying shell.
!	Opens a shell session. exit returns to the psql prompt.
\cd	Change working directory.
\i file	Executes script file.
\o file	Outputs results to file. Use \qecho to annotate. \o returns to the standard output.
\o /dev/null	Cancel all outputs.
\o	Restore normal output.

\copy from CSV

CSV from and to a table:

-- From a table to CSV file
\copy madrid.madrid_clientes from '/ext-src/data/010-in/madrid-clientes.csv' with csv header

-- From CSV file to a table
\copy madrid.madrid_clientes to '/ext-src/data/010-in/madrid-clientes.csv' with csv header

Run a Command from the Command Line

Do:

PGPASSWORD=$PG_PASS psql \
  -d $PG_DB \
  -h $PG_HOST \
  -p $PG_PORT \
  -U $PG_USER \
  -c "create schema area_lidar_costa_import_temp"

Run a Script from the Command Line

Do:

PGPASSWORD=$PG_PASS psql \
 -d $PG_DB \
 -h $PG_HOST \
 -p $PG_PORT \
 -U $PG_USER \
 -f scripts/020-transform.sql