From: | Miles Elam <miles(dot)elam(at)productops(dot)com> |
---|---|
To: | Michael Lewis <mlewis(at)entrata(dot)com> |
Cc: | Shatamjeev Dewan <sdewan(at)nbsps(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: ERROR: there is no unique constraint matching given keys for referenced table "audit_p" |
Date: | 2019-11-18 20:01:33 |
Message-ID: | CAALojA9wdnO0jraUyYY5UQP0cVEedNw_QJi0Rk+9FrodFwyv-A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <mlewis(at)entrata(dot)com> wrote:
> On Mon, Nov 18, 2019 at 10:10 AM Shatamjeev Dewan <sdewan(at)nbsps(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-11-18 20:32:31 | Re: ERROR: there is no unique constraint matching given keys for referenced table "audit_p" |
Previous Message | Dave Hughes | 2019-11-18 19:56:20 | pgaudit log directory |