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: 28 February 2026

interactive

Start interactive session

psql -U the_user

Show all databases in the current server

\l+

non-interactive

Run SQL command e.g. restore database backup from SQL in a file

psql -U username -d dbname -f backupfile.sql

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