How create a table AS SELECT with a serial field

Question

This query already work.

    CREATE TABLE source.road_nodes (
        node_id serial,
        node TEXT
    );

    -- SAVE UNIQUE NODES AND ASIGN ID
    INSERT INTO source.road_nodes (node)
    SELECT DISTINCT  node
    FROM
        (
            (SELECT DISTINCT node_begin AS node
            FROM map.rto)
                UNION
            (SELECT DISTINCT node_end AS node
            FROM map.rto)
        ) as node_pool; 

Im wondering if there is a way to create the table using

CREATE TABLE source.road_nodes AS SELECT ( ... )

Instead of having to create the table and then execute insert.

The thing is how create the serial column.

Answer 1

You can create the table as select:

create table source.road_nodes as
select (row_number() over())::int node_id, node::text
from (  
    select node_begin node from map.rto
    union
    select node_end node from map.rto
    ) sub;

and the data in the table will be as expected, but the column node_id will have no default.

You can, however, manually add the appropriate default post factum:

create sequence road_nodes_node_id_seq;
select setval('road_nodes_node_id_seq', 
    (select node_id from source.road_nodes order by 1 desc limit 1));
alter table source.road_nodes alter node_id set not null; -- not necessary
alter table source.road_nodes alter node_id set default 
    nextval('road_nodes_node_id_seq'::regclass);