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;

Postgresql - ADD/ REMOVE - DEFAULT VALUE FROM A COLUMN

 -- EXAMPLE: MY_TABLE_NAME HAVE A DEFAULT TOTAL OF 0 

ALTER TABLE MY_TABLE_NAME ALTER COLUMN TOTAL_CENTS SET DEFAULT 0;

-- EXAMPLE: MY_TABLE_NAME ARE AVAILABLE BY DEFAULT

ALTER TABLE MY_TABLE_NAME ALTER COLUMN AVAILABLE SET DEFAULT TRUE;

ALTER TABLE MY_TABLE_NAME ALTER COLUMN TOTAL_CENTS DROP DEFAULT;

Postgresql - ADD NOT NULL & REMOVE NOT NULL CONSTRAINT

 ALTER TABLE MY_TABLE_NAME ALTER COLUMN EMAIL SET NOT NULL;

ALTER TABLE MY_TABLE_NAME ALTER COLUMN EMAIL DROP NOT NULL;

Postgresql - Add/Remove primary key

 ALTER TABLE MY_TABLE_NAME ADD PRIMARY KEY (COLUMN_1, COLUMN_2);

ALTER TABLE MY_TABLE_NAME DROP CONSTRAINT TEST1_PKEY;

Postgresql - Identify duplicate rows

SELECT (mytablename.*)::text, count(*)

FROM mytablename

GROUP BY mytablename.*

HAVING count(*) > 1

Postgresql - Copy data from one table to another

 INSERT INTO mytablenamenew SELECT * FROM mytablenameold;

Postgresql - Delete N number of rows

DELETE FROM mytablename

WHERE id IN (SELECT id FROM mytablename ORDER BY 1 LIMIT 30000)

Postgresql - Random number generation

update foo set bar = floor(random() * 9 + 1); 

SELECT random(),floor(random() * 9 + 1) LIMIT 10;

select floor(7000000000 + random() * 8999);  

PostgreSQL - How to find and kill a hanging query?

First, check all the processes that are running:

SELECT * FROM pg_stat_activity;

So you can identify the PID of the hanging query you want to terminate, run this:

Option #1 (graceful):

SELECT pg_cancel_backend(PID);

Option #2 (forceful):

SELECT pg_terminate_backend(PID);

SELECT 

    pg_terminate_backend(pid) 

FROM 

    pg_stat_activity 

WHERE 

    -- don't kill my own connection!

    pid <> pg_backend_pid()

    AND state = 'idle'

How do I get the list of primary key(s) of a table from Postgres via pgadmin?

SELECT c.column_name, c.data_type

FROM information_schema.table_constraints tc 

JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) 

JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema

AND tc.table_name = c.table_name AND ccu.column_name = c.column_name

WHERE constraint_type = 'PRIMARY KEY' and tc.table_name = 'mytablename';

SQL Error : Multiple ResultSets were returned by the query

Upon using the custom query in the source object, the mapping fails with the following error:

The select query for read operation failed because of the following error: [org.postgresql.util.PSQLException: Multiple ResultSets were returned by the query​

However, the same query works as expected in PostGresDB.​

If the custom query has more than two query statements, one which is commented and separated by a semicolon, then multiple ResultSets were returned by the query. 

This is expected behavior as the JDBC driver verifies both the statement and returns a null value for the blank query which is a commented line.

Solution:

To resolve the issue, either remove the commented lines from the query or keep the commented lines at the top of the query, so it can be disregarded while running the original query