PostgreSQL

Updated: 28 February 2026

Create a temporary table from a select statement

CREATE TEMP TABLE narrow_table AS SELECT foo, bar from broad_table;

Dump a table to csv format file

COPY book TO '/tmp/book_entries.csv' WITH (FORMAT CSV, HEADER);

Updated: 08 May 2026

interactive

Start interactive session

psql -U the_user

Show all databases in the current server

\l+

non-interactive

Run a SQL file from the command line

psql -U username -d dbname -f our-db-backup.sql

Run a SQL statement from the command line

psql -U username -d dbname -c 'SELECT 101'

Updated: 28 February 2026

Remove a PostgreSQL database called app as user app

dropdb -U app app

Create a PostgreSQL database

createdb dbname

Use pg_dump, as user app, to backup a database inside docker container pg

docker compose exec pg pg_dump -U app > claude_results.sql

Also see pg_restore