List all duplicate Foreign Key constraint in PostgreSQL

Below SQL query will return all the referential integrity (Foreign Key ) constraint  in a current database.

This statement return  all the foreign key constraint including duplicate foreign key constraint that's differ in  constraint name for the same   reference (Parent)  table & column for the child table.

example :

 CONSTRAINT fk964cbaa6283cf475 FOREIGN KEY (str_fa_request_id)
      REFERENCES panmydesk4400.d_fa_indent_form (str_fa_request_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,

CONSTRAINT m_cash_purchase_str_fa_request_id_fkey FOREIGN KEY (str_fa_request_id)
      REFERENCES panmydesk4400.d_fa_indent_form (str_fa_request_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,

above example clearly shows that both foreign key constraint ( fk964cbaa6283cf475 and m_cash_purchase_str_fa_request_id_fkey )

 defined in child table of column

str_fa_request_id

refers the same reference table & column.

panmydesk4400.d_fa_indent_form (str_fa_request_id)

SELECT
    pc.conname as constraint_name, 
    --conrelid as child_table_id,   
    pclsc.relname as child_table,
    --pc.conkey as child_column_id,
    pac.attname as child_column,
    --confrelid as parent_table_id,
    pclsp.relname as parent_table,
    --pc.confkey as parent_column_id,
    pap.attname as parent_column,   
    nspname as schema_name
FROM 
    (
    SELECT
         connamespace,conname, unnest(conkey) as "conkey", unnest(confkey)
          as "confkey" , conrelid, confrelid, contype
     FROM
        pg_constraint
    ) pc
    JOIN pg_namespace pn ON pc.connamespace = pn.oid
    -- and pn.nspname = 'panmydesk4400'
    JOIN pg_class pclsc ON pc.conrelid = pclsc.oid
    JOIN pg_class pclsp ON      pc.confrelid = pclsp.oid
    JOIN pg_attribute pac ON pc.conkey = pac.attnum    and pac.attrelid =       pclsc.oid
    JOIN pg_attribute pap ON pc.confkey = pap.attnum and pap.attrelid = pclsp.oid

ORDER BY pclsc.relname

note : 

•       line start with -- indicate commented 

run the above statement  would return the following result  as output,

QUERY TO RETUN MOSTLY USED / ACCESSED TABLE IN POSTGRESQL

Below query will return most accessed table in PostgreSQL database,

SELECT c.relname as table_name, c.reltuples::numeric as no_of_records, c.relpages as  no_of_pages,
       t.seq_scan as tbl_seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,      t.n_tup_ins, t.n_tup_upd, t.n_tup_del,
       COALESCE(t.idx_tup_fetch,0) + COALESCE(t.seq_tup_read,0) as total_read
    FROM pg_stat_user_tables as t inner join pg_class as c
    ON (t.relid = c.oid)
    WHERE c.relkind ='r'
    ORDER BY total_read desc
    limit  5


it return top 5 mostly used table, if you want this query to return more-then 5 table change the limit 5 to desired No.of table

Compare Database Schema in POSTGRESQL using plpgsql function

Here the plpgsql utility to compare tables and find the difference between given schema of the database in PostgreSQL, And it returns differences table name as well as DDL statement for schema synchronization.

Function accept two schema of same database and it return statement that contains, table differences in schema, and automatically generated Data Definition Language (DDL) scripts ( for synchronization ) that you can use to update the schema of the destination to match the schema of the source.

before run this function in your database check it has plpgsql by
SELECT * FROM pg_language
if your database not supported plpgsql, add by using
CREATE LANGUAGE plpgsql


CREATE OR REPLACE FUNCTION fn_compare_tbl(p_fst_schema character varying, p_snd_schema character varying)
RETURNS character varying
language plpgsql
AS
$$
-- DECLARE VARIABLE
DECLARE
V_FstSchema varchar(150);
V_SndSchema varchar(150);
i integer;
VtblNot varchar = '';
VtblCrt varchar = '';
Vrtn varchar = '';
-- DECLARE CURSOR
Ctbl_list1 refcursor;
Ctbl1 pg_stat_user_tables.relname%type;
Vtblkcu information_schema.key_column_usage;
BEGIN
V_FstSchema := p_fst_schema;
V_sndSchema := p_snd_schema;
i :=0;
loop -- for passing both parameter by using single statment
-- CREATE AND OPEN CURSOR TO HOLD THE TABLES IN ONE SCHEMA
VtblNot := 'TABLES NOT IN SCHEMA : ' || V_sndSchema ||chr(10);
raise info 'TABLES NOT IN SCHEMA : % ',V_sndSchema;
open Ctbl_list1 for execute '
select relname from pg_stat_user_tables where schemaname='||quote_literal(V_FstSchema)||'
except
select relname from pg_stat_user_tables where schemaname='||quote_literal(V_sndSchema);
loop fetch next from Ctbl_list1 into Ctbl1;
exit when not found;
raise info 'TABLE NAME : % ',Ctbl1;
--ADD ALL THE TABLES IN ONE SCHEMA AND NOT IN ANOTHER
VtblNot := chr(10)||VtblNot||';'|| chr(10) || V_FstSchema||'.'||Ctbl1 ;
-- ADD CREATE DDL STATEMENT (var used VtblCrt, only Structure of the table )
VtblCrt := VtblCrt|| chr(10) || 'create table '|| V_SndSchema||'.'||Ctbl1 ||' as select * from '|| V_FstSchema||'.'||Ctbl1 ||' where 1=0; ' ;
raise info 'Vtblcrt : %',Vtblcrt ;
end loop;
Vrtn = vrtn || chr(10) || VtblNot;
Vrtn = chr(10)||Vrtn ||chr(10)||VtblCrt;
VtblCrt = '';
close Ctbl_list1;
exit when i=1;
i:=1;
--THIS TIME THE SCHEMA WOULD INTERCHANGE LOOP WILL EXECUTE AGAIN
V_FstSchema := p_snd_schema;
V_sndSchema := p_fst_schema;
end loop; --for comparing two schema in single statment
raise info 'VRTN : %; ',Vrtn;
RETURN Vrtn;
end;
$$

run above function by ( execute with write result to file option ),

SELECT fn_compare_tbl('dbschema1','dbschema2')

here both schema should be in same database, mostly it used in multi-tenant (single database with multiple schema ) architecture. By using this function we can able to know what all are the new feature added in one then other schema.

query in above would return result thats look like,

TABLES NOT IN SCHEMA : dbschema2;

Table name :dbschema1.tempmedical;

DDL statement : create table dbschema2.tempmedical as select * from dbschema1.tempmedical where 1=0;

TABLES NOT IN SCHEMA : dbschema1;

Table name : dbschema2.test4function;

DDL statement : create table dbschema1.test4function as select * from dbschema2.test4function where 1=0; ,

List the all the constraint with statement

Below function fn_get_fk() used to return the FOREIGN KEY constraints with create/drop statement.
This function will minimize PostgreSQL DBA's work to find all the referencing table to drop the constraint one by one manually.
Table name and Column names are the parameter for this function, it generate and return query to, CREATE/DROP CONSTRAINT statement for the referencing table (child table) for the given referenced table(parent table).

CREATE or REPLACE FUNCTION fn_get_fk(tbl_name varchar, col_name varchar) RETURNS varchar
LANGUAGE plpgsql
AS $
-- DECLARE VARIABLE
DECLARE
Vtbl varchar(150);
Vqrys varchar(150);
Vcol varchar(150);
Vcon varchar(150);
Vqryd varchar(300);
Vqrya varchar(300);
Vsch varchar(150);
VqryAdd varchar(30000) = '';
VqryDrp varchar(30000) = '';
Vrtn varchar;
-- CREATE CURSOR
Ctbl_list refcursor;
Ctblcon information_schema.table_constraints %rowtype;
Vtblkcu information_schema.key_column_usage;
BEGIN
-- OPEN CURSOR WITH REFERENCING TABLE FOR GIVEN PARENT TABLE
open Ctbl_list for execute 'SELECT *
FROM information_schema.table_constraints tc
right JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_catalog = ccu.constraint_catalog
AND tc.constraint_schema = ccu.constraint_schema
AND tc.constraint_name = ccu.constraint_name
and ccu.table_name in ('||quote_literal(tbl_name)||')
and ccu.column_name in ('||quote_literal(col_name)||')
WHERE lower(tc.constraint_type) in (''foreign key'');' ;
loop fetch next from Ctbl_list into Ctblcon ;
exit when not found;
Vtbl = Ctblcon.table_name;
Vcon = Ctblcon.constraint_name;
Vsch = Ctblcon.constraint_schema;
--GENERATE DROP CONSTRAINT STATEMENT
Vqryd = 'alter table '|| Vsch||'.'||Vtbl ||' drop constraint ' || Vcon;
VqryDrp = VqryDrp ||';'||chr(10)||Vqryd;
raise info 'Qry : %; ',Vqryd;
--GET REFRENCING COLUMN NAME
Vqrys = 'select * from information_schema.key_column_usage where constraint_name='||quote_literal(Vcon) ;
EXECUTE Vqrys into Vtblkcu;
-- GENERATE ADD CONSTRAINT STATEMENT
Vqrya = 'alter table '|| Vsch||'.'||Vtbl ||' add constraint ' || Vcon ||' foreign key('||Vtblkcu.column_name||')
references personal.m_pis_master(str_empno)';
raise notice 'add constraint : %; ',Vqrya;
VqryAdd = VqryAdd || chr(10) || Vqrya || ';';
end loop;
-- CONCATENATE BOTH ADD & DROP STATEMENT
Vrtn ='DROP CONSTRAINT : '|| VqryDrp ||chr(10)||'ADD CONSTRAINT : '||chr(10)||VqryAdd;
RETURN Vrtn;
END;
$;

in pgAdmin, to Execute the function with write output to file option,
SELECT * FROM fn_get_fk('employee_master','str_empno')

in command mode
copy (select * from fn_get_fk('employee_master','str_empno')) to '/root/Desktop/Constraint.txt'

this will return all the foreign key constraint which is referencing the column str_empno on employee_master table.

output looks like

DROP CONSTRAINT :
alter table personal.m_authority drop constraint m_authority_str_empno_fkey;
alter table personal.m_pis drop constraint m_pis_str_empno_fkey;
alter table tours.d_tour_info drop constraint d_tour_info_str_empno_fkey
ADD CONSTRAINT :
alter table personal.m_authority add constraint m_authority_str_empno_fkey foreign key(str_empno) references personal.m_pis_master(str_empno);
alter table personal.m_pis add constraint m_pis_str_empno_fkey foreign key(str_empno) references personal.m_pis_master(str_empno);
alter table tours.d_tour_info add constraint d_tour_info_str_empno_fkey foreign key(str_empno) references personal.m_pis_master(str_empno),

LIST OUT ALL (FORIEN KEY,PRIMARY KEY, UNIQUE KEY) CONSTRAINTS WITH REFERENCED TABLE & FIELD :

Here is the simple utility query for list out all the constraint( CHECK, UNIQUE, PRIMARY & FOREIGN KEY) their name, table name,column name from where constraint created and references table name with column name.

from this query you can also get hierarchical list of all tables

SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,

rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc

LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name

LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name

LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name

WHERE lower(tc.constraint_type) in ('foreign key')

here you can comment WHERE condition to list out all constraint, else it ll list only FOREIGN KEY constraint.

By running above utility you can get foreign key hierarchy in postgresql.

================================================

Below query will return the referencing table and field information for the parent table test_master.

Get all child table for the given parent table

SELECT *
FROM information_schema.table_constraints tc
right JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_catalog = ccu.constraint_catalog
AND tc.constraint_schema = ccu.constraint_schema
AND tc.constraint_name = ccu.constraint_name
and ccu.table_name in ('test_master')
WHERE lower(tc.constraint_type) in ('foreign key');

To find all child tables that belong to a parent table you can use above query, change the table (test_master) .

Function for spell out a number

Oracle having in built functionality to convert number into word format by using Juline(J) and spell out (SP) Pattern Modifiers for Date/Time Formatting.
Convert Number into Word in postgresql
for example

SELECT TO_CHAR(TO_DATE(12345,'J'),'JSP') FROM dual;

output like

TWELVE THOUSAND THREE HUNDRED FORTY-FIVE

in postgresql spell out (SP) pattern modifier not implemented (upto version postgresql 9.0)

here i have posted function to convert given number into word, lets look

create or replace function fn_spellout(p_num varchar) returns varchar as
$$
declare
v_word varchar(50);
v_spell varchar(1000) := '';
v_length integer;
v_pos integer := 1;
begin
select length(p_num) into v_length;
for v_pos in 1 .. v_length loop
--while v_pos <= v_length loop
SELECT case substring(p_num,v_pos,1)
when '1' then 'ONE '
when '2' then 'TWO '
when '3' then 'THREE '
when '4' then 'FOUR '
when '5' then 'FIVE '
when '6' then 'SIX '
when '7' then 'SEVEN '
when '8' then 'EIGHT '
when '9' then 'NINE '
when '0' then 'ZERO '
ELSE 'NULL'
end into v_word;
v_spell := v_spell || v_word;
--v_pos := v_pos+1;
end loop;
return v_spell;
end;
$$ language plpgsql

while execute this function it return output like

select fn_spellout('12345')

"ONE TWO THREE FOUR FIVE "

Function in PL/PgSQL

before creating function, check your database has PL/PgSQL here

* create function

simple pl/pgsql function, adding row to the test table

CREATE OR REPLACE FUNCTION pg_function_insert(
eno INTEGER) RETURNS VARCHAR AS $$
DECLARE
eno_end INTEGER := eno + 5;
BEGIN
FOR num IN eno.. eno_end LOOP
insert into test values (num,'solai');
END LOOP;
RETURN 'rows success fully inserted' ;
END;
$$ LANGUAGE plpgsql;

* execute function

SELECT pg_function_insert(100) AS function_output

we created function with function name of pg_function_insert,input parameter is Integer and function returns String.
table object test has two column eid with integer and ename with varchar data type.
in for loop initial value is input value given by user while executing function ( in our example 100 ), loop will iterate five times because loop end value is eno+5.

in create function statement we have added $$, This is PostgreSQL dollar-quoting. When used in place of the usual single-quotation mark quoting ('), you don't have to escape single quotation marks within the body of the function. This makes them much easier to read. more about $$

* now you can run a query to view the result

select * from test

eid|ename

100|solai
101|solai
102|solai
103|solai
104|solai
105|solai

| is column separator

Setting up PL/PgSQL

One of the most powerful features of PostgreSQL is its support for user-defined functions written in various programming languages, including pure SQL, C, Perl, Python, and PHP. Perhaps the most common programming language for PostgreSQL functions is PL/pgSQL, because it comes with PostgreSQL and is easy to set up.

PL/pgSQL is a procedural language similar to Oracle's PL/SQL. It's much more powerful than pure SQL in that it supports variables, conditional expressions, looping constructs, exceptions etc.. for more details in this blog we are going to learn how adding PL/PgSQL to your Database

Installing PL/pgSQL to Database

To get started with PL/pgSQL, first make sure it's installed in your PostgreSQL database. If it was a part of the template1 database when your database was created, it will already be installed. To see whether you have it, run the following in the psql client:

SELECT true FROM pg_catalog.pg_language WHERE lanname = 'plpgsql';

If the result row has the value true, PL/pgSQL is already installed in your database. If not, quit psql and execute the command as postgres superuser

$ createlang plpgsql database_name
or
cd /usr/local/pgsql/bin/
$ createlang plpgsql -U postgrers_super_uesrname database_name

To add a language, you must have superuser access to the database. If you've just installed PostgreSQL, then you can likely use the default postgres user by passing -U postgres to createlang. From this point, you should be able write and execute function in your pg client.
NOTE : above we just added PL/PgSQL to particular database only, not for entire server.

To create function in postgres can go here