Postgres: Querying Arrays

Quering tables that contain arrays can be tricky in postgres.

The Postgres docs: Array functions provide a good explanation but it is good to practically run the queries and see the results.

There is documention about arrays in general from postgres.

Caveats: Array Types

An important thing to note is that arrays being compared need to be exactly the same type. If you don’t an error will be raised.

SELECT ARRAY[1, 2, 3, 4];

This returns an integer[] – an integer array.

If you select the array field from a table, the array may be bigint[] or varchar[] (in pgadmin that is show as character varying []).

For example:

SELECT * FROM catalogue
WHERE catalogue.taxonomy_parents && ARRAY[948, 1039];

ERROR:  operator does not exist: bigint[] && integer[]

HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

The error explains that the comparison cannot be made for bigint[] to integer[].
One would need to explicitly cast the array to the matching type. Casting is done using ::<new type>[] eg. ::varchar[].

SELECT * FROM catalogue
WHERE catalogue.taxonomy_parents && ARRAY[948, 1039]::bigint[];

Basic Postgres Array Querying

In this example arrays will be created in both sides of the comparison. It is important to note that in reality one would be comparing an existing column in a database that contains an array to another.

@> Contains Left

Does the first array contain all elements appearing in the second?

SELECT ARRAY[1,4,3] @> ARRAY[3,1]; -- true

SELECT ARRAY[1,4,3] @> ARRAY[3,1,2]; -- false

<@ Contains Right

Does the second array contain all elements appearing in the first?

SELECT ARRAY[3,1] <@ ARRAY[1,4,3]; -- true

SELECT ARRAY[3,1,2] <@ ARRAY[1,4,3]; -- false

&& Intersection

Do the arrays overlap, that is, have any elements in common?

SELECT ARRAY[4,1] && ARRAY[1,4,3]; -- true

SELECT ARRAY[4,2] && ARRAY[1,3]; -- false

ANY

Do any of the array elements equal this single element?

SELECT 5 = ANY(ARRAY[4,2]); -- false

SELECT 5 = ANY(ARRAY[4,5]); -- true

Note single element must be on the left, this will not work: SELECT ANY(ARRAY[4,2]) = 5;

|| Concatenation

SELECT ARRAY[4,2] || ARRAY[1,3];

Returns {4,2,1,3}