Postgres cheat sheet

# Postgres cheat sheet

Note on prompts: 
$  The command is run from the unix shell
#  The command is run from psql, logged in as the default user.
=> The command is run from psql, logged in as a specific user.


Make sure that psql will auto start
$ pg_ctl -D /usr/local/var/postgres start && brew services start postgresql

See users in system
# \du

Create user and set password
# CREATE ROLE henfo WITH LOGIN PASSWORD 'password';

Set user permissions
# ALTER ROLE henfo CREATEDB;

sign in
$ psql postgres -U henfo

create database
=> CREATE DATABASE my_test_app;

Set Db priveliges
=> GRANT ALL PRIVELIGES ON DATABASE my_test_app TO henfo;

List all database
=> \l

Connect to a dataabse
=>  \c my_test_app

List tables in database (must be connected to it)
=> \dt

See details of table
=> \d <table-name> 

Exit database 
=> \q

See current user
=> SELECT current_user

See current database
=> SELECT current_database()

Drop database
=> DROP DATABASE "bookstore";

Create table
=> CREATE TABLE Posts(
  ID SERIAL PRIMARY KEY,
  TITLE CHAR(50) NOT NULL,
  TEXT TEXT NOT NULL);
  
Insert
=> INSERT INTO Posts(id, title, text) VALUES(1, 'Hello World', 'Lorem ipsum');
Note: Must be single quotes!