Писать и выполнять прямой sql по возможности

Вьюшкой например

Во время джойна появляются дубликаты записей, я делаю .distinct, затем суммирую значения по столбцу так .sum :count в итоге я получаю такой sql SELECT DISTINCT SUM(count) AS count_id FROM .... и соответственно не правильное значение, то есть все дубликаты записей берет, подскажите пожалуйста, как можно это сделать? исключить дубликаты и потом суммировать оставшиеся записи. rails

Исправьте запрос, да и всё. А вообще, DISTINCT в запросе —- почти 100% признак того, что кто-то пытается "замести под ковёр" предыдущую ошибку. ;)

Исправьте запрос, да и всё. А вообще, DISTINCT в запросе —- почти 100% признак того, что кто-то пытается "замести под ковёр" предыдущую ошибку. ;)

как правильно написать?

как правильно написать?

Вы же почти ничего не показали... Откуда у вас дубликаты-то берутся?

есть таблица Owners и есть Addresses у Owners есть address_id по сути я хотел бы такой запрос SELECT SUM(count) From addresses INNER JOIN owners .....

есть таблица Owners и есть Addresses у Owners есть address_id по сути я хотел бы такой запрос SELECT SUM(count) From addresses INNER JOIN owners .....

Вы бы \d таблиц показали... А что такое SUM(count), по сути?

Насоветуйте ER тул для постгре.

Да, отдельная сущность. А еще есть комментарий к телефону. И нужно будет связвать с юриками и физиками

Когда у вас в таблице 100500*n записей, то выгоднее, с точки зрения оптимизации, телефон держать не в отдельной сущности, а в денормализованном виде

Во время джойна появляются дубликаты записей, я делаю .distinct, затем суммирую значения по столбцу так .sum :count в итоге я получаю такой sql SELECT DISTINCT SUM(count) AS count_id FROM .... и соответственно не правильное значение, то есть все дубликаты записей берет, подскажите пожалуйста, как можно это сделать? исключить дубликаты и потом суммировать оставшиеся записи. rails

Какие то джоины в вашем случае необходимы только для проверки факта существования такой связи, но так как содержат не одну запись, то так ВЫ и получаете: накручиваете не только дубликаты, но и сложность исполнения запроса, завышение использования шаренных буферов, увеличение времени работы CPU. Вам такие джоины необходимо просто в exists переместить

Когда у вас в таблице 100500*n записей, то выгоднее, с точки зрения оптимизации, телефон держать не в отдельной сущности, а в денормализованном виде

Для каких-то запросов, может быть, и выгоднее (а другие пострадают, почти наверняка). Но просто от количества записей это вряд ли зависит.

это тонкая материя: баланс между нормализацией данных и денормализацией) сперва при проектировании стараются максимально все нормализовать, максимально растащить по отдельным таблицам... потом когда приходит что кроме телефона надо хранить еще емейл, два емейла, данные телеги, данные секретаря, еще данные секретаря...еще телефоны и телефоны не проще ли просто заложить под эти данные jsonb поле в том же контрагенте?

я просто не понимаю зачем телефоны хранить в отдельной таблице) адреса да, т.к. по адресам есть ФИАС, есть КЛАДР, это удобно

хотя у вас может телефоны это основной вид деятельности, тогда да, спорить не буду)

это тонкая материя: баланс между нормализацией данных и денормализацией) сперва при проектировании стараются максимально все нормализовать, максимально растащить по отдельным таблицам... потом когда приходит что кроме телефона надо хранить еще емейл, два емейла, данные телеги, данные секретаря, еще данные секретаря...еще телефоны и телефоны не проще ли просто заложить под эти данные jsonb поле в том же контрагенте?

Обычно это не вопрос баланса, а только вопрос оптимизации. Т.е. _логически_ модель должна быть нормализована. > что кроме телефона надо хранить еще емейл, два емейла, данные телеги, данные секретаря, еще данные секретаря...еще телефоны и телефоны Обычно добавляются соответствующие таблицы. > не проще ли просто заложить под эти данные jsonb поле в том же контрагенте? По-моему, не проще. Чтобы обеспечить consistence этого "поля", придётся приложить немало дополнительных усилий.

