create a temp table with serial/auto increment column

Question

Good Day I am trying to create a temp table with a auto increment column. I have tried various versions of the following, but i am not getting it to work.

create temp table aatemp1 as (id serial, (select unnest (string_to_array('388c45f-998d-4d9c-b59a-bd37b70bba7a', '-'))));

Answer 1

You can't combine "static" column definitions and a select like that.

You need to do this in two steps:

create temp table aatemp1 
(
  id serial, 
  some_value text
);

insert into aatemp1(some_value)
select unnest (string_to_array('388c45f-998d-4d9c-b59a-bd37b70bba7a', '-'));

if you just want to have a sequential number in the temp table, you could also do it like this:

create temp table aatemp1
as
select row_number() over (order by null) as id, 
       t.*
from (
   select unnest (string_to_array('388c45f-998d-4d9c-b59a-bd37b70bba7a', '-'))
) t;

(but that would not generate "new" ids when you insert more rows)