Re: How to represent a bi-directional list in db?

Francisco Olarte <folarte(at)peoplecall(dot)com> writes:

> On Sun, Sep 22, 2019 at 4:25 PM Pankaj Jangid <pankaj(dot)jangid(at)gmail(dot)com> wrote:
>> CREATE TABLE stages (
>> id SERIAL PRIMARY KEY,
>> name VARCHAR(80) NOT NULL,
>> created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
>> updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
>> prev_stage_id SERIAL REFERENCES stages NULL,
>> next_stage_id SERIAL REFERENCES stages NULL,
>> process_id SERIAL REFERENCES processes NOT NULL
>> );
>> Failed with: conflicting NULL/NOT NULL declarations for column
>> "prev_stage_id" of table "stages"
>> Is it not possible to create "nullable" self referencing foreign keys?
>
> Serial seems wrong. It means integer, not null, defaul next value from
> a sequence.
>
> What you probably want is just "prev_stage_id INTEGER" ( NULL by
> default ), as you do not want the prev/next stage ids to be generated,
> you normally would want to assign values from other tuples.
>

Thanks. This resolved my problem of NULL/NOT NULL conflict. I wasn't
aware that SERIAL is by default NOT NULL.

> Also, you may have problems populating this kind of table, as you will
> not have the ids from either prev or next stage when building it.
>

If NULL value is allowed I can fill it up with NULL initially. Right? Or
is there something wrong here.

> And lastly, in SQL you do not really need a doubly linked list, just
> populate prev_stage_id, and index it and you can query next stage of a
> tuple using it.
>

Could you please elaborate? Suppose I have this table,

CREATE TABLE stages (
id SERIAL PRIMARY KEY,
name VARCHAR(80) NOT NULL,
next_id INTEGER REFERENCE stages NULL,
);

What would be the backward query in that case? Forward is clear. This is
forward query,

SELECT name FROM stages WHERE next_id = 123;

--
Pankaj Jangid