я просто не понимаю зачем телефоны хранить в отдельной таблице) адреса да, т.к. по адресам есть ФИАС, есть КЛАДР, это удобно

Потому что такова реляционная модель, которую в общем стоит использовать для моделирования в реляционных СУБД. ;)

вам не страшно так мыслить на стадии планирования архитектуры бд, если у вас записей не больше 100тысяч-)

вам не страшно так мыслить на стадии планирования архитектуры бд, если у вас записей не больше 100тысяч-)

Хмм... как "так"?

что все используют первую/вторую/треюю нормальную форму)

что все используют первую/вторую/треюю нормальную форму)

Да, вообще это, эээ... нормально. ;) И четвёртую, и пятую (даже шестую, иногда).

Реляционная она не потому что там необходимо использовать эти формы

использование/не использование их это ваше право, но вы на больших объемах данных начнете деградировать на сложных выборках

Обычно это не вопрос баланса, а только вопрос оптимизации. Т.е. _логически_ модель должна быть нормализована. > что кроме телефона надо хранить еще емейл, два емейла, данные телеги, данные секретаря, еще данные секретаря...еще телефоны и телефоны Обычно добавляются соответствующие таблицы. > не проще ли просто заложить под эти данные jsonb поле в том же контрагенте? По-моему, не проще. Чтобы обеспечить consistence этого "поля", придётся приложить немало дополнительных усилий.

да я про оптимизацию. приходит время в проект, когда от нее многое зависит. и приходится довольстоваться тем что уже заложено в архитектуру БД. а там все максимально разбито по отдельным сущностям.

вы устанете на каджый чих заводить таблицу...точнее устаните в них разбираться потом

телефон это как раз один из таких чихов

Реляционная она не потому что там необходимо использовать эти формы

Да, не поэтому. Но если СУБД не находится в высшей достижимой форме, в ней уже возможны (и, по моему опыту, скорее всего, будут) аномалии в данных. И это (должно быть, иначе зачем было использовать RDBMS) куда важнее какой-то там производительности.

использование/не использование их это ваше право, но вы на больших объемах данных начнете деградировать на сложных выборках

А, по-моему, наоборот.

в постгресе давно уже используют возможности нереляционных моделей баз данных, причем даже обходият по скорострельности, вам лучше про них немного почитать

поле JSONB ооочень интересное)

да я про оптимизацию. приходит время в проект, когда от нее многое зависит. и приходится довольстоваться тем что уже заложено в архитектуру БД. а там все максимально разбито по отдельным сущностям.

Суть-то не в разбиении, а в корректном отражении ограничений...

все ограничения это нормально настроенные триггеры, с ними не так сложно разобраться

вы устанете на каджый чих заводить таблицу...точнее устаните в них разбираться потом

Да пока удаётся... мне кажется, так даже понятнее / логичнее.

плюс констрейны

Ярослав, вам успехов в проектировании бд, видимо оптимизация в вашем случае противопоказана)

или в ней нет необходимости

в постгресе давно уже используют возможности нереляционных моделей баз данных, причем даже обходият по скорострельности, вам лучше про них немного почитать

Да причём тут "скорострельность"... Это сравнение яблок с апельсинами, вот в чём дело. Т.е. JSONb без дополнительных телодвижений —- это отсутствие всяких проверок, и сравнивать с реляционной моделью такую реализацию "в лоб" некорректно.

все ограничения это нормально настроенные триггеры, с ними не так сложно разобраться

А зачем мне триггеры в тех случаях, когда результата можно добиться встроенными средствами?

в jsonb вы не сможете поместить два одинаковых телефона например) он поймет автоматом что это тоже самое

Ярослав, вам успехов в проектировании бд, видимо оптимизация в вашем случае противопоказана)

Денормализация "вслепую" (не под конкретные запросы, а из соображений вроде "телефон это как раз один из таких чихов") —- это отличный способ ДЕоптимизации, IMHO. ;)

в jsonb вы не сможете поместить два одинаковых телефона например) он поймет автоматом что это тоже самое

Да, но проблема в том, что так же "смело" я туда помещу вообще любой мусор, разве нет?

