Add the column only if not exist on PostgreSQL

here the function to add the column to the table only if column does not exist.

create or replace function addcol(schemaname varchar, tablename varchar, colname varchar, coltype varchar)
returns varchar 
language 'plpgsql'
as $$
declare 
    col_name varchar ;
begin 
      execute 'select column_name from information_schema.columns  where  table_schema = ' ||
      quote_literal(schemaname)||' and table_name='|| quote_literal(tablename) || '   and    column_name= '|| quote_literal(colname)    
      into   col_name ;   

      raise info  ' the val : % ', col_name;
      if(col_name is null ) then 
          col_name := colname;
          execute 'alter table ' ||schemaname|| '.'|| tablename || ' add column '|| colname || '  ' || coltype; 
      else
           col_name := colname ||' Already exist';
      end if;
return col_name;
end;
$$

function would require argument schema, table, column and data type

if the column exist it returns, 'column Already exist '  else 
return the added column.




 select addcol('masters','m_approve_status', 'test1', 'integer');




create a sequence that start with max id of another table in PostgreSQL

recently I had a chance to work around getting value from different table and merge into single table as part of the migration.

during the same I have to create a sequence that starts with maximum id of another table and increment by,

any way that can be created by simple as
 

SELECT max(col_name) from table_name  -- it 'll rtn max id i.e 89
CREATE SEQUENCE seqtest INCREMENT BY 1 STARTS WITH 89;

the same can be written in function, to shun the half human interaction.

the function below will find the max id from given table and assign it into sequence (Assign max(id) to Sequence)

the function find_max()  would return the max id  i.e currval of the sequence.

create or replace function find_max_seq(tablename varchar, colname varchar) 
returns int 
language 'plpgsql'
as 
$$
declare 
 maxno integer;
begin 
 execute 'select  max('||colname|| ')  from ' || tablename  into  maxno ;   
 --execute 'select max(cast( '||colname|| ' as int))  from ' || tablename  into  maxno ;   
 raise info  ' the val : % ', maxno;
 execute 'create sequence seqtest increment by 1 start with  ' || maxno; 
return maxno;
end;
$$

 SELECT  findmax('masters.m_approve_status', 'status_id')

PostgreSQL : No operator matches the given name and argument type(s).

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts

this information related to PostgreSQL version,

Issue :: DATE data type with pattern matching operator (LIKE) in version 8.3 later.

Date data type with LIKE operator works fine for the prior to 8.3 version.

for ex : SELECT name FROM employee WHERE date_of_birth LIKE '2007-01-19%'; // older version works fine

query will display all the name of date_of_birth falls on 2007-01-19.

but version 8.3 will gives hint like
> HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.

and
This isn't a bug. 8.3 removes a bunch of implicit casts to text which
led to inappropriate behaviours. Prior to 8.3, your timestamps was
casts to text implicitly.

to get the same query output you may use "date_trunc" function its works like normal "trunc" function.

for ex : SELECT name FROM employee WHERE date_trunc('day',date_of_birth) = '2007-01-19';
query will round with date and display all the name.

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,