0

I'm using a Postgres to preist a hierarchical data set; the table I'm using is something like this:

CREATE TABLE layers (
    bucket      text not null,
    uniq_id     serial not null,
    name        text not null,
    parent      text,
    value       text,

    constraint layers_pk primary key (bucket, uniq_id)
);

The name value should be unique in a defined scope, determined by parent value. In order to restrict and guarantee this I decided to use a CONSTRAINT CHECK, by a function relying on SELECT the current state of database.

CREATE FUNCTION layers_name_uniqueness_check(bk text, nm text, pr text) RETURNS BOOLEAN AS $$
    DECLARE
        cnt integer;
    BEGIN
        IF pr IS NULL THEN
            SELECT INTO cnt COUNT(*) FROM layers WHERE bucket = bk AND name = nm AND parent IS NULL;
        ELSE
            SELECT INTO cnt COUNT(*) FROM layers WHERE bucket = bk AND name = nm AND parent = pr;
        END IF;
        RETURN cnt = 0;
    END;
$$
LANGUAGE PLPGSQL;

ALTER TABLE layers
    ADD CONSTRAINT layers_name_uniqueness
    CHECK (layers_name_uniqueness_check(bucket, name, parent))
;

This approach seems working well if provided from the starting point. If I want to apply this CONSTRAINT CHECK to a table that has already some records, this approach will not work boviously, as I'm running query on the table, and the given record is already there.

INSERT INTO layers (bucket, name, parent, value) VALUES ('bisphone', 'A', null, 'Hello');
INSERT INTO layers (bucket, name, parent, value) VALUES ('bisphone', 'A.A', 'A', 'Hello');

ALTER TABLE layers DROP CONSTRAINT layers_name_uniqueness;

ALTER TABLE layers
    ADD CONSTRAINT layers_name_uniqueness
    CHECK (layers_name_uniqueness_check(bucket, name, parent))
;

The Output

[23514] ERROR: check constraint "layers_name_uniqueness" is violated by some row

I need to know that is it possible to fix this by a trick or should I address the problem (preventing duplicated name value in the the determined scope by parent) with a different approch. Any help?

2
  • Why are you not enforcing uniqueness with a UNIQUE constraint? – Michael Kutz May 27 '20 at 10:57
  • @MichaelKutz The real implementation have a more complex logic inside the function it relies on, though for the published example a UNIQUE should be enough. – Reza Same'ei May 27 '20 at 12:02
0

By adding the NOT VALID option to the ALTER ... ADD CONSTRAINT statement it's possible to ignore the the validation of the CONSTRAINT on the existed data.

ALTER TABLE layers
    ADD CONSTRAINT layers_name_uniqueness
    CHECK (layers_name_uniqueness_check(bucket, name, parent))
    NOT VALID
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.