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;

Answer 1

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;