0

I'm having a post, each post has tags (varchar[]), and I'm trying to find related ones according to tags. This is why I want to check if all tags of the target post or any of its subsets are contained in the 'tags' arrays of other posts.

I've checked several sources:

Postgres: check if array field contains value?

https://www.postgresql.org/docs/9.1/functions-array.html

According to them, @> should be suitable for this case, however neither

select * from posts where tags @> '{"California", "K-8"}';

nor

select * from posts where tags @> array['California', 'K-8']::varchar[];

work properly: I got an empty result while I have two posts with tags:

{'California','K-8','Legislation','AB77'}, {'California','K-8','Tips & Tricks'}

I would greatly appreciate it if someone could advise a solution to this issue :-)

EDIT: I've figured out what was wrong - the issue was in the format of stored data: after I had changed

{'California','K-8','Tips & Tricks'}

to

{California,K-8,"Tips & Tricks"}

in pgAdmin everything started to work properly.

klin
  • 112,967
  • 15
  • 204
  • 232
Pavel Shepelenko
  • 350
  • 3
  • 17
  • 1
    https://www.db-fiddle.com/f/d6kKrov5orrPHf3KR1zi81/1 appears to works fine here. Could you post some of your data? – Marth Sep 25 '21 at 16:08
  • 'all tags of the target post or any of its subsets are contained in'. Isn't the empty array a subset of every array? Or if you arbitrarily exclude that, then this sounds more like overlap `&&` than contains. – jjanes Sep 25 '21 at 16:24
  • Don't edit the question to add the answer. Answer your own question. That is better to understand, and people may credit your answer. – Laurenz Albe Sep 25 '21 at 20:27

1 Answers1

0

I've figured out what was wrong - the issue was in the format of stored data: after I had changed

{'California','K-8','Tips & Tricks'} to

{California,K-8,"Tips & Tricks"} in pgAdmin everything started to work properly.

Pavel Shepelenko
  • 350
  • 3
  • 17