ANTI-JOIN (Faster than NOT IN) :

-- ANTI JOIN
-- visites_iris_2011 > 1400000 entities | iris_centre > 1100000 entities
-- execution time : 39 sec 
SELECT ic.iris_centre  from iris_centre ic where 
NOT EXISTS 
	(SELECT distinct(id_iris_centre) 
	FROM visites_iris_20XX vi 
	WHERE ic.iris_centre = vi.id_iris_centre);

-- execution time : stopped after 1h30
select iris_centre.iris_centre  from iris_centre 
where iris_centre 
NOT IN (select distinct(id_iris_centre) from visites_iris_20XX);
                    

DELETE DUPPLICATES

(without DISTINCT which is resource intensive)

DELETE FROM table_one t1
WHERE EXISTS (SELECT * FROM table_one t2 
	WHERE t1.unique_key > t2.unique_key 
	AND t1.xxxx = t2.xxxx
	AND t1.yyyy = t2.yyyy
	AND t1.zzzz = t2.zzzz
	AND t1. ... = t2. ...
	...	-- all dupplicate fields
	);
-- ">" will keep the first line
-- or use another operator

DROP ALL TABLES IN A SCHEMA

CREATE OR REPLACE FUNCTION drop_table(schema_name varchar(100))
RETURNS bool AS
 $BODY$
 DECLARE
   rec_selection record;
    BEGIN
      FOR rec_selection IN (
            SELECT table_name 
            FROM information_schema.TABLES 
            WHERE table_schema=schema_name 
            AND table_type='BASE TABLE') 
            LOOP
        EXECUTE 'DROP TABLE schema_name.'|| rec_selection.table_name ||'   ';
      END LOOP;
    RETURN True;
   END;
 $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
-- Then execute function
SELECT drop_table('my_schema');
            

CLEANING/CORRECT OVERLAPPING POLYGONS (step-by-step)

--create all intersections with table test
DROP TABLE IF EXISTS intersection;
SELECT a.initial_id as id_a, b.initial_id as id_b, st_intersection(a.geom, b.geom) AS geom_intersection
INTO intersection 
FROM test a, test b;

--delete auto_intersection with id (initial_id)
DELETE FROM intersection WHERE id_a = id_b;

-- create table intersection_union 
DROP TABLE IF EXISTS intersection_union;
SELECT st_union(geom_intersection) AS geom INTO intersection_union FROM intersection
            

-- get difference 
DROP TABLE IF EXISTS difference;
SELECT st_difference(a.geom, b.geom) AS geom_difference
INTO difference 
FROM test a, intersection_union b ;
            

--create the final geom with difference and intersection_union
DROP TABLE IF EXISTS final_shape;
CREATE TABLE final_shape AS
(
select  st_union(iu.geom) AS geom FROM intersection_union iu
UNION
select d.geom_difference as geom FROM difference d
);
            

CLEANING/CORRECT OVERLAPPING POLYGONS (shortest one)

--Only one command
CREATE TABLE final_shape AS
WITH 
UNION_ AS
(SELECT  ST_Union(st_intersection(a.geom, b.geom)) AS geom_intersection
FROM test a, test b
WHERE a.id <> b.id), 
DIFFERENCE_ AS
(SELECT ST_DIFFERENCE(geom , geom_intersection) as geom_difference FROM test a, UNION_)
SELECT geom_intersection FROM UNION_
UNION
SELECT geom_difference FROM DIFFERENCE_;