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

Francisco Olarte <folarte@peoplecall.com> writes:

> On Sun, Sep 22, 2019 at 4:25 PM Pankaj Jangid <pankaj.jangid@gmail.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