Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
You can use row_number OVER () (a window function) for this, if I understand it correctly. – dezso Jan 16 '14 at 12:36
up vote 5 down vote accepted

As @deszo said user OVER()

create view foo as (
    select row_number() over (order by field), field, field2, field3 from bar
share|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. – josh1234 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
You cast by doing <field>::<type>. Thank for all your help everyone! – josh1234 Jan 16 '14 at 18:06
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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