Выбор из массива PostgreSQL

@Воротислав · Регистрация: 25.02.2010

Студворк — интернет-сервис помощи студентам

Добрый день. Необходим совет, как следует поступить. Есть таблица данных. Для каждой строки (ID уникален) в одном столбце требуется хранить несколько Id-шников из другой таблицы. Прочитал, что так можно сделать через массив integer[]. Но в дальнейшем, нужно будет считать все ID и вытащить их имена. нашел способ через:

SQL
1
2
SELECT "nameClient" FROM "client", "testTable"
WHERE public."testTable"."idClient"[2] = "client"."idClient"

Но в таком случае, мне нужно будет индексы руками подставлять ([1], [2] и т.д.). А можно ли сделать так, чтоб запрос был по всем элементам массива. К тому же, изначально не ясно, сколько элементов будет в массиве.

Или же лучше через третью таблицу, где хранить
id_probe1 : idClient1
id_probe1 : idClient2
и т.д.? Заранее благодарен!

@grgdvo · 07.07.2014, 16:14

imho однозначно через третью таблицу. вы выиграете и в гибкости, и в конечном счете в производительности.

@Tatikoma · 07.07.2014, 17:26

В PostgreSQL есть специальные операторы для работы с массивами - ALL, ANY, SOME. Почитайте в документации.

В конкретном случае, если я правильно понял задачу, нужно написать так:

SQL
1
2
SELECT "nameClient" FROM "client", "testTable"
WHERE ANY(public."testTable"."idClient") = "client"."idClient"

Ну и соответственно не забудьте поставить индекс.

@Воротислав · 07.07.2014, 18:23 **[ТС]**

Большое всем спасибо!

@Tatikoma · 07.07.2014, 18:31

Сообщение от grgdvo

imho однозначно через третью таблицу. вы выиграете и в гибкости, и в конечном счете в производительности.

Кстати, насчет производительности вы ошибаетесь.

Когда в постгресе начинаешь делать JOIN третьего уровня, - получаешь интересный эффект с планом запроса из-за того, что оптимизатор не может определить кол-во строк и выполняет seq scan, из-за чего начинаешь в срочном порядке менять на подзапросы, чтобы не менять структуры, но ускорить запрос...

@grgdvo · 08.07.2014, 16:17

и все же повозражаю...

1. Через масивы невозможно сделать ссылочную целостность. А это очень большой плюс (целостность большой плюс). Многие case-средства проектирования предлагают по умолчанию разруливать отношения один-ко-многим и многие-ко-многим (с использованием отдельной таблицы). Конечно ссылочную целостность можно сделать триггерами, но это минус в производительность, и нужно работать руками. Зачем?
2. ALL, ANY, SOME все же ограниченные операторы. Ну и чтобы делать аналитику (групповые и оконные функции), когда приспичит, придется попотеть, чтобы извернуться и составить запрос. Есть некоторые array_* - функции, но их тоже недостаточно. Группировку по элементам массива делать... тоже придется изворачиваться?
3. В конце-концов даже сами разработчики рекомендуют

Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

Tatikoma, Не в тему, конечно, но что такое "JOIN третьего уровня?", что вы понимаете под этим, не встречал такой формулировки? Далее можно подумать и над планом запроса.

@Tatikoma · 08.07.2014, 16:32

1. Никто не спорит. Хотя есть не апррувнутый патчик для постгреса, который реализует FK для массивов. Если очень надо - можно допилить и сделать пулл-риквест в мейнстрим.
2. Глупости. Кто отменял unnest, array_agg ? - Универсальные конверторы массива в строки и строк в массивы. Можете работать так, как вам удобнее.
3. Да, так и есть, не рекомендуется. Нужно использовать с умом.

Сообщение от grgdvo

Не в тему, конечно, но что такое "JOIN третьего уровня?", что вы понимаете под этим, не встречал такой формулировки? Далее можно подумать и над планом запроса.

SQL
1
2
3
4
5
SELECT t1.*
FROM t1
INNER JOIN t2 ON(t1.id = t2.id)
INNER JOIN t3 ON(t2.id2 = t3.id2)
WHERE условие по трем таблицам, так чтобы выбиралось штук 100 строк

Создайте под это таблички, нарисуйте в них по 100к+ строк, сделайте вакуум, откройте план запроса. Потом откройте план запроса убрав последний JOIN. Удивитесь. Да, это корректная логика - иначе сделать нельзя.

Если совсем интересно, - сделайте вместо третьего JOIN'а - подзапрос. Удивитесь еще больше, т.к. в данной ситуации подзапрос кардинально поменяет план запроса и он начнет выполняться мгновенно.

@grgdvo · 13.07.2014, 18:00

Все, что свзано с оптимизацией - всегда интересно. Пробую...

Сообщение от Tatikoma

Если совсем интересно, - сделайте вместо третьего JOIN'а - подзапрос. Удивитесь еще больше, т.к. в данной ситуации подзапрос кардинально поменяет план запроса и он начнет выполняться мгновенно.

С подзапросом можете тоже пример привести. Из предположения, что t1 одна сущность, t2 - другая, tt - их отношение многие ко многим (t1.id1 <---> (id1)tt(id2) <---> t2.id2). Не очень понимаю, куда вы хотите вынести подзапрос? Во from? что это даст, все равно будет join. В Where? Тоже будет join. Для данного случая немного спасают индексы и форсирование indexscan. Выигрышь процентов 10, не больше. Не могли бы Вы вашу версию рассказать.

Новые блоги и статьи Все статьи Все блоги /
Символьное дифференцирование igorrr37 13.02.2026 / * Логарифм записывается как: (x-2)log(x^2+2) - означает логарифм (x^2+2) по основанию (x-2). Унарный минус обозначается как ! */ #include <iostream> #include <stack> #include <cctype>. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .	И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.	«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»
SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL3_image 8Observer8 10.02.2026 Содержание блога Библиотека SDL3_image содержит инструменты для расширенной работы с изображениями. Пошагово создадим проект для загрузки изображения формата PNG с альфа-каналом (с прозрачным. . .	Установка Qt-версии Lazarus IDE в Debian Trixie Xfce volvo 10.02.2026 В общем, достали меня глюки IDE Лазаруса, собранной с использованием набора виджетов Gtk2 (конкретно: если набирать текст в редакторе и вызвать подсказку через Ctrl+Space, то после закрытия окошка. . .

@grgdvo 1264 / 978 / 384 Регистрация: 02.09.2012 Сообщений: 3,021
	07.07.2014, 16:14
	imho однозначно через третью таблицу. вы выиграете и в гибкости, и в конечном счете в производительности. 1

@Воротислав 127 / 105 / 27 Регистрация: 25.02.2010 Сообщений: 451
	07.07.2014, 18:23 [ТС]
	Большое всем спасибо! 0

Выбор из массива PostgreSQL

Решение