GATHERING ALL CREATE TABLE STATEMENTS IN POSTGRES DATABASE

=========================================================
GATHERING ALL CREATE TABLE STATEMENTS IN POSTGRES  DATABASE
=======================================================
1)SELECT any one table, right click and click on BACKUP
2)In FILE OPTIONS TAB, Give any filename by selecting the path
3)select format as PLAIN
4)In DUMP OPTIONS #1 TAB, select ONLY SCHEMA in TYPE OF OBJECTS
5)In DUMP OPTIONS #2 TAB, select Verbose Messages in TYPE OF OBJECTS(This is default option , so you don't hav to do any)
6)In OBJECTS TAB, Select all the tables you want.
7)Click on Backup Button

That's it, The output file does consists of all the tables with CREATE STATEMETNS you Want.

Finding SQL STATEMENTS that are locking TABLES.

SELECT * FROM pg_stat_activity;

Removing TABLE Locks in POSTGRES

1) Find Blocked Tables with the below query and get the PID.

select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc;

2) Pass the PID in the below query to Remove the LOCK
SELECT pg_terminate_backend(6052);

Finding Blocked Tables in POSTGRES

select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc;

CASE Example in POSTGRES

SELECT  
CASE WHEN "EQU000"."city_C"= 'abc'
THEN 'cityNAME'
ELSE 'Group'
END AS "NAME"
FROM "EQU000" 

Getting Rowcount of all the tables in POSTGRES

SELECT
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r'
ORDER BY reltuples DESC;

Describing Table in POSTGERS (DESC in Oracle)

Describing Table :

select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = 'EQUI_HDR';

OVER PARTITION ..... ROW_NUMBER Example in POSTGRES

select * from (
SELECT "PROCE_UNIT_C" ,"EQUI_C" ,"COMPO_C" ,"COMPO_TYPE_C","CUIF_INS_CAT",
row_number() over(PARTITION BY "PROCE_UNIT_C", "EQUIP_C","COMPO_C","COMPO_TYPE_C","CUIF_INS_CAT")  "Row_Number",
count("CUIF_INS_CAT") over(PARTITION BY "PROCE_UNIT_C", "EQUI_C","COMPO_C","COMPO_TYPE_C","CUIF_INS_CAT"),
CHR(min(ASCII("CUIF_INS_CAT")) over(PARTITION BY "PROCE_UNIT_C", "EQUI_C","COMPO_C","COMPO_TYPE_C"))
FROM
"INSPHISTORY" ) "a"
where "a"."Row_Number"=1