Skip to content

Instantly share code, notes, and snippets.

@edisonchee

edisonchee/psql_setup.md

Last active Apr 22, 2020
Embed
What would you like to do?
pgsql setup

POSTGRES Setup OS X

brew install postgres

# create user
/usr/local/opt/postgres/bin/createuser -s postgres

Get into psql

psql postgres

Change password

\password postgres

Add user

CREATE ROLE user WITH LOGIN PASSWORD 'password'; 

Alter role

ALTER ROLE user CREATEDB; 

Log into postgres db with user user. First argument is name of db.

psql postgres -U user

Create db

CREATE DATABASE databasename;

Assign privileges

GRANT ALL PRIVILEGES ON DATABASE databasename TO user

Connect to db

\c databasename

PGSQL Commands

Listing

  • \l: lists databases
  • \du: lists all users
  • \connect: connect to a specific database
  • \dt: list tables in connected database
  • \q: exit shell

Granting privileges

Granting all privileges for a user on a specific db:

GRANT ALL PRIVILEGES ON DATABASE db_name TO user;

Other operations

DELETE FROM <table_name> WHERE <column_name> = 'something';
ALTER SEQUENCE <table name>_<column_name>_seq RESTART WITH 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.