CODEWITHSUNDEEP
postgresql
smbarbour
smbarbour

Reputation: 352

Partitioning tables in PostgreSQL by a value stored in a different table

We are using a partitioning scheme that uses a date field in the table to determine the partition that should be used i.e. table foo with child foo_y2016m01.

This works for our simpler tables, but we are researching how to approach some of our more complex table relations to do the same style of partitioning, such as foo having a date field and bar storing the row id of the associated record in foo. The bar table does not have a date field of its own, but we still want to partition the table such that child tables would follow the same format (bar_y2016m01).

Is it possible to format the check constraint on bar such that it can use the date field from foo?

Upvotes: 0

Views: 55

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16467

The answer is: Possibly.

You can use an expression in check constraint so you can write a function that will check the other table for you. However it will be only triggered during instert/update on the table so the data might loose integrity and you'll have to use a foreign key or add a trigger to table bar to keep foo correct.

Upvotes: 1

Related Questions