Connecting for the first time

sudo -u postgres psql

Set the password

\password postgres

Logging in

psql -h localhost -p <port no> -U <username> <database name>

Commands list

  • \h help on SQL commands
  • \? help on psql commands
  • \l list databases
  • \c "database name" connect to a database
  • \d list of tables
  • \d "table name" schema of table name
  • \du list roles
  • \e edit in editor
  • \connect "database name" connect to a database

 

Create Database

CREATE DATABASE dbname OWNER ownername;

Create User

CREATE USER username WITH PASSWORD 'mypass'

Delete User

DROP USER username

Drop Table

DROP TABLE <TABLE NAME>

Drop Index

DROP INDEX <INDEX NAME>

Drop Column

ALTER TABLE <TABLE NAME> DROP COLUMN <COLUMN NAME>

Rename Database

ALTER DATABASE name RENAME TO new_name

Change Database Owner

ALTER DATABASE name OWNER TO new_owner

Grant all privileges to a user on a Database

GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;

Make Superuser

ALTER USER <username> WITH SUPERUSER;