postgres cheat / psql cheat

postgres.md

create user: CREATE ROLE username WITH LOGIN PASSWORD 'password';

show users: \du

create database: CREATE DATABASE databasename;

show databases: \l

use a specific db: \c dbnamehere;

give user permissions: GRANT ALL PRIVILEGES ON DATABASE super_awesome_database TO johndoe;

give user ownership of db: ALTER DATABASE db_name OWNER TO new_owner_username;

change table owner: ALTER TABLE <tablename> OWNER TO <username>

show tables: \dt

describe table: \d <table name>

insert (use single quote for strings):

insert into mm_thoughts_contexts (t_id, mm_id) VALUES
(41, 289),
(5, 289),
(35, 289);

read only user:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;

join example:

SELECT
	users_table.first_name, 
	users_table.last_name,
	people_sessions.session_status 

FROM
users_table

LEFT JOIN
some_appointments on users_table.id = some_appointments.people_id 

INNER JOIN
people_sessions on some_appointments.id = people_sessions.some_appointments_id

WHERE
people_sessions.session_status = 'WINNING';

Knex migrations example:

export NODE_ENV=name_of_local_dev_env ; npx knex migrate:latest --knexfile=./path/to/knexfile.ts