useful sql things

postgres_faq.sql

-- Substring select (1-indexed) --
SELECT SUBSTRING(column1, 1, 5) as column1_first_5_chars FROM my_table;
-- String concat --
SELECT (col1 || col2 || col3) as first_three_cols FROM my_table;

-- Create a temp table --
SELECT column INTO TEMPORARY new_table FROM my_table;
-- Retroactively add an autoincrementing primary key --
ALTER TABLE new_table ADD COLUMN id SERIAL PRIMARY KEY;

-- Locate pg_hba.conf (must be root user) --
SHOW hba_file;

POSTGRESQL.md

Dependencies

brew install postgresql

First time setting up local Postgres

Here's a nice readable tutorial on creating local Postgres users and DBs.

Create a user myuser with permissions to administrate a database mydatabase:

psql postgres
# Inside psql shell as `postgres` user
CREATE ROLE myuser WITH LOGIN PASSWORD 'my_super_secret_password';
ALTER ROLE myuser CREATEDB;
\q
# Go back into psql shell as `myuser` user
psql postgres -U myuser
# In psql shell as `myuser` user
CREATE DATABASE mydatabase;
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;
\q

You can now shell into that DB with psql -U myuser -d mydatabase.

Passwordless login

If you don't already have passwordless login and want it, you can set your pg_hba.conf to trust a user (or all users) on localhost. https://www.postgresql.org/docs/14/auth-trust.html

Frequently used CLI commands

Run SQL commands from a file

# The -a flag prints the SQL lines to stdout
psql -U myuser -d mydatabase -a -f commands.sql

Dump & load

Dump DB to file without explicitly setting ownership:

pg_dump mydatabase --no-owner > dumpfile.postgres

Load a DB dump:

psql -U myuser -d mydatabase < dumpfile.postgres

psql quickref

(You can also see the full list by entering \? in psql shell, but some are more frequently used than others.)

\q - quit
\d - list tables, views, and sequences
\du - list roles
\dt - list tables
\l - list databases