identity not working with inherited table

Question

I'm using PostgreSQL 11 latest version, having problems with an identity as PK that inherits a table.

assume you have simple parent table like:

CREATE TABLE test7 
    (
    id_t7 int GENERATED always AS IDENTITY PRIMARY KEY,
    folio int GENERATED always AS IDENTITY,
    client int
    );

with any inherited table like:

CREATE TABLE test7_detail1
(
  --  uuid uuid DEFAULT uuid_generate_v4(), <-- fiddle doesn't support it
    in_process boolean,
    id_corte integer,
    ts_captura timestamp(6) without time zone DEFAULT (now())::timestamp without time zone
) INHERITS (test7);

if I try insert like:

insert into test7_detail1 (client,in_process, id_corte)
values (20797,'t',101)

it returns:

ERROR:  null value in column "id_t7" violates not-null constraint
DETAIL:  Failing row contains (null, null, 20797, t, 101, 2019-05-03 22:27:54.823894).

here is the fiddle

what am i doing wrong?
is it a bug?

score 1 · Answer 1 · answered May 04 '19 at 04:51

1

I tried id_t7 serial PRIMARY KEY instead of id_t7 int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY and seems to work.

CREATE TABLE test7 (
  id_t7 serial PRIMARY KEY,
  folio serial,
  client int
);

CREATE TABLE test7_detail1 (
  --  uuid uuid DEFAULT uuid_generate_v4(),
  in_process boolean,
  id_corte integer,
  ts_captura timestamp(6) without time zone DEFAULT (now())::timestamp without time zone
) INHERITS (test7);

insert into test7_detail1 (client,in_process, id_corte)
values (20797,'t',101);

answered May 04 '19 at 04:51

acarlstein

1,799
2
13
21

thanks [acarlstein](https://stackoverflow.com/users/7231449/acarlstein) **so it's a bug?** identity was declared correctly; was folio incremented? – glicuado May 04 '19 at 05:00
I actually test it: https://www.db-fiddle.com/f/iYkASiKbr8t1oEg1U2MCcX/3 – acarlstein May 04 '19 at 07:27
Using PostgreSQL SERIAL To Create Auto-increment Column: http://www.postgresqltutorial.com/postgresql-serial/ – acarlstein May 04 '19 at 07:29
Well, normally its a good practice that each table has its own auto-increment primary identity index. Depending of the query, it can improve performance and its quite standard. Therefore, I am not surprise that the table don't inherit such column from the parent and that you have to define it yourself. – acarlstein May 05 '19 at 12:44
on the one hand it's correct being able to solve it by adding an identity to the inherited one but on the other hand I don't understand why it works with sequences (serial) and not with identities that are also sequences? maybe the correct way to work with identities is by making two inserts in a transaction, it doesn't make sense – glicuado May 06 '19 at 14:59

score 1 · Answer 2 · answered May 27 '21 at 13:47

1

identity column is not inherited

From docs

If a column in the parent table is an identity column, that property is not inherited. A column in the child table can be declared identity column if desired.

answered May 27 '21 at 13:47

Smit Patil

11
1
2

identity not working with inherited table

2 Answers2

Linked