@rnattochdagWriting

til/useful postgres commands

Useful Postgres commands and queries that I tend forget.

PSQL

  • \c databaseName - Connect to a database
  • \dt - List all tables (relations)
  • \l - List all databases

Queries

-- Create a comma separated list of all values of an enum
SELECT ENUM_RANGE(NULL::enum_name)
-- Update a table column with the row_numbebk:r()
-- with the table in a specific sort order.
-- Useful for updating sequential values.
UPDATE the_table SET id = col_serial
FROM (
	-- Get the id and row_number with the table
	-- sorted by created_at in this case
	SELECT id, row_number() OVER (ORDER BY created_at) as col_serial
	FROM the_table ORDER BY created_at
) AS t
WHERE the_table.id = t.id;

Command line

# Standard database dump
pg_dump -h host -U user databasename > filename.sql

# Only export the data, not the database structure
pg_dump -h host -U user --data-only databasename > filename.sql

# Create a tar archive
pg_dump -h host -U user -F t databasename > filename.tar