How to autogenerate ID during sql INSERT?

Question

INSERT INTO table_having_pk SELECT * FROM table_without_pk;

Schema of both tables are the same, only the primary key constraints are not set for the table_without_pk.

Problem: during copy the entries of the 2nd table have null in ID, thus inserts into first table fails.

org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: 23502
org.hibernate.engine.jdbc.spi.SqlExceptionHelper: ERROR: NULL-Value in Column ?id? violates Not-Null-Constraint

How can I let the 1st table autogenerate the IDs (just count them up) during insert?

Postgres 9.x

Inside my @Entity class the ID is generated as follows for the table_having_pk:

  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private long id;

Version numbers for Postgres include the digit after the dot: http://www.postgresql.org/support/versioning/ — Erwin Brandstetter, Apr 02 '15 at 14:02

score 0 · Answer 1 · answered Apr 02 '15 at 13:56

0

create table twpk(a text);

insert into twpk values('a'), ('b'), ('c');

create temporary sequence mysequence;

create table twpk2 as
select nextval('mysequence'), a from twpk;

select * from twpk2

answered Apr 02 '15 at 13:56

percy

988
9
9

Could you alsow explain your solution? Such answers are stronger, and more likely to get up-voted. – Magnilex Apr 02 '15 at 16:16

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

Just omit the id column and it will be generated automatically for a serial column.
You need to add a column list with all columns except the id column:

INSERT INTO table_having_pk (col1, col2, ...)
SELECT col1, col2, ... FROM table_without_pk;

If your column does not have a default from a sequence (like a serial would), you can also drop the id column from both tables and:

INSERT INTO table_having_pk
SELECT * FROM table_without_pk;

Then add a serial column:

ALTER TABLE table_having_pk ADD COLUMN table_having_pk_id serial PRIMARY KEY;

Numbers are auto-generated, so this will take a moment. Every row is rewritten.

Or you generate the numbers on the fly, if you don't want a serial (or can't have one):

INSERT INTO table_having_pk (id, col1, col2, ...)
SELECT row_number() OVER (), col1, col2, ...
FROM table_without_pk;

edited May 23 '17 at 11:50

Community

1
1

answered Apr 02 '15 at 13:57

Erwin Brandstetter

605,456
145
1,078
1,228

Would it have the same effect if I remove the `id` column from `table_without_pk` and then write again `insert into table_having_pk select *...`? – membersound Apr 02 '15 at 14:01
I cannot make the id colum `serial` as I have to maintain the hibernate auto generated sequence for not breaking the java code. Could I maybe `update` the `id` column within `table_without_pk` based on the sequence? – membersound Apr 02 '15 at 14:02
@membersound: I suspect that *is* implemented as `serial` in Postgres. – Erwin Brandstetter Apr 02 '15 at 14:03

How to autogenerate ID during sql INSERT?

2 Answers2