SQLAlchemy/PostgreSQL: Change column type from ARRAY to JSON

Question

I am trying to change the type of one of the columns on my table from one that takes arrays filled with strings to one that takes JSON. The SQL I'm trying to execute looks like:

ALTER TABLE my_table
ALTER COLUMN my_column TYPE JSON USING my_column::json

But I get an error back saying "cannot cast type character varying[] to json". The column I'm trying to change is empty, there are no rows so there is no data that needs to be cast to JSON. Since it's empty I've thought of dropping the column and remaking it but I'd like to keep the column and just change its type if possible. I'm not a whizz with PostgreSQL so any nudge in the right direction would be appreciated.

select array_to_json(null::varchar[]); NULL

Adrian Klaver
– Adrian Klaver

2023-02-08 16:18:42 +00:00
Commented Feb 8, 2023 at 16:18 — Adrian Klaver
– Adrian Klaver, Commented Feb 8, 2023 at 16:18

Adrian Klaver · Accepted Answer · 2023-02-08 16:21:33Z

 array_test 
                      Table "public.array_test"
    Column     |        Type         | Collation | Nullable | Default 
---------------+---------------------+-----------+----------+---------
 id            | integer             |           |          | 
 array_fld     | integer[]           |           |          | 
 numeric_array | numeric[]           |           |          | 
 jsonb_array   | jsonb[]             |           |          | 
 varchar_array | character varying[] |           |          | 
 text_array    | text[]              |           |          | 

ALTER TABLE array_test
    ALTER COLUMN varchar_array TYPE json
    USING array_to_json(varchar_array);

\d array_test 
                 Table "public.array_test"
    Column     |   Type    | Collation | Nullable | Default 
---------------+-----------+-----------+----------+---------
 id            | integer   |           |          | 
 array_fld     | integer[] |           |          | 
 numeric_array | numeric[] |           |          | 
 jsonb_array   | jsonb[]   |           |          | 
 varchar_array | json      |           |          | 
 text_array    | text[]    |           |          |

Collectives™ on Stack Overflow

SQLAlchemy/PostgreSQL: Change column type from ARRAY to JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related