Ярослав, еще планировщик умеет нормально просчитывать план выполнения запроса только для первых ЭНЦАТЬ (10 или 8, смотря что в настройках стоит) если вы в одном запросе используете много джоинов, а фильтр прийдет по 11му джоину, то вы получите полный seqscan всех первых 10 таблиц и только потом отфильтруете по 11й вот это не забывайте) а по jsonb:

по сути это такая же таблица) что и ваши телефоны, если вы в свою таблицу не хотите добавлять новых колонок, и никому этого не дадите, то муссор не появится.)

но что плохого в муссоре) в этом как раз и вся прелесть)

Ярослав, еще планировщик умеет нормально просчитывать план выполнения запроса только для первых ЭНЦАТЬ (10 или 8, смотря что в настройках стоит) если вы в одном запросе используете много джоинов, а фильтр прийдет по 11му джоину, то вы получите полный seqscan всех первых 10 таблиц и только потом отфильтруете по 11й вот это не забывайте) а по jsonb:

Вы про from_collapse_limit/ join_collapse_limit (я обычно ставлю где-то 12, кстати)? Если да, то они работают совсем не так вот тупо: > то вы получите полный seqscan всех первых 10 таблиц и только потом отфильтруете по 11й

по сути это такая же таблица) что и ваши телефоны, если вы в свою таблицу не хотите добавлять новых колонок, и никому этого не дадите, то муссор не появится.)

А как я "никому этого не дам"? Для базы-то JSONb поле (без constraint-ов) —- немногим более, чем обычный BLOB. Т.е. пиши что хочешь, да и ладно. ;) В отличие от реляционной модели, где поле —- это в любом случае какой-то domain (хотя бы и базовый).

у меня был один коллапс по этому join_collapse_limit, только пример был сложнее: там по первой из джоина таблицы тоже было условие в where, и по 11й, планировщик стал плясать от первой, потому что в настройках было 10 но это было совсем неудачно) т.к. по 11й был однозначно единственный вариант

но что плохого в муссоре) в этом как раз и вся прелесть)

Я не вижу в мусоре никакой прелести, т.к. СУБД, по идее, должны использоваться для хранения каких-то важных данных, а не для мусора. ;)

в нашем случае апдейтов вставок инсертов по отношению к выборке не так много, поэтому в нашем случае перекладывают некоторые расчеты на момент сохранения, что бы потом выборки летали пользователь не заметит если у него даже на 1 секкунду будет дольше сохранятся....но зато потом эти данные потом гораздо быстрее можно открыть на просмотр, анализировать, использовать в расчетах, выгрузках данных у вас скорее всего другие приоритеты: сделать бд максимально понятной архитектору бд, (возможно пользователи ваши тоже видят вживую эту бд и работают с ней напрямую)

>в нашем случае апдейтов вставок инсертов по отношению к выборке не так много это в процентном соотношении)

Почему телефоны в отдельной таблице, а не в JSON Допустим: У Вас есть свой телефон и телефон мамы. Вы сообщили о контакте оба. Мама ваша (ничего про нее плохого не имею ввиду), указала свой телефон и Ваш, а еще Вашего брата. Брат ваш указала и сваой и в Мамы и Ваш телефон. Нужно: Пойнять что телефоны сходятся у нескольких пользователей. Нужно поссчитать количество быстро посчитать количество таких связей. В базе 1 000 000 контактов. У каждого указано по 3-4 телефона.

в нашем случае апдейтов вставок инсертов по отношению к выборке не так много, поэтому в нашем случае перекладывают некоторые расчеты на момент сохранения, что бы потом выборки летали пользователь не заметит если у него даже на 1 секкунду будет дольше сохранятся....но зато потом эти данные потом гораздо быстрее можно открыть на просмотр, анализировать, использовать в расчетах, выгрузках данных у вас скорее всего другие приоритеты: сделать бд максимально понятной архитектору бд, (возможно пользователи ваши тоже видят вживую эту бд и работают с ней напрямую)

> в нашем случае перекладывают некоторые расчеты на момент сохранения, что бы потом выборки летали Перекладывать _расчёты_ —- т.е. вычислять _производные_ данные на основании корректных —- это совсем другое дело. По сути, это такая же "денормализация", как, например, индексация. ;) Логически в этом случае модель нормализована, все constraint-ы поддерживаются (и если выкинуть все производные данные, ничего, кроме производительности, не изменится). А нарушение нормальных форм —- это "выкидывание" каких-то ограничений. > у вас скорее всего другие приоритеты Да, у меня приоритет —- корректность хранимых данных.

