I have a big,big table, this big table is referenced by another tables by foreign keys.
I have a performance issues so i decide partition it by month -> ...,table_201610,table_201611,table_201612,... after i have been created the partition tables i try to insert data but foreign key constraint doesn't allow the insertion. I try set to deferrable those constraints but doesn work. only works if i drop those constraints :( What can i do to set foreign keys referencing a big table from partition table? Here the process in detail: 0. I have a big_table with many constraint. I have a tables, T1,T2,T3,T4 referencing id from bigtable like foreign key. 1. Backup T1..T4 tables. 2. Delete records from T1..T4 tables. I must delete if not then point 6 fails. 3. Rename bigtable to temp_bigtable. 4. Create master bigtable with same DDL 5. Create child/partitionated tables inherits from big_table. and create the same constraint and indexes like big_table 6. Reset sequences, views and constraint to the new bigtable. When you rename a table the views and sequences related to bigtable are renamed to temp_bigtable too. 7. Create a function to insert data to the right partition table using copy. 8. insert data with the function created. and I got: ERROR: insert or update on table "T1" violates foreign key constraint "fk-lstl-stl" DETAIL: Key (id_stl_msg)=(26874097) is not present in table "bigtable". CONTEXT: SQL statement "WITH upsert AS ( UPDATE T1 SET id_stl_msg = v_id_stl_msg WHERE id_trun = in_idtrun RETURNING * ) --INSERT INSERT INTO T1 (id_trun, id_stl_msg) SELECT in_idtrun, v_id_stl_msg WHERE NOT EXISTS (SELECT * FROM upsert)" But if drop the constraint works fine .... What can i do to set foreign keys refrencing a id from bigtable or partition table?