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 #

1-- Create a comma separated list of all values of an enum
2SELECT ENUM_RANGE(NULL::enum_name)

 1-- Update a table column with the row_numbebk:r()
 2-- with the table in a specific sort order.
 3-- Useful for updating sequential values.
 4UPDATE the_table SET id = col_serial
 5FROM (
 6	-- Get the id and row_number with the table
 7	-- sorted by created_at in this case
 8	SELECT id, row_number() OVER (ORDER BY created_at) as col_serial
 9	FROM the_table ORDER BY created_at
10) AS t
11WHERE the_table.id = t.id;

1-- Getting the name of a month from a date
2TO_CHAR(now(), 'month') -- june
3TO_CHAR(now(), 'Month') -- June
4TO_CHAR(now(), 'MONTH') -- JUNE
5TO_CHAR(now(), 'mon') -- jun
6TO_CHAR(now(), 'Mon') -- Jun
7TO_CHAR(now(), 'MON') -- JUN

Command line #

1# Standard database dump
2pg_dump -h host -U user databasename > filename.sql
3
4# Only export the data, not the database structure
5pg_dump -h host -U user --data-only databasename > filename.sql
6
7# Create a tar archive
8pg_dump -h host -U user -F t databasename > filename.tar