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_number()
-- 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;

-- Getting the name of a month from a date
TO_CHAR(now(), 'month') -- june
TO_CHAR(now(), 'Month') -- June
TO_CHAR(now(), 'MONTH') -- JUNE
TO_CHAR(now(), 'mon') -- jun
TO_CHAR(now(), 'Mon') -- Jun
TO_CHAR(now(), 'MON') -- JUN

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