Oracle to PostgreSQL query conversion with string_to_array()

Question

I have below query in Oracle:

SELECT to_number(a.v_VALUE), b.v_VALUE
       FROM TABLE(inv_fn_splitondelimiter('12;5;25;10',';')) a
       JOIN TABLE(inv_fn_splitondelimiter('10;20;;', ';')) b
         ON a.v_idx = b.v_idx

which give me result like:

I want to convert the query to Postgres. I have tried a query like:

SELECT UNNEST(String_To_Array('10;20;',';'))

I have also tried:

SELECT a,b
       FROM (select  UNNEST(String_To_Array('12;5;25;10;2',';'))) a
       LEFT JOIN (select  UNNEST(String_To_Array('12;5;25;10',';'))) b
         ON a = b

But didn't get a correct result.
I don't know how to write query that's fully equivalent to the Oracle version. Anyone?

Answer 1

In the expression select a the a is not a column, but the name of the table alias. Consequently that expressions selects a complete row-tuple (albeit with just a single column), not a single column.

You need to define proper column aliases for the derived tables. It is also recommended to use set returning functions only in the from clause, not in the select list.

If you are not on 9.4 you need to generate the "index" using a window function. If you are on 9.4 then Erwin's answer is much better.

SELECT a.v_value, b.v_value
FROM (
   select row_number() over () as idx,  -- generate an index for each element
          i as v_value
   from UNNEST(String_To_Array('12;5;25;10;2',';')) i
) as a 
  JOIN (
     select row_number() over() as idx, 
            i as v_value
     from UNNEST(String_To_Array('10;20;;',';')) i
  ) as b 
  ON a.idx = b.idx;

An alternative way in 9.4 would be to use the with ordinality option to generate the row index in case you do need the index value:

select a.v_value, b.v_value
from regexp_split_to_table('12;5;25;10;2',';') with ordinality as a(v_value, idx)
  left join regexp_split_to_table('10;20;;',';') with ordinality as b(v_value, idx) 
    on a.idx = b.idx