Почему телефоны в отдельной таблице, а не в JSON Допустим: У Вас есть свой телефон и телефон мамы. Вы сообщили о контакте оба. Мама ваша (ничего про нее плохого не имею ввиду), указала свой телефон и Ваш, а еще Вашего брата. Брат ваш указала и сваой и в Мамы и Ваш телефон. Нужно: Пойнять что телефоны сходятся у нескольких пользователей. Нужно поссчитать количество быстро посчитать количество таких связей. В базе 1 000 000 контактов. У каждого указано по 3-4 телефона.

Еще один кейс. Каждый номер, как я и говорил, отмечается комментарием. А также еть понятие "основной номер". Со временем добавляется понятие "номера в черном списке"

Почему телефоны в отдельной таблице, а не в JSON Допустим: У Вас есть свой телефон и телефон мамы. Вы сообщили о контакте оба. Мама ваша (ничего про нее плохого не имею ввиду), указала свой телефон и Ваш, а еще Вашего брата. Брат ваш указала и сваой и в Мамы и Ваш телефон. Нужно: Пойнять что телефоны сходятся у нескольких пользователей. Нужно поссчитать количество быстро посчитать количество таких связей. В базе 1 000 000 контактов. У каждого указано по 3-4 телефона.

И в чём проблема для обычной модели? Или вы про JSON?

Я про то, что в JSON это контрпродуктивно хранить

кейс интересный)

Не юзайте json, если можете нормально нормализовать. Не ленитесь. Json для неструктурированных данных

Я про то, что в JSON это контрпродуктивно хранить

Более того, в JSON вообще в большинстве случаев контрпродуктивно хранить. Продуктивно в случае, если этот самый JSON(b) —- это такой "почти что blob". ;)

Не юзайте json, если можете нормально нормализовать. Не ленитесь. Json для неструктурированных данных

я тоже такого мнения. Отсюда и перенос телефона в отдельную таблицу

в постгресе давно уже используют возможности нереляционных моделей баз данных, причем даже обходият по скорострельности, вам лучше про них немного почитать

Я про начало этого диалога и то, почему таблицу вынес отдельно

если у вас действительно такие кейсы то вы правы, я просто хотел предостеречь что на каждый чих не стоит заводить отдельные таблицы) в вашем случае телефоны это фундамент бизнеса)

но кейс этот инетересен и с точки зрения jsonb)

используйте номер телефона как ключ jsonb) значение : подмассив где есть имя коменты и тп

Далее мое личное мнение Любые данные фундамент бизнеса. В итогом виде они либо отнимают время на перестройки механизмов и написание костылей по выборке. Либо сильно экономят время и позволяют сделать оптимизацию, исключив некоторые выборки из бэкенда. Я не могу представить ни одного кейса, когда бы нормаизация была бы неприемлемой. Денормализованные данные буду т приводить к избыточности алгоритмов, индексов и непрозрачности с точки зрения для тестирования. Единственный кейс, когда это приемлемо - долговременное хранение данных (типа логов) с поведением insert + select + delete

если у вас действительно такие кейсы то вы правы, я просто хотел предостеречь что на каждый чих не стоит заводить отдельные таблицы) в вашем случае телефоны это фундамент бизнеса)

А даже если не "фундамент", зачем ослаблять контроль? Цель какая? Я понимаю, если вы столкнулись с техническими ограничениями СУБД, которые не позволяют выполнить нормализацию, или эти телефоны каким-то образом создают проблемы с производительностью, и не очень важны (что уже странно)...

Но изначально вопрос стоял несколько иначе ? "есть ли возможность повестить consraint на родительскую таблицу и чтобы при добавлениии записи в дочернюю (inherits) он не ругался что запись не найдена.?

Но изначально вопрос стоял несколько иначе ? "есть ли возможность повестить consraint на родительскую таблицу и чтобы при добавлениии записи в дочернюю (inherits) он не ругался что запись не найдена.?

И ответ "нет, это не работает". ;) Мой вам совет —- лучше не используйте наследование вообще (ни для чего, кроме партиционирования, и всяких "хитрых трюков").

