Can Postgres self-increment when inserting from a select?

Question

PostgreSQL

There is a Temporary Table , let's call it tmp_tbl , which repeats the structure of the table that needs to be updated (let it be tbl1 ), except for the absence of the id field in the tmp_tbl table - it is an auto-increment in tbl1 . When trying to run a query like this:

WITH tmp as (SELECT * FROM tmp_tbl) INSERT INTO tbl1 (SELECT * FROM tmp)

I get this great message:

SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "id" is of type integer but expression is of type character varying HINT: You will need to rewrite or cast the expression.

Question: is it possible to re-tell postgres that it is its responsibility to fill autoincrements?

Answer 1

-- Create a temporary table
   CREATE TEMPORARY TABLE temp_location
   (
      mytable_key    serial primary key,
      id int,
      ...
   ) 
   ON COMMIT DELETE ROWS;

www.sqlines.com/postgresql/statements/create_tempo...
stackoverflow.com/questions/7718585/how-to-set-aut...