Partitioning table with billion row I have a big table referenced by other tables and need to split the table
on several parts.


I create table by CREATE TABLE product_part0 () INHERITS (product);
Move some rows to product_part0 by INSERT INTO product_part0 SELECT *    FROM ONLY product ORDER BY id LIMIT 200;
Now I need to remove duplicates from master-table. Run DELETE FROM ONLY product WHERE id IN (SELECT id FROM product_part0);


With result:

ERROR: update or delete on table "product" violates foreign key constraint
"product_id_refs" on table "searchcache"
DETAIL: Key (id)=(13375) is still referenced from table "searchcache".


So, how to delete duplicate rows?

I made similar question at postgresql maillist.
Unfortunately, you can't define a foreign key against a partitioned table, only the partitions themselves - but, of course, the referencing table can have only on FK on the column in question...

So you have to either partition the referencing table, too, or live together with foreign-keylessness (with help from a trigger or the application code).

Let me note that similarly, you will have to define the eventual (unique) indexes on the child tables one by one.  This also means that unique constraints have to be defined this way, too.