Re: pg_restore error with partitioned table having exclude constraint

On Thu, 17 Apr 2025 at 15:06, Álvaro Herrera <alvherre@kurilemu.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_tsrtstzrange 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_idWITH =, 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