CODEWITHSUNDEEP
postgresqlconstraintsauto-increment
n1000
n1000

Reputation: 5314

Copying rows violates not-null constraint in PostgreSQL

I am trying to do what is described in this solution and also here. That means I would like to copy rows with many columns while changing only a few values. So my query looks like this:

CREATE TEMPORARY TABLE temp_table AS 
    SELECT * FROM original_table WHERE <conditions>;

UPDATE temp_table 
    SET <auto_inc_field>=NULL, 
        <fieldx>=<valuex>,
        <fieldy>=<valuey>;

INSERT INTO original_table SELECT * FROM temporary_table;

However, the <auto_inc_field>=NULL part is not working for me, respectively my PostgreSQL 9.4 database:

Exception: null value in column "auto_inc_field" violates not-null constraint

The <auto_inc_field> column is defined as BIGINT, SERIAL, and has a primary key constraint.

What do I need to pass, if NULL is not working? Is there an alternative method?

Upvotes: 0

Views: 469

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

I understand that the primary key is a serial. List all columns but the primary key in the insert command. List the correspondent columns and values in the select command:

insert into original_table (col_1, col_2, col_3)
select col_1, value_2, value_2
from original_table
where the_conditions;

Upvotes: 1

Related Questions