I was under the impression that PostgreSQL 12 removed this limitation. Was this incorrect? https://www.2ndquadrant.com/en/blog/postgresql-12-foreign-keys-and-partitioned-tables/
On Mon, Nov 18, 2019 at 9:58 AM Michael Lewis <mle...@entrata.com> wrote: > On Mon, Nov 18, 2019 at 10:10 AM Shatamjeev Dewan <sde...@nbsps.com> > wrote: > >> I am trying to create a foreign key constraint on a table : audit_param >> in postgres 12 which references partitioned table audit_p. is there anyway >> to get rid of this error. >> >> >> > *ERROR: there is no unique constraint matching given keys for referenced >> table "audit_p"* >> > > As far as I understand, what you want is not possible. You cannot > partition on a timestamp and then foreign key to an object_id natively. You > can get around this with triggers- > https://www.depesz.com/2018/10/31/foreign-key-to-partitioned-table-part-2/ - > but that would not be recommended. > > Admittedly, I have a pretty nasty head cold so it may be that someone > chimes in with much better insight on your design. > > By the way, there are strong recommendations for using timestamp WITH > TIMEZONE in nearly all cases. I just wanted to mention since timestamp > without timezone is rarely the best choice. >
