2

I have 3 tables as follows:

    CREATE TABLE A (
    ID integer PRIMARY KEY generated always as identity,
    data integer
);

CREATE TABLE B (
     other_data integer

) INHERITS (A);

CREATE TABLE C (
     other_other_data integer
) INHERITS (A);

My intent is to have unique id for tables B and C so they don't mix up. When trying this approach and inserting data into B (insert into B (other_data) values (1)) i get the following error:

ERROR:  null value in column "id" of relation "B" violates not-null constraint 
DETAIL:  Failing row contains (null)

I suspect this approach is not the correct way to make 2 tables share unique ids. And Postgres documentation really sucks. I tried using serial for a long time. Only to find out that buried in the wiki is a warning to not use them.

-edit: added the insert statement

Dorknob78
  • 43
  • 4
  • Add to your question the `INSERT` query you are using for table B. There is nothing inherently wrong with `serial` it just that in newer versions of Postgres `Identity` is available and is easier to work with. Both use `sequence` behind the scene. – Adrian Klaver Jul 15 '22 at 16:59
  • Is there a reason to not just use FOREIGN KEYs from tables `b` and `c` to table `a`? – Adrian Klaver Jul 15 '22 at 17:06
  • I personally don't use `INHERITS` since I don't understand it well. I just create all three tables and add the foreign keys as needed. – The Impaler Jul 15 '22 at 18:01
  • `id serial PRIMARY KEY` instead of `id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY` works https://stackoverflow.com/a/55979552/1291521 – Roman Zaykovsky Nov 18 '22 at 11:59

0 Answers0