current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
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.
up vote 1 down vote favorite

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
4  
You can use row_number OVER () (a window function) for this, if I understand it correctly. –  dezso Jan 16 '14 at 12:36

1 Answer 1

up vote 4 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
1  
You cast by doing <field>::<type>. Thank for all your help everyone! –  josh1234 Jan 16 '14 at 18:06
1  
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

 
discard

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.