Question 113784 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 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;
    Login or Signup to reply.
  2. 0

    Simply do

    ALTER TABLE my_table
ALTER COLUMN country TYPE TEXT[]
USING string_to_array(country,'');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search