I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N.
Is this possible to do without effecting the original schema of the legacy data structure?
As @deszo said user OVER()
create view foo as ( select row_number() over (order by field), field, field2, field3 from bar )
You can use row_number, and the easiest way is to just add a
row_number() OVER (PARTITION BY true)
field into the view. You need the PARTITION BY and using the "true" expression is the most performant way (no need for sorting like in Fabrizio Mazzoni's answer).