Postgresql – Adding a dropped check-constraint what queries the table

check-constraintspostgresql

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?

Best Answer

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
;

Related Solutions

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers                            
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying | 
 foo       | text              | 

db=> \d temp_person_two 
   Column    |         Type         |                            Modifiers                            
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    | 
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 |

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.

PostgreSQL – How to Drop an Unnamed Check Constraint in a Table

The constraint has a name, whether you specified it or not.

From psql, \d foo will list all table constraints, along with their automatically assigned names.

You can also find these by querying the catalog tables directly:

SELECT conname
FROM pg_constraint
WHERE
  conrelid = 'foo'::regclass AND
  contype = 'c'

The table name can be schema-qualified if necessary (e.g. 'public.foo'::regclass).

contype = 'c' filters it down to CHECK constraints; the contype values for other constraint types are documented here.

Best Answer

Related Solutions

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

PostgreSQL – How to Drop an Unnamed Check Constraint in a Table

Related Question