PostgresQL select row where value是在数组中

Question

我有这个表

CREATE TABLE "public"."test" (
"id" integer NOT NULL,
"game_ids" integer[],
CONSTRAINT "test_pkey" PRIMARY KEY ("id")
);
INSERT INTO "test" ("id", "game_ids") VALUES
(1, '{5,6,7}'),
(2, '{5,12,18}'),
(3, '{12,13,18}'),
(4, '{14,22,23}');

我想查询，以便我得到game_ids列包含值12的所有行。

比如SELECT * FROM test WHERE game_ids CONTAIN 12;

我听说过ANY和@>操作符，但它们似乎不起作用。我在所有这些尝试中得到语法错误:

SELECT * FROM test WHERE ANY game_ids = 12;  // Error in query: ERROR: syntax error at or near "ANY"
SELECT * FROM test WHERE game_ids @> [12]; // Error in query: ERROR: syntax error at or near "["
SELECT * FROM test WHERE game_ids @> 12; // Error in query: ERROR: operator does not exist: integer[] @> integer
SELECT * FROM test WHERE 12 = ANY game_ids; // Error in query: ERROR: syntax error at or near "game_ids"
SELECT * FROM test WHERE game_ids >@ [12]; // Error in query: ERROR: syntax error at or near "["

有人知道我做错了什么吗?

Answer 1

ANY运算符需要括号:

SELECT * FROM test WHERE 12 = ANY( game_ids );

为@>操作符构造数组，使用

SELECT * FROM test WHERE game_ids @> ARRAY[12];
-- or
SELECT * FROM test WHERE game_ids @> '{12}';