How to count rows where an array column contains an element

Question

I have a table that has a column city[], so I have:

1 city [london]
2 city [london,paris]

How can I count the city london in my table?

Do you want to count how many rows contain "london" or how many times "london" appears in one row? — RealSkeptic, Apr 05 '15 at 07:02
thank you @RealSkeptic, i want to now how many times "london" appears in all rows — Mostafa Jamareh, Apr 05 '15 at 07:04
And is it possible that it appears more than once in a single row? — RealSkeptic, Apr 05 '15 at 07:06
And how do you implement this constraint? http://stackoverflow.com/questions/8016776/can-postgresql-have-a-uniqueness-constraint-on-array-elements/8017013#8017013 — Erwin Brandstetter, Apr 05 '15 at 08:16

score 2 · Accepted Answer · answered Apr 05 '15 at 07:21

To count all the rows in a PostgreSQL table where a certain value appears anywhere in an array, you use the ANY function:

SELECT COUNT(*) FROM cities WHERE 'london' = ANY( city );

(This is assuming the table is called cities).

The predicate 'london' = ANY( city ) means "Any element in the array city is equal to 'london'". This select the rows that match "london", and then counts them.

score 2 · Answer 2 · edited May 23 '17 at 12:28

2

If the table is not trivially small you'll want to have a GIN index on the array column. You'll want to use array operators instead of the = ANY () construct to actually use this index:

SELECT count(*) FROM cities WHERE '{london}'::text[] <@ city;

Details:

Check if value exists in Postgres array

edited May 23 '17 at 12:28

Community

1
1

answered Apr 05 '15 at 08:26

Erwin Brandstetter

605,456
145
1,078
1,228

it gives me array value must start with "{" or dimension information – Mostafa Jamareh Apr 05 '15 at 09:58
@MostafaJamareh: Sorry, syntax error; fixed. `<@` expects an *array* on both sides. – Erwin Brandstetter Apr 05 '15 at 10:08

How to count rows where an array column contains an element

2 Answers2