Thanks a lot Michael. Get well soon.

From: Michael Lewis <mle...@entrata.com>
Sent: November-18-19 12:57 PM
To: Shatamjeev Dewan <sde...@nbsps.com>
Cc: pgsql-general <pgsql-gene...@postgresql.org>
Subject: Re: ERROR: there is no unique constraint matching given keys for 
referenced table "audit_p"

On Mon, Nov 18, 2019 at 10:10 AM Shatamjeev Dewan 
<sde...@nbsps.com<mailto: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.

Reply via email to