Pivotal Knowledge Base

Follow

Query that filter a column with array datatype errors with "ERROR: cannot cast type text[] to text" text"

Environment

Product Version
Pivotal Greenplum All versions

Symptom

The column is defined as array.

flightdata=# \d pg_roles
                View "pg_catalog.pg_roles"
      Column       |           Type           | Modifiers
-------------------+--------------------------+-----------
[....]
 rolconnlimit      | integer                  |
 rolpassword       | text                     |
 rolvaliduntil     | timestamp with time zone |
 rolconfig         | text[]                   |
[.....]
[.....]

Here the user is using the column (that is defined as array) to search for a specification that results in the below error message.

flightdata=# select * from pg_roles where rolconfig like '%optimizer%on%' ;
ERROR:  operator does not exist: text[] ~~ unknown
LINE 1: select * from pg_roles where rolconfig like '%optimizer%on%'...
                                               ^
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.

If user attempts to cast the column to text or char, it ends up with the below error message:

flightdata=# select * from pg_roles where rolconfig::text like '%optimizer%on%' ;
ERROR:  cannot cast type text[] to text
LINE 1: select * from pg_roles where rolconfig::text like '%optimize...

Resolution

Use array_to_string functions to convert the array to string, for example;

select * from pg_roles where array_to_string(rolconfig, ',') like '%optimizer%on%' ;

Comments

Powered by Zendesk