Array overlaps an array in Postgres

Question

I'm making a search by tags function, in a table like this

    CREATE TABLE permission (
      id serial primary key,
      tags varchar(255)[],
    );

Then I add a row that has the tags "artist" and "default".

I'd like to query it by tags (using the knex query builder), so if I do this:

  async getByTags(tags: string[]): Promise<PermissionTable[]> {
    return this.db<PermissionTable>('permission')
      .select('*')
      .whereRaw("tags @> '{??}'", [tags])
  }

That produces the following statements depending on how many tags you passed.

This works

    select * from "permission" where tags @> '{"artist"}';

This doesn't (returns an empty array, when it should the row I'm looking for)

    select * from "permission" where tags @> '{"artist", "event"}';

Why does the one with multiple tags not work?

Using ?? replacement there has SQL injection hole. Those are meant only for passing identifier names with correct quotes to the query. — Mikael Lepistö
– Mikael Lepistö, Commented May 7, 2020 at 9:04

mu is too short · Accepted Answer · 2020-05-07 00:03:26Z

8

@> is the "contains" operator so a1 @> a2 is true when a1 contains all the elements in a2. For example:

array['artist', 'default'] @> array['artist']            -- True
array['artist', 'default'] @> array['default', 'artist'] -- True
array['artist', 'default'] @> array['artist', 'event']   -- False

So @> is checks if the right operand is a set-wise subset of the left operand.

I think you're looking for the "overlaps" operator:

&&
overlap (have elements in common)
ARRAY[1,4,3] && ARRAY[2,1] -- True

This operator checks if the set-wise intersection of the left and right operands is non-empty.

Something like:

.whereRaw("tags && '{??}'", [tags])

should server you better.

answered May 7, 2020 at 0:03

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Collectives™ on Stack Overflow

Array overlaps an array in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related