CODEWITHSUNDEEP
postgresqlpartition
René paul Debroize
René paul Debroize

Reputation: 269

How to add or emulate a unicity constraint on a column part of a composite primarey key of a partitioned table in postgres 12?

I'd like to do domething like:

create table foo (
  foo_id int,
  cutlery text,
  primary key (foo_id, cutlery)
  unique (foo_id)
) partition by list (cutlery);

create table foo_spoon partition of foo for values in ('SPOON');
create table foo_fork partition of foo for values in ('FORK');

but postgres complain:

[0A000] ERROR: insufficient columns in UNIQUE constraint definition

Detail: UNIQUE constraint on table "foo" lacks column "cutlery" which is part of the partition key.

I can add a unique constraint on foo_id for each partition table but it does not prevent to do something like

insert into foo_spoon (foo_id, cutlery) values (1, 'SPOON'); -- this goes into one partition
insert into foo_fork (foo_id, cutlery) values (1, 'FORK');    -- this goes into another partition

Is there a nice way to address this issue, is the only solution trigger based ?

Using triggers i did something like:

create or replace function _check_duplicate_foo_id()
returns trigger as
$$
begin
    if exists (select count(*) from foo where foo.foo_id = new.foo_id having count(*) <> 1) then
        raise exception 'Duplicate foo.foo_id %s', new.foo_id;
    end if;
    return new;
end;
$$ language plpgsql;

create trigger _check_duplicate_foo_id_trig
    after insert or update on foo_spoon
    for each row execute procedure _check_duplicate_foo_id();

-- same for foo_fork

Is it the right direction or does it looks totally crazy?

Upvotes: 2

Views: 1304

Answers (1)

AdamKG
AdamKG

Reputation: 14081

There is no built-in "nice" way to handle this, because of how unique constraints are implemented in Postgres. The docs note this limitation:

Unique constraints on partitioned tables must include all the partition key columns. This limitation exists because PostgreSQL can only enforce uniqueness in each partition individually.

Unique constraints are implemented using an index, and each index only applies to a single partition, so enforcing uniqueness across multiple partitions must use some other mechanism.

Upvotes: 1

Related Questions