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?
asked Jan 16, 2014 at 12:23
2 Answers
As @deszo said user OVER()
create view foo as (
select row_number() over (order by field), field, field2, field3 from bar
)
answered Jan 16, 2014 at 12:48
-
This creates a field as a bigint, How can I create the field as a long int? I need a 32bit ID field. Jan 16, 2014 at 13:56
-
You can cast the row_number to integer when selecting columns from the view Jan 16, 2014 at 14:27
-
1You cast by doing <field>::<type>. Thank for all your help everyone! Jan 16, 2014 at 18:06
-
3Placement of cast operator can be tricky with window functions:
row_number() over (order by field)::intJan 16, 2014 at 20:34
You can use row_number, and the easiest way is to just add a
row_number() OVER ()
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).
Update: Also no need to partition by true, just use () as the "over" expression.
-
1Thanks for saving me another google search. For others' reference, if you need to cast it to a certain type like the OP, you can wrap the whole thing in parentheses and select your type. Ex:
(ROW_NUMBER() OVER (PARTITION BY TRUE))::int4Sep 20, 2022 at 21:14
row_number OVER ()(a window function) for this, if I understand it correctly.