0

According to PostgreSQL documentation on the ARRAY constructor:

By default, the array element type is the common type of the member expressions, determined using the same rules as for UNION or CASE constructs (see Section 10.5). You can override this by explicitly casting the array constructor to the desired type, for example:

SELECT ARRAY[1,2,22.7]::integer[];
  array
----------
 {1,2,23}
(1 row)

This works for converting elements to integers, and for simple types:

SELECT ARRAY[ 'name', 1]::text[];
  array   
----------
 {name,1}
(1 row)

But the casting does not seem to work for complex elements such as array elements:

SELECT ARRAY[ 'name', 1, ARRAY['world']]::text[];
ERROR:  malformed array literal: "name"
LINE 1: SELECT ARRAY[ 'name', 1, ARRAY['world']]::text[];
                      ^
DETAIL:  Array value must start with "{" or dimension information.

My question is:

Is there a way to make automatic conversion work for text[]?

(i.e. make the above conversion work without having to write SELECT ARRAY[ 'name'::text, 1::text, ARRAY['world']::text];)

1
  • Not only does this fail with array constructors mixing arrays and scalar values as elements, but also with array constructors whose elements are all arrays but of different dimensions. These two requirements – that an array constructor must produce a rectangular array, and that the elements must be either all scalar or all arrays – are probably checked before applying the type casting.
    – Andriy M
    Commented Jun 3, 2021 at 22:58

2 Answers 2

0

As of PostgreSQL 14, it seems that automatic type conversion does not work if it has an array element. The array element has to be explicitly converted to text:

SELECT ARRAY[ 'name', 1, ARRAY['world']::text]::text[];
0

1. Custom a convert function

You can create a convert function that is used to cast text[] to integer[].

CREATE OR REPLACE FUNCTION convert_text_array_to_int_array(text[])
RETURNS integer[] AS $$
SELECT ARRAY(SELECT CAST(unnest($1) AS integer));
$$ LANGUAGE SQL IMMUTABLE;

Let's test this function:

psql# select pg_typeof(convert_text_array_to_int_array(array['123','456']::text[]));
 pg_typeof
-----------
 integer[]

2. Alter table column with the custom function.

Let's assume that we have a column named as response_codes text[].

Now we need to alter its type to integer[] as following steps.

-- drop the old default typed value(important!!!!)
ALTER TABLE monitor_apis ALTER COLUMN response_codes DROP DEFAULT;

-- Alter table column with the convert function above.
ALTER TABLE monitor_apis ALTER COLUMN response_codes TYPE integer[] USING convert_text_array_to_int_array(response_codes);

-- reset its default typed value.
ALTER TABLE monitor_apis ALTER COLUMN response_codes SET DEFAULT '{}'::integer[];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.