4 ms·
How do people partition with a requirement where you also need unique index constraints (across all partitions)?
by Existenceblinks 4y ago
How do people partition with a requirement where you also need unique index constraints (across all partitions)?
- Existenceblinks 4y agoI wonder if this worth the price: an extra (thing_uniq) table and extra key column on partitioned tables. So pay an extra query to the thing_uniq table before insert to target table. thing_uniq has one indexed column (the key). But we lose upsert.
- plasma 4y agoUnique constraints work as long as the partition key is included in the constraint (which makes sense, so a table knows it’s the only partition for a value and checks it’s unique index).
- piaste 4y agoI think I would use a lightweight 'reference' table with the unique index, put the actual data in the partitioned table, and use a foreign key to enforce a 1:1 relationship. Something like this: create table orders( order_id int unique , year int , primary key (order_id, year) ); create table order_data( order_id int , year int , LOADS_OF_COLUMNS_HERE jsonb , primary key (order_id, year) , foreign key (order_id, year) references orders(order_id, year) ) partition by list(year);
- Existenceblinks 4y agoYeah, same thought as my other comment. Not sure about the other comment that says using partition key .. it doesn't seem possible.