Some useful queries for Postgres beginners:

    After Mysql ,Now Postgres SQL has been gaining popularity at multiple level in software development.It can be freely downloaded from internet.Postgres Sql server comes with command line utilities to query,backup , restore and what more.For all those spoiled by GUI tools like me ,PgAdmin 3 is right choice.

PgAdmin 3 is GUI tool similar to SQL Server Management Studio for MSSQL server which is freely available. Postgres SQL comes with tones of new features. Its DML syntax is quite similar to Oracle,Mysql along with some own stuff but point to note is postgres is case sensitive means table with name Users' is different from table with name 'users'.

During my exploration of postgres SQL, I collected some useful queries, obliviously from resources on internet.Many of them are from stackoverflow.Thanks to Stackoverflow for being great place for seeking information.

 There are multiple ways to do same thing,neither I claim its the efficient way to do task yet may be useful to beginners to find it in one place.

Here are some queries from my collection

Lists Databases:

SELECT datname FROM pg_database WHERE datistemplate = false;

Lists all tables in the current database:

SELECT * 

FROM information_schema.tables 

WHERE table_type = 'BASE TABLE' 

    AND table_schema = 'public' 

ORDER BY table_type, table_name

Creating a table with Identity /auto increment columns:

CREATE SEQUENCE user_id_seq;

CREATE TABLE user (

    user_id smallint NOT NULL DEFAULT nextval('user_id_seq')

);

ALTER SEQUENCE user_id_seq OWNED BY user.user_id;

All Constraints on Given table:

select * from information_schema.table_constraints where table_name='Projects'

Postgres tables without foreign key

SELECT *

FROM

information_schema.tables

WHERE

table_type = 'BASE TABLE'

AND table_schema = 'public'

and table_name not in (select distinct table_name from information_schema.table_constraints where constraint_type = 'FOREIGN KEY')

Postgres tables with foreign key:

SELECT

tc.constraint_name, tc.table_name, kcu.column_name,

ccu.table_name AS foreign_table_name,

ccu.column_name AS foreign_column_name

FROM

information_schema.table_constraints AS tc

JOIN information_schema.key_column_usage AS kcu

ON tc.constraint_name = kcu.constraint_name

JOIN information_schema.constraint_column_usage AS ccu

ON ccu.constraint_name = tc.constraint_name

WHERE

constraint_type = 'FOREIGN KEY'

Pg-dump backup & restore at command prompt:

Pg-dump is command line utility in postgres used to backup & restore database.

Backup:

pg_dump -U postgres dbname -f dbname_pg_dump_20160715.sql

Restore:

psql -h localhost -d dbname -U postgresuser  dbname < E:\Repo\project\DBScript\dbname_pg_dump_20160715.sql

List foreign key on given table:

SELECT

tc.constraint_name, tc.table_name, kcu.column_name,

ccu.table_name AS foreign_table_name,

ccu.column_name AS foreign_column_name

FROM

information_schema.table_constraints AS tc

JOIN information_schema.key_column_usage AS kcu

ON tc.constraint_name = kcu.constraint_name

JOIN information_schema.constraint_column_usage AS ccu

ON ccu.constraint_name = tc.constraint_name

WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='Projects';

Table referencing given table:

Below query list all table that reference User table.

SELECT

tc.constraint_name, tc.table_name, kcu.column_name,

ccu.table_name AS foreign_table_name,

ccu.column_name AS foreign_column_name

FROM

information_schema.table_constraints AS tc

JOIN information_schema.key_column_usage AS kcu

ON tc.constraint_name = kcu.constraint_name

JOIN information_schema.constraint_column_usage AS ccu

ON ccu.constraint_name = tc.constraint_name

WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='Users';

List functions in given database:

SELECT

distinct routines.routine_name

FROM

information_schema.routines

WHERE

routines.specific_schema=' public'

ORDER BY

routines.routine_name

more detailed query can be

SELECT

routines.routine_name,

parameters.data_type,

parameters.ordinal_position,

routines.specific_schema

FROM

information_schema.routines

JOIN information_schema.parameters ON

routines.specific_name=parameters.specific_name

WHERE

routines.specific_schema='public' AND routine_type='FUNCTION'

ORDER BY

routines.routine_name,

parameters.ordinal_position;

Creating New user:

CREATE USER MyUser WITH PASSWORD 'abcd546fg';

GRANT ALL PRIVILEGES ON DATABASE DbName to MyUser;

REVOKE TRUNCATE ON MyUser.public."Users" FROM MyUser;

REVOKE DELETE ON MyUser.public."Users" FROM MyUser;

GRANT EXECUTE ON  FUNCTION DbName.public.fn_test;

Change Owner of Table,Sequence:

ALTER TABLE DbName.public.my_table;

ALTER SEQUENCE "Tablename_Columnname_seq" OWNER TO MyUser;

ALTER DATABASE DbName OWNER TO MyUser;

Rename User:

ALTER USER MyUser RENAME TO MyNewUser;

Set password:

ALTER USER MyUser WITH PASSWORD 'dljhfdkjs67N'

List all sequence:

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

Postgres Service Start, Stop(Window 10 only):

NET STOP postgresql-x64-9.5

NET START postgresql-x64-9.5

Find list of table with given column name:

select table_name from information_schema.columns where column_name = 'id'

Grant Create database access:
 ALTER USER xdba CREATEDB;

Make user super user:
 
ALTER USER xdba WITH SUPERUSER;

Please feel free to correct queries if  needed.