2

In order to use Unnest function I want convert a list to array.

This is my list of type text. It's an output of this function (How get all positions in a field in PostgreSQL?):

108,109,110,114,115,116,117,156,157,200,201,205

I convert to array with

array[108,109,110,114,115,116,117,156,157,200,201,205]

result is type text[]:

"{"108,109,110,114,115,116,117,156,157,200,201,205"}"

With this kind of array unnest function doesn't work so I think I want convert to array of Int

Thanks

franco_b
  • 868
  • 3
  • 13
  • 31
  • `select concat('{','108,109,110,114,115,116,117,156,157,200,201,205','}')::int[];` [Array Value Input](https://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-INPUT) – Abelisto May 23 '17 at 17:53

2 Answers2

2
with the_data(str) as (
    select '108,109,110,114,115,116,117,156,157,200,201,205'::text
)

select elem
from the_data,
unnest(string_to_array(str, ',')) elem;

 elem 
------
 108
 109
 110
 114
 115
 116
 117
 156
 157
 200
 201
 205
(12 rows)
klin
  • 112,967
  • 15
  • 204
  • 232
1

If I correctly understand, you need this (no necessary convert to INT):

select unnest( string_to_array('108,109,110,114,115,116,117,156,157,200,201,205', ',' ) )
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236