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