Dividing sql results in lines through delimiter

Question

Good afternoon, you guys. I have a hard time making a select on a DB postgres here.

For some reason, the programmer invented to create a field where if the user input two notes at the same time, the system saves in the bank only one line for that release but separating the two notes by a comma in the recn_numerodcto field.

Therefore, if I run select below in the bank,

 select  recn_data, recn_unid, recn_numerodcto from recnota

The result will be:

recn_data | recn_unid | recn_numerodcto

25-09-2018 | 001 | 123, 321

Is there a way for me to break this result to generate a line for each note? Ex:

recn_data | recn_unid | recn_numerodcto

25-09-2018 | 001 | 123

25-09-2018 | 001 | 321

Answer 1

Use the function combination string_to_array with the function unnest.

string_to_array: converts the reported string in the first parameter to a array separating the elements according to the delimiter given in the second parameter.

unnest: Expands an array to a set of rows.

select
    recn_data, 
    recn_unid, 
    unnest(string_to_array(recn_numerodcto,','))
from recnota

I added some records to the sample database:

insert into recnota (recn_data,recn_unid,recn_numerodcto)
values
    ('2018-09-25','001','123,321'),
    ('2018-09-26','001','1234,4321');

Running the first code will return:

┌────────────┬───────────┬────────┐
│ recn_data  │ recn_unid │ unnest │
├────────────┼───────────┼────────┤
│ 2018-09-25 │ 001       │ 123    │
│ 2018-09-25 │ 001       │ 321    │
│ 2018-09-26 │ 001       │ 1234   │
│ 2018-09-26 │ 001       │ 4321   │
└────────────┴───────────┴────────┘
(4 registros)

https://www.postgresql.org/docs/10/static/functions-array.html