1

There is a PG table example with two fk columns table_a_id, table_b_id and position::int column. It's guaranteed there will be only one column filled table_a_id or table_b_id.

CREATE TABLE table_a(
   id INT GENERATED ALWAYS AS IDENTITY,
   PRIMARY KEY(id)
);

CREATE TABLE table_b(
   id INT GENERATED ALWAYS AS IDENTITY,
   PRIMARY KEY(id)
);

CREATE TABLE example(
   table_a_id INT,
   table_b_id INT,
   position INT,
   CONSTRAINT fk_table_a
      FOREIGN KEY(table_a_id) 
          REFERENCES table_a(id),
   CONSTRAINT fk_table_b
      FOREIGN KEY(table_b_id) 
          REFERENCES table_a(id)
);

I want position column to be incremented every time new record is being created. But value for incrementing must be picked based on which column is currently filled. I considered creating two sequences owned by table_a_id and table_b_id but I have no guess how to set default value for position based on these sequences. Probably it'd be something like

CREATE SEQUENCE serial_a OWNED BY example.table_a_id;
CREATE SEQUENCE serial_b OWNED BY example.table_b_id;

ALTER TABLE example ALTER COLUMN position SET DEFAULT 
  nextval('serial_a' IF table_a_id IS NOT NULL) ||  
  nextval('serial_b' IF table_b_id IS NOT NULL);

but I dont know how to express it correcly.

Maybe I'm looking in the wrong direction. The result should be like that:

table_a_id table_a_id position
1 1
2 2
1 1
2 2
3 3
3 3
4 4
4 4
1
  • Not possible. You need a trigger for that
    – user1822
    Commented Aug 25, 2022 at 12:05

1 Answer 1

0

From a data model perspective this seems a bit wierd, but perhaps you have good reasons for it. From your example it appears as if one of the columns is always null and the other one has a value. You can then add a generated column that picks its value from the non-null column:

CREATE TABLE example(
   table_a_id INT,
   table_b_id INT,
   position INT generated always as ( least(table_a_id, table_b_id)) stored ,
   CONSTRAINT fk_table_a
      FOREIGN KEY(table_a_id) 
          REFERENCES table_a(id),
   CONSTRAINT fk_table_b
      FOREIGN KEY(table_b_id) 
          REFERENCES table_a(id)
);

Fiddle

2
  • Thanks for your suggestion, but unfortunately it doesn't fit to my particular case since table_*_id is actually UUID. ID::int was introduced for simplification sake.
    – mzsrn
    Commented Aug 26, 2022 at 8:00
  • I suggest you make a new question without oversimplifications Commented Aug 26, 2022 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.