Sample Header Ad - 728x90

How to case text array elements automatically?

0 votes
2 answers
1817 views
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];)
Asked by tinlyx (3820 rep)
Jun 3, 2021, 09:46 PM
Last activity: Sep 21, 2023, 03:55 AM