PostgreSQL向带自增ID的表用SELECT插入数据报错解决

Got it, let's work through this problem together. The core issue here is that when you use @GeneratedValue(strategy = GenerationType.SEQUENCE), your database expects the ID to be populated by the configured sequence—but passing NULL directly in your insert statement doesn't trigger the sequence to generate a valid value, hence the non-null constraint error. Here are a few reliable solutions:

1. Omit the ID column entirely from your INSERT statement #

Since the ID is set to auto-generate via a sequence, you don't need to include it in your insert at all. The database will automatically pull the next value from the sequence for each new row:

INSERT INTO T1(dataColumn)
SELECT dataToCopy FROM T2

This is the simplest approach, and it works as long as your T1 table's ID column is properly linked to the sequence managed by JPA.

2. Explicitly call the sequence's next value in your SELECT clause #

If you need to explicitly specify the ID column in your insert (for edge cases), you can directly invoke the sequence's next value function. The syntax varies slightly by database:

• For PostgreSQL/Oracle:

  INSERT INTO T1(id, dataColumn)
  SELECT your_sequence_name.nextval, dataToCopy FROM T2
  

• For SQL Server (using SEQUENCE objects):

  INSERT INTO T1(id, dataColumn)
  SELECT NEXT VALUE FOR your_sequence_name, dataToCopy FROM T2
  

Note: Replace your_sequence_name with the actual sequence name. If you didn't define a custom sequence via @SequenceGenerator, JPA (Hibernate specifically) typically uses hibernate_sequence as the default name.

3. Use JPA's batch persist API instead of raw SQL #

If you prefer to stay within the JPA ecosystem rather than writing native SQL, you can fetch T2 data, map it to T1 entities, and let JPA handle the ID generation:

// Assuming you have Spring Data repositories for both entities
List<T2Entity> t2Records = t2Repository.findAll();
List<T1Entity> t1Records = t2Records.stream()
    .map(t2 -> {
        T1Entity t1 = new T1Entity();
        t1.setDataColumn(t2.getDataToCopy());
        // No need to set ID—JPA will generate it via the sequence
        return t1;
    })
    .collect(Collectors.toList());

t1Repository.saveAll(t1Records);

This method adheres strictly to JPA conventions, but for very large datasets, you'll want to enable JPA batch optimization (e.g., setting hibernate.jdbc.batch_size in your properties) to avoid performance hits.

A quick sanity check: Make sure your sequence exists in the database and that your application has permissions to read from it. If you used JPA to auto-create schema objects, this should already be handled, but it's worth verifying if you run into persistent issues.

内容的提问来源于stack exchange，提问作者Manu