Search⌘ K
AI Features
Home
Courses
The Art of PostgreSQL

Searches and User-Defined Tags

Explore how to efficiently index and search user-defined tags using PostgreSQL's intarray extension. This lesson guides you through building dynamic tag search queries with logical operators and retrieving associated metadata, helping you implement advanced tag searches in your applications.

We'll cover the following...

Searches

Let’s see some queries for using the index feature.

Use case: Finding tracks

Now we’re ready for the real magic. Let’s find all the tracks we have that have been tagged as both blues and rhythm and blues:

PostgreSQL
select array_agg(rowid)
  from tags
 where tag = 'blues' or tag = 'rhythm and blues';

That query gives the following result, which might not seem very interesting at first:

 array_agg 
═══════════
 {3,739}
(1 row)

The intarray PostgreSQL extension implements a special kind of query string named query_int. It looks like '(1880&179879)', and it supports the three logic operators not, and, and or that you can combine in your queries.

Concatenation of strings

As we want our tag search queries to be dynamically provided by our users, we’re going to build the query_int string from the tags table itself:

PostgreSQL
select format('(%s)',
              string_agg(rowid::text, '&')
             )::query_int as query
  from tags
 where tag = 'blues' or tag = 'rhythm and blues';

This query uses the format function to build a string for us, here putting our intermediate result inside parentheses. The intermediate result is obtained with string_agg, which aggregates text values together, using a separator between them. Usually, the separator would be a comma or a semicolon. Here we’re preparing a query_int string, and we’re going to search for all the tracks that have been tagged both blues and rhythm and blues ...