psql 15: insert ... select triggering autoincrement where NULL values

Question

I'm trying to "copy/paste" rows of a table with bigserial id column, without listing all columns names in the query. There is a related post https://stackoverflow.com/a/15344327, which does not work with psql. The isses is that psql does not trigger auto increment, when inserting NULL values. The minimal case is

CREATE TABLE src (                                                                                                                                                                                                                            
    id bigserial, 
    txt text NOT NULL);
INSERT INTO src ( 
    txt)          
    VALUES (      
        'a'),     
    (             
        'b'),     
    (             
        'c'),     
    (             
        'b'       
);                
                  
CREATE temp TABLE src_temp AS
SELECT            
    *             
FROM              
    src           
WHERE             
    txt = 'b';    
                  
UPDATE            
    src_temp      
SET               
    id = NULL;    
                  
INSERT INTO src   
SELECT            
    *             
FROM              
    src_temp;

resulting in:

ERROR: null value in column "id" of relation "src" violates not-null constraint Detail: Failing row contains (null, b).

The practical case is that there are way more columns in the src table.

Is there any way to make it?

Answer 1

You need to use correct values in your UPDATE query. To do this you first need to find out the identifier of the sequence that works the magic of your bigserial column. Usually it is tablename underscore columnname underscore 'seq', so for your example it would most likely be src_id_seq. Then you need to modify the UPDATE statement so that it fills in values from this sequence instead of NULL using nextval():

UPDATE            
    src_temp      
SET               
    id = nextval('src_id_seq');  

Now you have valid datasets in src_temp that you can insert into src.

Note that if your tablename or columnname needs quoting, i.e. because they contain uppercase letters, you end up with an identifier for the sequence that also needs quoting like this

nextval('"Table_Column_seq"')

Answer 2

1. Constraints are checked before the update and the autoincrement is triggered.

2. You are explicitly adding NULL to id. The issue is that serial types add NOT NULL to the column definition so that constraint kicks in when the INSERT is attempted. If you want this to work do INSERT INTO src(txt) SELECT txt FROM src_temp;. Then the id column will be populated by the DEFAULT value which is thenextval() of the sequence backing the bigserial type.

CREATE TABLE src (                                                                                                                                                                                                                            
    id bigserial, 
    txt text NOT NULL);
INSERT INTO src ( 
    txt)          
    VALUES (      
        'a'),     
    (             
        'b'),     
    (             
        'c'),     
    (             
        'b'       
);                
                  
CREATE temp TABLE src_temp AS
SELECT            
    *             
FROM              
    src           
WHERE             
    txt = 'b';    
                  
UPDATE            
    src_temp      
SET               
    id = NULL;    
                  
INSERT INTO src(txt)   
SELECT            
    txt
FROM              
    src_temp;
INSERT 0 2

select * from src;
 id | txt 
----+-----
  1 | a
  2 | b
  3 | c
  4 | b
  5 | b
  6 | b