Official Docs:

• Server Setup and Operation
• create table

Differences beteween PostgreSQL-8 and Redshift ​

• a lot, but a nota-bene about one factor I haven't seen any docs on: Redshift varchar lengths are the maximum actual bytes, not a count of utf8 code points -- so varchar(10) can't hold 10 chars in utf8... unless they're all from the ASCII subset.

bash ​

psql [dbname] [username] -cSQL (both db and user names default to unix username)

If I've a DB called xander, I can get the disk size: psql -c "SELECT pg_size_pretty( pg_database_size('xander'));"

For a table: psql -c "SELECT pg_size_pretty( pg_total_relation_size('schema.table'));"

init ​

On my ubuntu20 run, I had to sudo -i -u postgres to auth the next step:

username=xander
dbname=xander
createuser -s $username # -s means create a psql admin
createdb -O $username $dbname # create a db with that person as owner

TODO: add homebrew variant to this doc.

1. ensure connectivity psql postgres --username=$USER -c'SELECT version();' (I'm overriding --username by CLI back to default as I tend to have a PGUSER set)
   • or use the list of DBs as a test psql --username=$USER -dtemplate1 -c "\l+"|cat
2. create my go-to nickname for root while using my brew-factory-default root account: createuser -s $PGUSER -U$USER
3. test! psql "-c\pset null '<null>'" -c '\l+'|cat (well that was easy; no password was needed. good enough for a dev box, and what else would a macbook using brew be?)

creating a password-based user ​

• psql -c "CREATE USER $name PASSWORD '$pass' $superpowers"
• if you're creating an admin:
  • on PostgreSQL, SUPERUSER
  • on Redshift, CREATEUSER CREATEDB
• if not, you can put them in groups: IN GROUP groopfoo, groupbar
• if the password should expire, put VALID UNTIL 2000-12-31 before any group clause.
  • (TESTME: I've been using fully specified 2011-10-05T14:48:00.000Z but I bet date is sufficient...)
• if you want to set a custom search path, append a second statement with a semicolon: ; ALTER USER $name SET search_path TO schema1, schema2

So my go-to is:

pass=$(codeword --num); echo $pass
SQL="CREATE USER merlin PASSWORD '$pass' SUPERUSER" # on local brew psql, this still didn't require a password. this is really about EC2 devs or QA/prod mode.
psql -c "$SQL"
# and then I configure my client:
db psql psql localhost 5432 merlin $pass $dbname

list dbs ​

(still as postgres user here) psql -U postgres -l or the slightly richer (and TCP friendly) \l+ (which I've wired up as v show databases

(or using the new admin user, and letting -U default) psql -l

If you don't know of the right DB to connect to, and you're getting psql: error: FATAL: database "$a_user" does not exist, I use template1 so that my code works on Redshift as well. (psql -l is hardwired to assume db=postgres exists, and I don't use AWS drivers when psql standard ones work fine for my needs.)

psql -dtemplate1 -c "\l+"

list schemas ​

\dn+ (also in v show databases for legacy reasons)

create a schema ​

CREATE SCHEMA IF NOT EXISTS schema_name https://www.postgresql.org/docs/12/sql-createschema.html

list tables ​

psql -U adminuser -d <database_name>

secure? ​

how do I lock psql to localhost? is this the default? how do I do the opposite and support encrypted requests?

backups ​

how? https://www.a2hosting.com/kb/developer-corner/postgresql/postgresql-database-backups-using-cron-jobs

tear down a db ​

dropdb dbname

dropuser username (this one will block if that user is the owner of a DB)

fingerprint ​

psql -c'SELECT version();'

switch DBs ​

to switch in REPL mode: \c newdb

node ​

add a user to a DB ​

GRANT permissions ON DATABASE dbname TO username;

auto increment ID ​

In all my DBs, I'm partial to the primary key being an autoincrementer. the psql way to do that:

The Old Way

create table foo (id SERIAL PRIMARY KEY)

N.B. serial is just a macro and you can't mutate an existing column to be of type serial;

To retrofit this pattern to an existing table: https://www.postgresql.org/docs/8.1/sql-createsequence.html

CREATE SEQUENCE foo_id_seq;
ALTER TABLE foo ALTER id SET DEFAULT NEXTVAL('user_id_seq');

The New Way is much nicer! https://www.postgresqltutorial.com/postgresql-identity-column/ (a great so)

CREATE TABLE foo (
    id INT GENERATED ALWAYS AS IDENTITY,
    blahblah VARCHAR NOT NULL
);