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
– anil uchadiya, Commented Apr 1, 2021 at 10:08

klin · Accepted Answer · 2021-04-01 11:25:21Z

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 1, 2021 at 11:25

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Julius Tuskenis Over a year ago

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.

anil uchadiya Over a year ago

The same error arise with your solution it is not able to handle nested "" within names

klin Over a year ago

@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.

Julius Tuskenis · Accepted Answer · 2021-04-01 13:34:42Z

1

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

edited Apr 1, 2021 at 13:34

answered Apr 1, 2021 at 11:00

Julius Tuskenis

1,63010 silver badges15 bronze badges

3 Comments

anil uchadiya Over a year ago

its showing error malformed array literal on row ERROR: malformed array literal: "{'Bunny Berigan', 'Gene Gifford And His Orchestra', 'Joe "Wingy" Manone'}"

anil uchadiya Over a year ago

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 Over a year ago

error arise due to the nested "Wingy" in there is there any way to handle it?

anil uchadiya · Accepted Answer · 2021-04-01 12:32:11Z

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 1, 2021 at 12:32

anil uchadiya

151 silver badge9 bronze badges

Collectives™ on Stack Overflow

changing type of column to array Postgresql

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related