Constraint on a group of rows

Question

For a simple example, let's say I have a list table and a list_entry table:

CREATE TABLE list 
(
    id SERIAL PRIMARY KEY,
);

CREATE TABLE list_entry 
(
    id SERIAL PRIMARY KEY,
    list_id INTEGER NOT NULL
        REFERENCES list(id)
        ON DELETE CASCADE,
    position INTEGER NOT NULL,
    value TEXT NOT NULL,

    CONSTRAINT list_entry__position_in_list_unique
        UNIQUE(list_id, position)
);

I now want to add the following constraint: all list entries with the same list_id have position entries that form a contiguous sequence starting at 1.

And I have no idea how.

I first thought about EXCLUDE constraints, but that seems to lead nowhere.

Could of course create a trigger, but I'd prefer not to, if at all possible.

Answer 1

You can use a generated column to reference the previous number in the list, essentially building a linked list. This works in Postgres:

create table list_entry 
(
  pos integer not null primary key,
  val text not null,
  
  prev_pos integer not null
    references list_entry (pos)
    generated always as (greatest(0, pos-1)) stored
);

In this implementation, the first item (pos=0) points to itself.

Answer 2

You can't do that with a constraint - you would need to implement the logic in code (e.g. using triggers, stored procedures, application code, etc.)

Answer 3

I'm not aware of such way to use constraints. Normally a trigger would be the most straightforward choice, but in case you want to avoid using them, try to get the current position number for the list_entry with the list_id you're about to insert, e.g. inserting a list_entry with list_id = 1:

INSERT INTO list_entry (list_id,position,value) VALUES 
(1,(SELECT coalesce(max(position),0)+1 FROM list_entry WHERE list_id = 1),42);

Demo: db<>fiddle