postgresql change character varying to character varying array

Question

below I have two columns in table. they both say they are character varying but zoning_description has an array symbol after it. I need to change the zoning column to match the zoning_description column type. how do I accomplish this.

Also what is the difference between these two column types... I am running into an error later in a different process because of this problem

Answer 1

just alter type with casting, eg your table:

t=# create table z as select '{R}'::varchar zoning, '{"Blah-ah ah"}'::varchar[] zoning_description;
SELECT 1
t=# select * from z;
 zoning | zoning_description
--------+--------------------
 {R}    | {"Blah-ah ah"}
(1 row)

alter type:

t=# alter table z alter column zoning type varchar[] using zoning::varchar[];
ALTER TABLE
t=# select * from z;
 zoning | zoning_description
--------+--------------------
 {R}    | {"Blah-ah ah"}
(1 row)

checking columns:

t=# \d z
                            Table "postgres.z"
       Column       |        Type         | Collation | Nullable | Default
--------------------+---------------------+-----------+----------+---------
 zoning             | character varying[] |           |          |
 zoning_description | character varying[] |           |          |

Answer 2

It looks like zoning_description is an array of VARCHAR values, while zoning is just a VARCHAR. If you want to convert zoning to be the same data type as zoning_description, you can just do:

SELECT ARRAY[zoning]

This will CAST it as an array data type.

Working query

SELECT 
    MyVarchar, 
    PG_TYPEOF(MyVarchar),  -- character varying
    MyVarcharArray, 
    PG_TYPEOF(MyVarcharArray),  -- character varying[]
    ARRAY[MyVarchar], 
    PG_TYPEOF(ARRAY[MyVarchar])  -- character varying[]
FROM (
    SELECT 
    CAST('rammstein' AS VARCHAR) AS MyVarchar, 
    ARRAY[CAST('du hast' AS VARCHAR)] AS MyVarcharArray
) src