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 ?

score 50 · Accepted Answer · answered May 28 '13 at 08:21

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.

Conversion array types

1 Answers1