PostgreSQL с несколькими столбцами, ГДЕ = ЛЮБОЙ (МАССИВ)

#sql #postgresql

Вопрос:

У меня есть таблица с тремя столбцами: «идентификатор», «буква» и «номер». У меня есть список пар «буква» и «число», для которых мне нужно получить «идентификаторы» в одном запросе. Очевидно, что простым решением является использование n запросов, где n — размер списка.

 SELECT id FROM table WHERE number=... AND letter=...

Но для этого требуется n запросов, в моем случае это миллионы, и это большие накладные расходы. Ранее у меня был фильтр только для списка «чисел», поэтому я использовал

 SELECT id FROM table WHERE number = ANY(ARRAY[...])

Есть ли какой-нибудь синтаксис, который делал бы то, что мне нужно, что-то вроде

 SELECT id FROM table WHERE PAIR[letter,number] = ANY(ARRAY[PAIR[...],...])

Спасибо.

Ответ №1:

Вы могли бы использовать массив строк:

 select id from table where (letter, number) = any(array[(l1, n1), (l2, n2), ...])

если вы настроены на использование = any . Возможно, вам потребуется включить множество приведений типов, чтобы убедиться, что все выровнено, поэтому это может быть уродливее, чем присоединение к списку ЗНАЧЕНИЙ.

1. Это не работает, однако проблема может заключаться в том, что вторая строка на самом деле «отличается от символа». Я получаю ОШИБКУ: не удается сравнить разные типы столбцов, различающиеся символами и неизвестные в столбце записи 2

2. Отсюда примечание в конце «Возможно, вам потребуется включить множество приведений типов, чтобы убедиться, что все выровнено». Возможно, вам потребуется сказать что-то вроде (l1::varchar, n1::int) , чтобы получить правильные типы.

Ответ №2:

Хотя вы, безусловно, можете использовать МАССИВ с <@ (is-contained-by) оператором ANY или operator, для этого вам не нужны массивы. JOIN При фиксированных значениях, безусловно, будет выполняться намного быстрее, чем другие варианты.

Рассмотрим следующую test таблицу с данными:

 CREATE TEMP TABLE IF NOT EXISTS 
    test(id SERIAL, letter TEXT, num NUMERIC);

WITH letters AS (
    SELECT chr(generate_series(65, 90)) AS letter
)
,numbers AS (
    SELECT generate_series(101, 999) AS num 
)
INSERT INTO test(letter, num)
SELECT  letter, num 
FROM    letters, numbers

Теперь вы можете добавить свои значения в запрос и выполнить JOIN . Например, следующий запрос находит id из пар (‘A’, 105), (‘B’, 110) и (‘C’, 879):

 SELECT  id
FROM    test T
    JOIN (VALUES    /* your query criteria goes here */
        ('A', 105)
       ,('B', 110)
       ,('C', 879)
    ) AS V(l, n) 
        ON T.letter = V.l AND T.num = V.n

Который возвращает (при условии, что в тестовую таблицу не производилось никаких предварительных записей):

 id  |
----|
   5|
 909|
2577|