0

I use select to some columns see the following example,

 select 55,5,8,3

and the result will be

enter image description here

so, how to convert this into rows and get the row_number of each ? like below

         row.number | col_val
        ------------+--------
              1       55
              2        5
              3        8
              4        3
Improve this question

2 Answers 2

Reset to default
1 
select *, 
       row_number() over ()
from unnest(array[55,5,8,3]);

although it is not guaranteed that the order is always the same (but in reality it is).

With the upcoming 9.4 version you can get a stable row number using the with ordinality option: http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I wish one day on my CV I have Certified 'a horse with no name'
it works but I need array[(select 55,5,8,3)] instead of array[55,5,8,3]
@a_horse: I'm guessing the real problem involves a select from a table, not just a fixed list. unnest((select array[55,5,8,3])) should do it.
0

For this purpose a_horse_with_no_name's answer is enough

or try something like this

select row_number() over() row_num,col_val from (
select unnest((
 select array[c1,c2,c3,c4] from (
select 55  c1,5 c2 ,8 c3,3 c4 )s 
)) as col_val) b

       

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.