A table holds records that represent a chain (or, more accurate yet, any number of independent chains, respectively).
In addition to referencing the direct ancestor in the chain, a record should also reference the first record inserted, marking the beginning of a particular chain.
- the table is only ever inserted into, the user writing to it does not have
- ideally, the column holding the reference to the first record of a chain would be a self-referential foreign key as well as
- the primary key is a
uuid, hence potential solutions accessing the
latest_value()of a sequence (
serial) do not apply
create table chained_records ( id uuid not null default gen_random_uuid(), -- in the case, where an inserted record *is* the first of a chain, -- this column would reference itself (i.e. the above `id` column) first_in_chain_id uuid not null references chained_records(id), -- snip );
In modern postgres (14), can this be done?
Does a way exist to insert a new immutable record, that references itself, in a single
insert statement, while keeping all constraints in place?