Цель: Нужен единый id для сделок. Есть ряд таблиц ссылающихся на одну и ту же таблицу и нужно контроллировать целостность (например телефоны ссылкаются на контакты,а это юрлица и физ лица, каждые со своими полями.)

тут я уже немного запутался, inherits подразумевает что у вас все таки связь 1 к 1му, т.е. сущност КОНТРАГЕНТ_А может существовать только в одной записи и быть напрмиер дополненной в наследуемой таблице, если это есть, верно? т.е. если вы телефон выделяете в наследуемую таблицу, то вы не сможете две записи приравнять одному контрагенту

поправьте меня если я ошибаюсь)

Цель: Нужен единый id для сделок. Есть ряд таблиц ссылающихся на одну и ту же таблицу и нужно контроллировать целостность (например телефоны ссылкаются на контакты,а это юрлица и физ лица, каждые со своими полями.)

Ну, есть модель "общего предка" (забыл, как называется). Но поддерживать целостность в ней тоже не сахар (для полной реализации). :( Т.е. идея в том, что есть таблица "лица" (контрагенты), на которую уже ссылаются таблицы юр. лиц и физ. лиц., т.е. одна сущность представлена "частями" в _двух_ таблицах. Соответственно, таблицы, которые должны ссылаться на контрагентов вообще, ссылаются на таблицу контрагентов. Те же, которые должны ссылаться только, например, на юр.лиц, имеют FK на таблицу юр.лиц. (и т.п.).

ну тут норм)

пока все логично)

привязываете телефон к головной таблице)

на "лица" привязываете телефоны и все там уже не надо ломать голову физ лицо это или юр лицо

И нет. Для constraint оказывается: pk родительской таблицы! = pk дочерней

т.е. один телефон может быть как у физ лица так и у фирмы

пусть будет проблем не вижу)

пусть будет проблем не вижу)

Ругается при вставке телефона. Говорит что нет такого pk в родителькой. Если insert был вставлен в дочернюю

в общем вы чтото начудили в целостности данных

И нет. Для constraint оказывается: pk родительской таблицы! = pk дочерней

Ну да, такая вот реализация inherits. Планов по исправлению я что-то не помню / не видел. Вообще, мне кажется, всё это наследование в PostgreSQL пришло из времён увлечения "объектно-реляционными СУБД" (что бы это ни значило). Т.к. "не взлетело", улучшения в самом механизме сейчас никому не интересны (насколько мне известно).

в общем вы чтото начудили в целостности данных

Да нет там никакого чуда, это так и работает, к сожалению.

у вас телефоны на какую таблицу как дочернюю ссылаются? Лица, физ лицо, юр лицо?

в общем в вашей модели не может телефон существовать без данных Юр лица/физ лица/лица вы вставили только телефон

сделали бы свои телефоны обычной таблицей и FK на эту таблицу и все)

т.е. вам надо отвязать наследование

Чуть позже опишу еще один кейс. Может и в самом деле чтото не так делаю. Пока в дороге.

пока у вас телефоны как наследуемая таблица, то вы не сможете на одно лицо повесить два телефона

можно еще использовать generic foreign key - хранить 2 колонки с чистом(id другой таблицы) и типом(таблицу, из какой тащить данные)

Чуть позже опишу еще один кейс. Может и в самом деле чтото не так делаю. Пока в дороге.

Да всё вы "так" делаете, просто это НЕ РАБОТАЕТ. См. https://www.postgresql.org/docs/current/static/ddl-inherit.html#DDL-INHERIT-CAVEATS А именно: Specifying that another table's column REFERENCES cities(name) would allow the other table to contain city names, but not capital names. There is no good workaround for this case.

тогда телефон в отдельной таблице и его можно привязать к любой сущности

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

как пример реализации партицирования до 10й версии, да можно

опять же если там через наследование выводят телефон в отдеьлную таблицу, то смысл только в экономии места, но в этом смысла точно нет, когда усложняется система я бы телефон отдельной колонкой и зранил в лицах)

опять же если там через наследование выводят телефон в отдеьлную таблицу, то смысл только в экономии места, но в этом смысла точно нет, когда усложняется система я бы телефон отдельной колонкой и зранил в лицах)

Скину сюда через полтора часа ddl, предметно обсудим)