How to change column type in PostgreSQL from text to array and cast only non-null values?-CodePudding

I have a table in PostgreSQL:

| id | country| type |
| 1  | USA    | FOO  |
| 2  | null   | BAR  |

I want to change the column type for the country column from text to array and cast to the new type only non-null values to have the table look as follows:

| id | country | type |
| 1  | {USA}   | FOO  |
| 2  | null    | BAR  |

So far, I have come up with this expression that casts any value to the array. So for the 2nd row, I have an array with a null value.

ALTER TABLE my_table
ALTER COLUMN country TYPE TEXT[]
USING ARRAY[country];

How can I use the USING expression to cast only not null values?

CodePudding user response：

You can use a CASE expression

ALTER TABLE my_table
  ALTER COLUMN country TYPE TEXT[]
  USING case 
          when country is null then null 
          else ARRAY[country] 
        end;

CodePudding user response：

Simply do

ALTER TABLE my_table
ALTER COLUMN country TYPE TEXT[]
USING string_to_array(country,'');