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

Question

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?

Pepe N O · Accepted Answer · 2022-11-11 21:57:29Z

2

Simply do

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

answered Nov 11, 2022 at 21:57

Pepe N O

2,3841 gold badge12 silver badges17 bronze badges

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

Comments

user330315 · Accepted Answer · 2022-11-11 21:54:09Z

0

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;

answered Nov 11, 2022 at 21:54

user330315

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related