PostgreSQL cheatsheet

--quit from psql
\q

--version
select version();

--Create PostgreSQL database

create database MYDB owner myowner;

--ALTER Database name 

ALTER DATABASE myolddbname RENAME TO new dbname;

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

drop database MYDB;

--if other user sessions are blocking DROP DATABASE use - How to kill a PostgreSQL user session connection

--list PostgreSQL databases
\list
\l

--list tables
\dt

--list all tables,views,sequences
\dtvs the_schema.*

--list all servers on Postgres

\des+

--list all extensions

\dx

--list schemas in a database with psql

\dn

--create a schema
CREATE SCHEMA myschema;

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

--ALTER schema name 

ALTER SCHEMA name RENAME TO new_name

--drop schema

drop schema IF EXISTS my_schema

--you may get this error message - then use the CASCADE option which will drop the dependent objects

---ERROR: cannot drop schema my_schema because other objects depend on it
--DETAIL: table my_schema.mytable depends on schema my_table

drop schema IF EXISTS my_schema CASCADE

-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 - alter 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);

--list SEQUENCES in a database 

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES;

--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 the COPY function to export data to a text files

COPY ( select col1,col2 from mytable) TO '/tmp/myfile.txt' DELIMITER ',' HEADER;

--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

--use watch to create repeat executes of a query 

This example will execute every 1 second

SELECT usename, datname, state FROM pg_stat_activity; \watch 1