Postgresql - Compare two tables and identify & change rows

 INSERT INTO target 

    (id, name, location, flag,ad_org_id)

    (

        SELECT 

            s.id, s.name, s.location, s.flag,s.ad_org_id 

        FROM source s

    ) 

ON CONFLICT (id,ad_org_id) 

DO UPDATE

SET 

name = excluded.name,

location = excluded.location,

flag=excluded.flag ;

Postgresql - List all table rows count in the schema

 SELECT 

        schemaname,

        relname,

        n_live_tup AS EstimatedCount 

FROM pg_stat_user_tables 

ORDER BY n_live_tup 

DESC LIMIT 5;

Postgresql - List all tables

SELECT 

    TABLE_NAME 

FROM INFORMATION_SCHEMA.TABLES 

WHERE 

    TABLE_SCHEMA = 'mydatabase' 

    AND 

    TABLE_TYPE = 'BASE TABLE' 

ORDER BY TABLE_NAME

Postgresql - Add/ Remove Unique Constraint

 ALTER TABLE mydatabase.tablename DROP CONSTRAINT constraintname;

ALTER TABLE mydatabase.tablename DROP ADD CONSTRAINT constraintnameUNIQUE  (name, ad_org_id);

Postgresql - List Constraints

 SELECT r.relname,c.* FROM pg_catalog.pg_constraint c

INNER JOIN pg_catalog.pg_class r ON r.oid = c.conrelid

INNER JOIN pg_catalog.pg_namespace n ON n.oid = connamespace 

WHERE n.nspname = 'mydatabase' AND conname = 'anyconstraintnamea'

Postgresql List Index

 SELECT TABLENAME,INDEXNAME,INDEXDEF FROM PG_INDEXES 

WHERE SCHEMANAME = 'mydatabase' ORDER BY TABLENAME,INDEXNAME;

Postgresql - Turn off triggers

 SET session_replication_role = replica;