CLI commands I seem to use sometimes & forget sometimes...

Postgres Cheatsheet

# connect to db
sudo -u postgres psql postgres

# export table as CSV
COPY table_name TO '/tmp/users_export.csv' DELIMITER ',' CSV HEADER;

# create db
CREATE DATABASE <name>;

# create role
CREATE ROLE <username> WITH LOGIN ENCRYPTED PASSWORD '<password>' CREATEDB;

# change bool or other
ALTER TABLE <table> ALTER <column> TYPE boolean USING (<column>::boolean);

# change smallint to bool
ALTER TABLE <table> ALTER <column> TYPE boolean USING (<column>::int::boolean);

# alter role
"/Applications/Postgres.app/Contents/Versions/9.6/bin/psql" -p5432 -d "database" "user"
ALTER USER myuser WITH SUPERUSER;

# drop/create schema
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
GRANT ALL ON SCHEMA public TO anyuser;

# reset pk/id sequence
SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);