The Art of PostgreSQL/

...

/

Searches and User-Defined Tags

Searches and User-Defined Tags

Learn how advanced tag indexing helps with searching and user-defined tags.

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:

Press + to interact
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:

Press + to interact
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 ...