changing type of column to array Postgresql

Question

I am trying to convert text column to array[text] column in table i have column entry like

['Nikolai Rimsky-Korsakov', 'Jascha Heifetz', 'Arpárd Sándor'] but this is one string or text format I want to convert it into a real array of a string so that I can access a particular name in the above column. I tried converting the type from this link by setting it to type to text[] but the column is just becoming one element of an array like this.

[ "['Nikolai Rimsky-Korsakov', 'Jascha Heifetz', 'Arpárd Sándor']" ]

But what I wanted is to type Array[text] for tat column to able to access particular names.

or is it possible while populating table i can set type of that column as array and it automatically take it as array — anil uchadiya, Apr 01 '21 at 10:08

Julius Tuskenis · Answer 1 · 2021-04-01T13:34:42.677

Section 8.15.2. Array Value Input of PostgreSQL documentation describes the general look of array to be

'{ val1 delim val2 delim ... }'

So you need to trim your '[' and ']' characters and replace them with '{' and '}'.

Then you can cast to text array (text[]) and enjoy the results.

SELECT 
  replace(
    replace(
      '{'||trim(BOTH '[]' FROM test.sample)||'}',
      '\',
      '\\'
    ),
    '"', 
    '\"'  
  )::text[] AS names
FROM
(
  SELECT '[''Nikolai Rimsky-Korsakov'', ''Jascha Heifetz'', ''Arpárd Sándor'', ''Joe "Wingy" Manone'']'::text AS sample
) test

EDIT 2

To handle cases when there " and '' characters in your input we must escape it with \.

SELECT 
  replace(
    replace(
      '{'||trim(BOTH '[]' FROM test.sample)||'}',
      '\',
      '\\'
    ),
    '"', 
    '\"'  
  )::text[] AS names
FROM
(
  SELECT '[''Nikolai Rimsky-Korsakov'', ''Jascha Heifetz'', ''Arpárd Sándor'', ''Joe "Wingy" Manone'']'::text AS sample
) test

EDIT 3

To remove quotes from names:

  SELECT 
    replace( 
      replace(
        replace(
          '{'||trim(BOTH '[]''' FROM test.sample)||'}',
          '\', '\\' ),
        '"', '\"'),
      ''', ''', ',')::text[] AS names
  FROM
  (
    SELECT '[''Nikolai Rimsky-Korsakov'', ''Jascha Heifetz'', ''Arpárd Sándor'', ''Joe "Wingy" Manone'']'::text AS sample
  ) test

its showing error malformed array literal on row ```ERROR: malformed array literal: "{'Bunny Berigan', 'Gene Gifford And His Orchestra', 'Joe "Wingy" Manone'}"``` — anil uchadiya, Apr 01 '21 at 11:31
I tried changing csv file using python script to replace [] with {} and ' with " so replacing single cots to double is needed or not ? — anil uchadiya, Apr 01 '21 at 11:33
error arise due to the nested "Wingy" in there is there any way to handle it? — anil uchadiya, Apr 01 '21 at 11:42

score 1 · Accepted Answer · answered Apr 01 '21 at 11:25

1

Use the function translate() to replace square brackets with curly ones and remove single-quotes:

translate(str, '[]''', '{}')::text[]

See the full example in Db<>fiddle.

answered Apr 01 '21 at 11:25

klin

112,967
15
204
232

I think using translate is too dangerous, because it replaces all occurences of `[`, `]` and `'`. You'll get undesired results with name "Sinéad O'Connor" for example. – Julius Tuskenis Apr 01 '21 at 11:38
The same error arise with your solution it is not able to handle nested "" within names – anil uchadiya Apr 01 '21 at 11:51
@JuliusTuskenis - It is obvious that the actual data format is far from good SQL standards and my answer is ad hoc advice on the simplest possible way based on the data sample provided. The question should contain a representative sample of data, taking into account all special cases. – klin Apr 01 '21 at 21:31

score 0 · Answer 3 · answered Apr 01 '21 at 12:32

0

The problem solved by removing nested square bracket from my CSV file before populating table and that using translate function with little bit of change thank

    alter artist type text[] using translate(artist, '[]"', '{}')::text[] ; ```

answered Apr 01 '21 at 12:32

anil uchadiya

15
7

changing type of column to array Postgresql

3 Answers3