Postgresql - Generating where not Exists condition dynamically for re-runnable insert script

Question

I need to generate Insert script in postgres for all the tables in a database such that it can be run again without throwing any error. The problem is, Only few tables have primary key while the rest have Unique index on different columns.

This is why I am not able to list out the columns on which unique index has been created. The reason behind this is that the schema is automatically created through Magnolia.

Can anyone help me write the query which produces Insert statement including 'Where not Exists (Select 1 from table where column = value)' condition based on Primary Key/Unique columns?

Answer 1

This function returns Insert script for data and works well with tables on which primary constraint is not available. I have modified the code that I found on another thread by adding the condition to it.

CREATE OR REPLACE FUNCTION public.generate_inserts(varSchema text, varTable text) RETURNS TABLE(resultado text) AS $$

DECLARE CODE TEXT;

BEGIN
CODE :=
(
SELECT
'SELECT ''INSERT INTO '
|| table_schema || '.'
|| table_name ||' ('
|| replace(replace(array_agg(column_name::text)::text,'{',''),'}','') || ') SELECT ''||'
|| replace(replace(replace(array_agg( 'quote_nullable(' || column_name::text || ')')::text,'{',''),'}',''),',',' || '','' || ')
|| ' || '' Where Not Exists (Select 1 From ' || table_name ||' Where 1 = 1 ' 
|| ''''
|| replace(replace(replace(replace(array_agg(' || '' and (' || column_name::text || ' = '' || quote_nullable(' || column_name::text || '),' || ' || '' or ' || column_name::text || ' is null)''')::text,'{',''),'}',''),'"',''),',','')
|| '|| '');'''
|| ' FROM ' || table_schema || '.' || table_name || ';'
FROM information_schema.columns c 
WHERE table_schema = varSchema
AND table_name = varTable
GROUP BY table_schema, table_name);



RETURN QUERY
EXECUTE CODE;
END;
$$ LANGUAGE plpgsql;