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;
-- 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