Array: could not find array type for data type character varying[]

Question

I have the following sample data for demo:

Table:

create table tbl_array
(
   array_data varchar[]
);

Some values:

insert into tbl_array values('{AUS,USA}'),('{IND,SA}'),('{UK,UAE,NZ}'),('{CAN,BAN,SL,KW}');

Query: I have input values {USA,AUS} or {KW,CAN,SL,BAN} or {UK,UAE,NZ} to get details from tbl_array. Input values can come with any sequence.

Expected Output:

For {USA,AUS}:

array_data
-------------
{AUS,USA}

For {KW,CAN,SL,BAN}:

array_data
-------------
{CAN,BAN,SL,KW}

For {UK,UAE,NZ}:

array_data
-------------
{UK,UAE,NZ}

Try:

select *
from tbl_array where array_data = ALL('{USA,AUS}');

Getting an error:

could not find array type for data type character varying[]

Answer 1

Try this:

select * from tbl_array where array_data @> '{USA,AUS}' AND array_length(array_data, 1) = 2;

It must contain both (ignores order) and be of length 2 (to exclude other cases if you wish).

array_length(array_data, 1) the 1 means your array is one dimensional.

I also assume you have no duplicates in your arrays.

Also note that array contains @> can benefit from GIN indexes.