Skip to content

Instantly share code, notes, and snippets.

@zmts
Last active June 23, 2023 15:16
Show Gist options
  • Save zmts/854f80c45b4e85fd7a14ec19ef2bc26c to your computer and use it in GitHub Desktop.
Save zmts/854f80c45b4e85fd7a14ec19ef2bc26c to your computer and use it in GitHub Desktop.
SQL: select rows where some id in array

SQL: select rows where some id in array

CREATE TABLE "public"."items" (
    "id" int4 NOT NULL DEFAULT nextval('item_id_seq'::regclass),
    "name" text,
    "has_access" _int4 NOT NULL DEFAULT '{}'::integer[],
    PRIMARY KEY ("id")
);
id name has_access
1 dogs {1,2,3}
2 cats {1,2}
3 food {1,2}
4 cars {1,2,3}
5 movies {1}
6 books {1}
7 papper {1}

Select rows where some id in array

SELECT * FROM items
WHERE (2 = ANY (has_access))
ORDER BY id;
id name has_access
1 dogs {1,2,3}
2 cats {1,2}
3 food {1,2}
4 cars {1,2,3}

Select rows where some id in array and some id not in array

SELECT * FROM items
WHERE (2 = ANY (has_access))
AND NOT (3 = ANY (has_access))
ORDER BY id;
id name has_access
2 cats {1,2}
3 food {1,2}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment