How to create an id in a sequence in postgres sql?-CodePudding

It is the query


insert into xyz_table (id_, column1, column2, 
column3, column4, column5) 
select a.id_, a.column1, a.column2, a.column3, a.column4, a.column5
from xyz_view a;

I need to copy the bulk data from xyz_view to the xyz_table. As of now the view is not having the sequence id generator. I need to provide id_, generated in a sequence for each row I copy from view to the table.

For testing purpose I need to do this only by postgres sql query.

Note: The xyz_view doesn't have the id_ column. I explicitly added these id_column in the query level to provide the id_ for each row in xyz_table sequentially.

If possible, porivde me a working query.

CodePudding user response：

You could use row_number to generated a sequential id

insert into xyz_table (id_, column1, column2, 
column3, column4, column5) 
select row_number() OVER () AS id, a.column1, a.column2, a.column3, a.column4, a.column5
from xyz_view a;