Conversion array types

Question

I have in table column, which type is CHARACTER VARYING[] (that is array)

I need concatenate existed rows whith other array

This is my code:

UPDATE my_table SET col = array_cat(col, ARRAY['5','6','7'])    

returned error: function array_cat(character varying[], text[]) does not exist

Reason error is that array types not matches right?

Question: how to convert this array ARRAY['5','6','7'] as CHARACTER VARYING[] type ?

Answer 1

Cast to varchar[]:

 > SELECT ARRAY['5','6','7']::varchar[], pg_typeof( ARRAY['5','6','7']::varchar[] );   SELECT ARRAY['5','6','7']::varchar[], pg_typeof( ARRAY['5','6','7']::varchar[] );   array  |      pg_typeof       ---------+---------------------  {5,6,7} | character varying[] 

You can use the PostgreSQL specific ::varchar[] or the standard CAST(colname AS varchar[])... though as arrays are not consistent across database implementations there won't be much advantage to using the standard syntax.