–quit from psql
q

–version
select version();

–Create PostgreSQL database

create database MYDB owner myowner;

–drop PostgreSQL database.BE CAREFUL USING THIS COMMAND – IT CANNOT BE REVERSED!! TAKE A BACKUP FIRST

drop database MYDB;

–list PostgreSQL databases
list
l

–list schemas in a database with psql

dn

–list schemas in database  with postgresql

select nspname from pg_catalog.pg_namespace;

–list tables
dt

–list all tables,views,sequences
dtvs the_schema.*

–list all servers on Postgres

des+

–list all extensions

dx

–create a schema
CREATE SCHEMA myschema;

–list all schemas in a databases
select nspname from pg_catalog.pg_namespace;

–list all tables in a particular schema
dt public.*

–columns in a table
d+ my_table_name

–change the schema for a table
ALTER TABLE myTable SET SCHEMA myschema;

–change the schema for a SEQUENCE
ALTER SEQUENCE mysequence SET SCHEMA schema1;

–change sequence name
ALTER TABLE old_seq_name RENAME TO new_seq_name;

–add column to existing table
ALTER TABLE distributors ADD COLUMN address varchar(30);

–drop table
DROP TABLE mytable;

–change table name
ALTER TABLE mytab RENAME TO mytab_renamed;

–change table name with schema
SET search_path TO domain;
ALTER TABLE new RENAME TO old;

–drop multiple columns on a table
ALTER TABLE table DROP COLUMN col1, DROP COLUMN col2;

–execute a sql in Postgres 9
psql -h localhost -d userstoreis -U admin -p 5432 -a -q -f /home/jobs/Desktop/resources/postgresql.sql
psql -U username -d myDataBase -a -f myInsertFile
-h PostgreSQL server IP address
-d database name
-U user name
-p port which PostgreSQL server is listening on
-f path to SQL script

–use database
c db_name

–create user and assign privileges on a database
CREATE USER youruser WITH ENCRYPTED PASSWORD ‘yourpass’;
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
GRANT SELECT, INSERT, UPDATE,DELETE PRIVILEGES ON DATABASE yourdbname TO youruser;

–list all users
du;

–change user password

ALTER USER user_name WITH PASSWORD ‘new_password’;

–backup a Postgres database  to a tar file i.e -F t   (tar)
pg_dump -h localhost -p 1609 -U postgres -W -F  t my_db > mydb_dump_file.tar

 –restore using pg_restore , from a tar file

Option 1 – using the -C switch will create a new db

pg_restore -h localhost -p 2222 -d mydb -C -F t -v /home/postgres/myfile.tar

Option 2 – using the -c switch will clear the source tables

–restore using Postgres database created from a pg_dump but as a sql file

psql -U username -f mybackupfile.sql

–query PostgreSQL port number

select inet_server_port();

–change owner of database

ALTER DATABASE name OWNER TO new_owner