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?

  • 4
    You can use row_number OVER () (a window function) for this, if I understand it correctly. – dezso Jan 16 '14 at 12:36

As @deszo said user OVER()

create view foo as (
    select row_number() over (order by field), field, field2, field3 from bar
| improve this answer | |
  • This creates a field as a bigint, How can I create the field as a long int? I need a 32bit ID field. – code base 5000 Jan 16 '14 at 13:56
  • You can cast the row_number to integer when selecting columns from the view – Fabrizio Mazzoni Jan 16 '14 at 14:27
  • 1
    You cast by doing <field>::<type>. Thank for all your help everyone! – code base 5000 Jan 16 '14 at 18:06
  • 3
    Placement of cast operator can be tricky with window functions: row_number() over (order by field)::int – Erwin Brandstetter Jan 16 '14 at 20:34

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).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.