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

Question

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.

If the id column doesn't exist in your view and it is mapped to a serial type in your table, you could exclude it from your insert query and let the database take care of auto incrementing it. — ashu
– ashu, Commented Oct 22, 2021 at 23:03

nbk · Accepted Answer · 2021-10-22 22:51:40Z

2

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;

answered Oct 22, 2021 at 22:51

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ashu Over a year ago

...or if the id column is a serial, you could exclude it from your insert queries and let the database do its job..?

nbk Over a year ago

yes, but it seems that the user wants one per insert

Pent Terry Over a year ago

Thank you guys, it worked. could you explain me what it is doing?

nbk Over a year ago

it makes exactly what you ask for, for every row in the view it add its row number, which marks its position on the result set. it is art if th window function and has a number of more posibilities

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related