Conversion Oracle SQL to PostgreSQL

Question

I have a problem to convert a script from Oracle to Postgres:

WITH q AS (
    SELECT 'ABCDE' str
    FROM dual)
SELECT 'TEST' AS WL_ID
    ,substr(str, LEVEL, 1) AS SUPPLY_MODE_ID
FROM q connect BY LEVEL < length(q.str) + 1;

I tried this in Postgres:

WITH RECURSIVE t(n) 
    AS (SELECT 'TEST', substring('ABCDE', 1, 1)
        UNION ALL
        SELECT 'TEST', substring('ABCDE', n+1,1)
        FROM t 
        where n<length('ABCDE')
)
SELECT * FROM t

I have the error :

ERROR: operator does not exist: text + integer

I don't know how to return exactly the same result as in Oracle.

You don't have any values defined for `n` in the postgress CTE, as per documentation, [**here**](http://www.postgresql.org/docs/9.1/static/queries-with.html). — Radu Gheorghiu, Jul 21 '15 at 07:15
It would be nice if you showed what the result from Oracle is. — Craig Ringer, Jul 22 '15 at 04:58

score 0 · Answer 1 · answered Sep 06 '18 at 09:09

This is a lot easier with Postgres:

select 'TEST' as wl_id, t.*
from unnest(string_to_array('ABCDE', null)) as t(supply_mode_id)

If you want to make sure the rows are guaranteed to be shown in the order the characters are in the string, you need an order by:

select 'TEST' as wl_id, t.supply_mode_id
from unnest(string_to_array('ABCDE', null)) with ordinality as t(supply_mode_id, idx)
order by t.idx;

Conversion Oracle SQL to PostgreSQL

1 Answers1