YwS49Qc6

1DROP TABLE IF EXISTS tbl_foo;
2CREATE TABLE tbl_foo (
3    id bigint NOT NULL,
4    geom public.geometry(MultiPolygon, 4326),
5    att_category character varying(15),
6    att_value integer
7);
8INSERT INTO tbl_foo (id, geom, att_category, att_value) VALUES 
9    (1, ST_SetSRID('MULTIPOLYGON (((0 6, 0 12, 8 9, 0 6)))'::geometry,4326) , 'cat1', 2 );
10INSERT INTO tbl_foo (id, geom, att_category, att_value) VALUES 
11    (2, ST_SetSRID('MULTIPOLYGON (((5 0, 5 12, 9 12, 9 0, 5 0)))'::geometry,4326), 'cat1', 1 );
12INSERT INTO tbl_foo (id, geom, att_category, att_value) VALUES 
13    (3, ST_SetSRID('MULTIPOLYGON (((4 4, 3 8, 4 12, 7 14,10 12, 11 8, 10 4, 4 4)))'::geometry,4326) , 'cat2', 5 );
14	
15SELECT
16(ST_Dump(
17    ST_SymDifference(a.geom, b.geom) 
18)).geom
19FROM tbl_foo a, tbl_foo b
20WHERE a.ID < b.ID AND ST_INTERSECTS(a.geom, b.geom)
21UNION ALL
22SELECT
23ST_Intersection(a.geom, b.geom) as geom
24FROM tbl_foo a, tbl_foo b
25WHERE a.ID < b.ID AND ST_INTERSECTS(a.geom, b.geom);