Postgresql – How to exclude range overlaps if one range may be empty

constraintexclusion-constraintpostgresql

Suppose I want to ensure that, for any expected height in feet, I have a single, unambiguous classification. I create this table:

CREATE TABLE heights_in_feet(
  size TEXT NOT NULL, min INTEGER NOT NULL, max INTEGER NOT NULL
);
INSERT INTO heights_in_feet (size, min, max)
  VALUES ('wee', 0, 2), ('not so wee', 3, 6), ('friggin huge', 7, 10);

To prevent ambiguity, I add an exclusion constraint:

ALTER TABLE heights_in_feet ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (int4range("min", "max") WITH &&);

At first, this seems to be correct:

INSERT INTO heights_in_feet (size, min, max) 
  VALUES ('medium', 4, 5); -- INSERT fails, as it should

However, it doesn't work with empty ranges:

INSERT INTO heights_in_feet (size, min, max) 
  VALUES ('medium', 5, 5); -- INSERT succeeds!

This is bad: it's now ambiguous whether 5 feet is "medium" or "not so wee".

The issue is that Postgres considers int4range(5,5) to be empty, and thus non-overlapping:

SELECT int4range(3, 6) && int4range(4, 5); -- t 
SELECT int4range(3, 6) && int4range(5, 5); -- f

It does consider (3,6) to contain (5,5):

SELECT int4range(3, 6) @> int4range(5, 5);

… but 1) I can't use that in an exclusion constraint because it's not commutative and 2) it also returns true for int4range(3, 6) @> int4range(50, 50); apparently any empty range is "contained by" any non-empty range.

How can I exclude new rows that would create ambiguous classifications?

Best Answer

Use inclusive ranges

Adding an argument like '[]' to int4range tells it to use inclusive ranges, meaning that, eg, (5,5) is not empty because it contains 5. Eg:

SELECT int4range(3, 6) @> int4range(5, 5, '[]');   -- t
SELECT int4range(3, 6) @> int4range(50, 50, '[]'); -- f

Therefore, this exclusion works as I expected:

ALTER TABLE heights_in_feet ADD CONSTRAINT no_overlap 
  EXCLUDE USING gist (int4range("min", "max", '[]') WITH &&);

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.

Oracle Trigger – Insert Values Based on Existence with Check Constraint

If you query the table you're inserting into inside a trigger on that same table, you are likely to get "ORA-04091: table name is mutating, trigger/function may not see it." error. Check constraints cannot contain subqueries. Therefore your best option is to encapsulate logic for inserting data into a stored procedure and use that procedure instead of direct inserts.

I can assist you with writing the procedure, however, it seems to me that you have a logical flaw in your data model. Your example data shows that my_id is not a unique key for the table but rather that many rows may share the same value for my_id. So if one row with my_id = 1 has field_2 set to some value and for some other row field_2 is null, what is to be done? Don't you have a primary key in your table?

Anyways, if you want literally what you wrote in your post, you could do with something like

create or replace procedure my_practice_table_insert(p_my_id in number, p_field_2 in varchar2, p_field_3 in varchar2)
is
    l_cnt number;
begin
    if p_field_2 is null then
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is not null;
    else
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is null;
    end if;

    if l_cnt = 0 then
        insert into my_practice_table(my_id, field_2, field_3) values(p_my_id, p_field_2, p_field_3);
    end if;
end;

I didn't actually try out the procedure so it may have slight errors, but that's the general form it could take.

Best Answer

Use inclusive ranges

Related Solutions

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

Oracle Trigger – Insert Values Based on Existence with Check Constraint

Related Question