SQL (Postgres): query array Column which must be a subset of an array



  • I have a table "article" and for each entry there are some "tags" stored (array column)

    name   |   tags
    

    test1 | {t1,t3}
    test2 | {t2,t3}
    test3 | {t3}
    test4 | {t1,t5}
    test5 | {t1,t3}
    test6 | {t2,t3}

    I can query for tags which passed as an array:

    SELECT name, tags FROM article WHERE tags && array['t1','t3','t10']::_varchar

    This is working but I actually want to achieve that just entries will be returned when all tags of the entry are in the passed array. (not just 1)

    enter image description here



  • Use the contains operator to check if the tags array contains all elements of the "other" array

    SELECT name, tags 
    FROM article 
    WHERE tags 

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=8d3cac22950f917993b883c9ecb8f4d4




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2