Compare an array to elements of a multi-dimensional array

Question

I have a table in a PostgreSQL 9.6 database:

CREATE TABLE new_table (
 column_name varchar(255)[],
 name        varchar(40)
);

INSERT INTO new_table VALUES
 ('{one, two}'  , 'first_user'),
 ('{other, two}', 'second_user'),
 ('{one, more}' , 'third_user');

I have an array of arrays (2D-array) and want to find all rows of the table where column_name matches any of the contained 1D-array. I'm after something like this:

select * 
from new_table as s
where s.column_name = any('{{"one", "two"}, {"one", "more"}, {"two", "five"}}')

But this gives me the error:

ERROR:  could not find array type for data type text[]

My desired result:

column_name |    name
------------+--------------
{one,two}   | first_user
{one,more}  | third_user

Any one has any idea?

Answer 1

use array operator for work with operators:

t=# select *
from new_table as s
where s.column_name <@ '{{"one", "two"}, {"one", "more"}, {"two", "five"}}';
 column_name |    name
-------------+------------
 {one,two}   | first_user
 {one,more}  | third_user
(2 rows)

I assume https://www.postgresql.org/docs/current/static/functions-comparisons.html#idm46428706346880 could have some notes, that expression is not array and you can't use it for such comparison

update

to be able to "unnest array by 1 dimention" and use the result set for incomarison, use Pavel Stěhule suggested function:

t=# select * from new_table 
where column_name in (
  select reduce_dim('{{one, two}, {two, more}, {one, five}}'::character varying[])
);
 column_name |    name
-------------+------------
 {one,two}   | first_user
(1 row)

Answer 2

Error message

For the record, your superficial error:

ERROR:  could not find array type for data type text[]

.. would go away with an explicit cast. Like:

...
WHERE column_name = any('{{"one", "two"}, {"one", "more"}, {"two", "five"}}'::varchar[])

But that would not solve the underlying problem:

ERROR:  operator does not exist: character varying[] = character varying

Explanation

Postgres support for multi-dimensional arrays can be confusing. In fact, varchar(255)[] resolves to the exact same data type as varchar(255)[][] internally.

• mapping postgresql text[][] type and Java type

The element type of each of these array values is varchar (not varchar[]). The ANY construct effectively ignores array dimensions. The manual:

Array comparisons compare the array contents element-by-element

Solution

To achieve what you want, you need to unnest 2D-arrays to 1D-arrays. Create this function once per database:

CREATE OR REPLACE FUNCTION unnest_2d_1d(ANYARRAY, OUT a ANYARRAY)
  RETURNS SETOF ANYARRAY AS
$func$
BEGIN
   FOREACH a SLICE 1 IN ARRAY $1 LOOP
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql IMMUTABLE STRICT;

Further reading:

• How to unnest a 2d array into a 1d array quickly in PostgreSQL?
• Unnest array by one level

Now, all of these queries work:

SELECT t.*
FROM   unnest_2d_1d('{{"one", "two"}, {"one", "more"}, {"two", "five"}}'::varchar[]) a(column_name)
JOIN   new_table t USING (column_name);

Or:

SELECT t.*
FROM   new_table t
WHERE  column_name = ANY ((SELECT unnest_2d_1d('{{"one", "two"}, {"one", "more"}, {"two", "five"}}'::varchar[])));

Or:

...
WHERE  column_name IN ((SELECT unnest_2d_1d('{{"one", "two"}, {"one", "more"}, {"two", "five"}}'::varchar[])));

dbfiddle here

Related:

• How to use ANY instead of IN in a WHERE clause with Rails?

Asides

This is one of the rare cases where a btree index on an array column might be useful:

CREATE INDEX ON new_table(column_name);

There is nothing special about varchar(255) in Postgres. I mostly just use text. See:

• What is the overhead for varchar(n)?
• Indexes: integer vs string performance if the number of nodes is the same
• Do fixed-width rows improve PostgreSQL read performance?