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
– RealSkeptic, Commented Apr 5, 2015 at 7:02
thank you @RealSkeptic, i want to now how many times "london" appears in all rows — Mostafa Jamareh
– Mostafa Jamareh, Commented Apr 5, 2015 at 7:04
And is it possible that it appears more than once in a single row? — RealSkeptic
– RealSkeptic, Commented Apr 5, 2015 at 7:06
And how do you implement this constraint? stackoverflow.com/questions/8016776/… — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 5, 2015 at 8:16

RealSkeptic · Accepted Answer · 2015-04-05 07:21:37Z

2

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.

answered Apr 5, 2015 at 7:21

RealSkeptic

34.8k7 gold badges56 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:28:25Z

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, 2017 at 12:28

CommunityBot

11 silver badge

answered Apr 5, 2015 at 8:26

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

2 Comments

Mostafa Jamareh Over a year ago

it gives me array value must start with "{" or dimension information

Erwin Brandstetter Over a year ago

@MostafaJamareh: Sorry, syntax error; fixed. <@ expects an array on both sides.

Collectives™ on Stack Overflow

How to count rows where an array column contains an element

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related