PostgreSQL Features You May Not Have Tried but Should

Re:PostgreSQL Features You May Not Have Tried but Should by blattimwind on Wednesday June 20, 2018 @06:15PM (#17360324)

I don't feel like tags are a particularly good example. For example, how do you efficiently get a list of all tags and how many X there are per tag? Easy for a relation (SELECT tags.name, COUNT(posts) FROM tags NATURAL JOIN posts GROUP BY tags.name), not so easy for an array (SELECT x.tag, COUNT(posts) FROM (SELECT DISTINCT tag FROM unnest(posts.tags)) AS x JOIN posts ON posts.tags @> [x.tag] GROUP BY x.tag).

[ Parent | on HN ]

Re:PostgreSQL Features You May Not Have Tried but Should by Inviz on Wednesday June 20, 2018 @08:42PM (#17361158)

select region, count(*) from projects, unnest(region_ids) as region GROUP BY 1;

[ Parent | on HN ]

Re:PostgreSQL Features You May Not Have Tried but Should by blattimwind on Thursday June 21, 2018 @07:02PM (#17369953)

The issue (iirc) with that sort of query is that it's essentially a join, so you'll get count(projects)*sum(length(regions)) intermediate rows that need to be grouped back together.

[ Parent | on HN ]