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

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?

Solution

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"')