Few RAW SQL

Common_PostgreSQL.sql

-- Get Database Size
select pg_size_pretty(pg_database_size('<DB Name>'));
-- Get Table Size
select pg_size_pretty(pg_table_size('<Table SIze>'));
-- Delete a small table
delete from table_name;
-- Delete a big table 
TRUNCATE TABLE table_name;
                                    
-- Count row of a table
select count(*) from table_name;
-- Unique togather of existing table columns 
ALTER TABLE the_table ADD CONSTRAINT constraint_name UNIQUE (column1, column2);
-- Copy a table with same structure & data
CREATE TABLE new_table AS TABLE existing_table;
-- Copy a table with same structure but No data 
CREATE TABLE new_table AS TABLE existing_table WITH NO DATA;
-- Copy a table with same structure & data with condition
CREATE TABLE new_table AS 
SELECT
*
FROM
    existing_table
WHERE
    condition;


-- Getting duplicate data from a table
-- method 01: 
select column1, column2, count(*)
from table_name
group by column1, column2
HAVING count(*) > 1;
-- method 02:
select * from table_mame ou
where (select count(*) from table_name inr
where inr.compare_column_name = ou.compare_column_name and inr.compare_column_name = ou.compare_column_name) > 

-- Method 03
SELECT * FROM (
  SELECT *,
  ROW_NUMBER() OVER(PARTITION BY column1, comlumn2 ORDER BY id) AS Row
  FROM table_name
) dups
WHERE dups.Row > 1;
                                    
-- Delete duplicate data and keep lowest id row 
 DELETE FROM table_name a USING table_name b WHERE a.id > b.id and b.column1 == a.column1;
-- Delete duplicate data and keep latest id row 
DELETE FROM table_name a USING table_name b WHERE a.id < b.id and b.column1 == a.column1;

-- Delete duplicate data and move duplicate data into another table 
WITH
  u AS (select * from (SELECT *, ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id) AS Row
  FROM table_name) dups where dups.Row > 1),
  x AS (DELETE FROM table_name a USING table_name b
      WHERE a.id > b.id and a.column1 = b.column1 and a.column2 = b.column2
      )
INSERT INTO duplicate_table SELECT * FROM u;
                                    
-- Getting all sizes sequence (max to min)
SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 30;

 -- Getting all database size 
 select t1.datname AS db_name,
       pg_size_pretty(pg_database_size(t1.datname)) as db_size
from pg_database t1
order by pg_database_size(t1.datname) desc;

-- Drop role and drop user if have any assign permission
REASSIGN OWNED BY vubon TO postgres;  -- or some other trusted role
DROP OWNED BY vubon;
-- repeat both in ALL databases where the role owns anything or has any privileges!

DROP USER vubon;