Re: pg_restore error with partitioned table having exclude constraint

On Thu, 17 Apr 2025 at 15:06, Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:
> Hello,
>
> On 2025-Apr-16, Keith Paskett wrote:
>
>> A table partitioned by LIST with an exclusion constraint errors on
>> creating the constraint on the parent table when doing a
>> pg_dump/pg_restore
>
> Was this working previously?
>
> Thanks,

It seems PG 16 does not support exclusion constraints on partitioned tables.

[local]:2119558 postgres=# SELECT version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 16.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
(1 row)

[local]:2119558 postgres=# CREATE TABLE had_working_hist (context_id serial not null, had_person_id integer not null, active_tsr tstzrange not null) partition by LIST (context_id);
CREATE TABLE
[local]:2119558 postgres=# ALTER TABLE had_working_hist ADD CONSTRAINT had_working_hist_tsr_excl EXCLUDE USING btree (context_id WITH =, had_person_id WITH =);
ERROR: exclusion constraints are not supported on partitioned tables
LINE 1: ALTER TABLE had_working_hist ADD CONSTRAINT had_working_hist...
^

--
Regrads,
Japin Li