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;