оборачиваешь в cte, делаешь из него select и там order by

так это к выборке возвращаемся. с этим ок. В приведенной конструкции селекта нет => order by тоже.

или же не ждать от идентификатора того что он будет говорить о каком-либо порядке

Не ждать и не будет

Гарантирует ли постгрес то, что при вставке множества значений Insert into table (...) values (...), (...)... returning "Id"; Он вернет id вставленных в том порядке, в котором они перечислены?

а этот запроос правда вернет для каждой вставленной записи свой Id?

а этот запроос правда вернет для каждой вставленной записи свой Id?

Вернет те столбцы вставленных, которые укажешь, как select

@Somezed что называется, решай сам)) https://www.mail-archive.com/pgsql-hackers@postgresql.org/msg253739.html

Привет пацаны. А можете подсказать, хочу сделать две таблицы, одна из таблиц наследует вторую, у обоих есть поле(одно и то же) с констрой unique. Как сделать так, чтобы гарантировалась уникальность в рамках двух таблиц, а не каждой по отдельности? Пока понимаю, что никак, кроме навешивания триггера - который не всегда будет срабатывать из-за того, что он видит только закоммиченные данные при вставке.

Привет пацаны. А можете подсказать, хочу сделать две таблицы, одна из таблиц наследует вторую, у обоих есть поле(одно и то же) с констрой unique. Как сделать так, чтобы гарантировалась уникальность в рамках двух таблиц, а не каждой по отдельности? Пока понимаю, что никак, кроме навешивания триггера - который не всегда будет срабатывать из-за того, что он видит только закоммиченные данные при вставке.

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

Привет пацаны. А можете подсказать, хочу сделать две таблицы, одна из таблиц наследует вторую, у обоих есть поле(одно и то же) с констрой unique. Как сделать так, чтобы гарантировалась уникальность в рамках двух таблиц, а не каждой по отдельности? Пока понимаю, что никак, кроме навешивания триггера - который не всегда будет срабатывать из-за того, что он видит только закоммиченные данные при вставке.

А почему, кстати, триггер не будет срабатывать?

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

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

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

Это вам кажется, что оно хорошо ложится. Ограничения PostgreSQL в этом отношении всё равно не дадут вам на его основании сделать что-то более-менее сложное. Лучше бросьте, пока ещё не глубоко не влезли...

А почему, кстати, триггер не будет срабатывать?

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

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

Хорошо. Но ведь физическая модель не обязана соответствовать логической модели. Наиболее эффективный способ реализации наследования в физической модели - это слияние сущностей в одну таблицу с полем типа.

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

А, да, но только для низких уровней изоляции. Но с ними вообще приходится долбаться вместо того, чтобы просто писать, IMHO... ;)

Хорошо. Но ведь физическая модель не обязана соответствовать логической модели. Наиболее эффективный способ реализации наследования в физической модели - это слияние сущностей в одну таблицу с полем типа.

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

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

Это не так. Есть виды (view).

Это не так. Есть виды (view).

А - ну да.

Хорошая идея с вьюхами, надо проработать

Хорошо. Но ведь физическая модель не обязана соответствовать логической модели. Наиболее эффективный способ реализации наследования в физической модели - это слияние сущностей в одну таблицу с полем типа.

Почему это наболее эффективный способ? Мне вот, например, не нравится (не то что бы альтернативные прямо счастье, но этот вообще смешивает данные с метаданными... ну фу ;) ).

Хорошая идея с вьюхами, надо проработать

Если доводить идею до логического конца, то надо определить инварианты на физическом уровне с помощью check contraint. Это тем сложнее, чем больше типов сущностей может хранить таблица.

Почему это наболее эффективный способ? Мне вот, например, не нравится (не то что бы альтернативные прямо счастье, но этот вообще смешивает данные с метаданными... ну фу ;) ).

Потому что данные хранятся в одной таблице.

Потому что данные хранятся в одной таблице.

А, в плане только физической эффективности, понятно (и, кстати, не факт). И, кстати, за счёт консистентности (если не обвешать кучкой триггеров).

Если доводить идею до логического конца, то надо определить инварианты на физическом уровне с помощью check contraint. Это тем сложнее, чем больше типов сущностей может хранить таблица.

больше двух оно хранить не будет.

А, в плане только физической эффективности, понятно (и, кстати, не факт). И, кстати, за счёт консистентности (если не обвешать кучкой триггеров).

В плане консистентности см. про инварианты.

больше двух оно хранить не будет.

Ну тогда совсем не сложно будет.

В плане консистентности см. про инварианты.

А как помогают инварианты? Можете на конкретном примере? А то вот я недавно пытался искать модели на данную тему... и мне прямо все чем-то не нравятся. :(

Ну тогда совсем не сложно будет.

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

Спасибо

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

А вы не забыли, что при изменениях структуры вам эти view-ы и триггеры придётся модифицировать?

А вы не забыли, что при изменениях структуры вам эти view-ы и триггеры придётся модифицировать?

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

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

Хмм... а ссылаться вы на них как будете? Или в вашей задаче это не важно?

Хмм... а ссылаться вы на них как будете? Или в вашей задаче это не важно?

Что понимается под ссылаться? Не очень понял. Я пока только еще думаю над решением, и не продумал все тонкости

А как помогают инварианты? Можете на конкретном примере? А то вот я недавно пытался искать модели на данную тему... и мне прямо все чем-то не нравятся. :(

Помогают обеспечить гарантию целосности. Конкретный пример - таблица "персона" хранит данные, общие для всех персон (фио), специфические данные мужчин и специфические данные женщин. В этой таблице есть поле типа, которое при определении инварианта входит в check constraint - если тип "м", то все поля типа "ж" обязаны быть null. И наоборот.

Что понимается под ссылаться? Не очень понял. Я пока только еще думаю над решением, и не продумал все тонкости

Лучше бы на каком-то конкретном примере разбирать, мне кажется (потому что возможные зависимости могут быть важны). Я имел в виду, что может быть нужно гарантировать корректность ссылок на ваши таблицы из других, в том случае, если какие-то могут ссылаться только на "наследников".

Лучше бы на каком-то конкретном примере разбирать, мне кажется (потому что возможные зависимости могут быть важны). Я имел в виду, что может быть нужно гарантировать корректность ссылок на ваши таблицы из других, в том случае, если какие-то могут ссылаться только на "наследников".

А, такое не планируется. т.е. не очень страшно, если кто то случайно станет ссылаться на другой тип

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

Помогают обеспечить гарантию целосности. Конкретный пример - таблица "персона" хранит данные, общие для всех персон (фио), специфические данные мужчин и специфические данные женщин. В этой таблице есть поле типа, которое при определении инварианта входит в check constraint - если тип "м", то все поля типа "ж" обязаны быть null. И наоборот.

Да, это помогает обеспечить консистетность данных, но только в этой таблице. Мне в этой модели не нравится ссылочная целостность. К примеру, таблица записей на приём к гинекологу должна ссылаться только на женщин, и как это обеспечить? Я вот видел такое решение: В percona добавляется (только для ссылок) уникальный индекс (id, пол), а в таблице приёмов — поле "пол" (с constraint = "ж"), и FK (id, пол) на percona(id, пол)... Но это даже выглядит очень тупо. :(

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

А, ну у вас всё просто, похоже — действительно, достаточно поля для типа.

Да, это помогает обеспечить консистетность данных, но только в этой таблице. Мне в этой модели не нравится ссылочная целостность. К примеру, таблица записей на приём к гинекологу должна ссылаться только на женщин, и как это обеспечить? Я вот видел такое решение: В percona добавляется (только для ссылок) уникальный индекс (id, пол), а в таблице приёмов — поле "пол" (с constraint = "ж"), и FK (id, пол) на percona(id, пол)... Но это даже выглядит очень тупо. :(

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

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

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

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

Их нет. Как нет ничего идеального.

Их нет. Как нет ничего идеального.

О, для многих простых моделей ещё как есть (вон, целая теория нормализации). ;) Но про то, как моделировать отношение "A является B", в ней, похоже, вообще ничего нет. :(

Это хорошо, что в задаче одна база =)А когда есть целый зоопарк, обеспечивать различные гарантии становится невыносимо.

О, для многих простых моделей ещё как есть (вон, целая теория нормализации). ;) Но про то, как моделировать отношение "A является B", в ней, похоже, вообще ничего нет. :(

Оставим теорию теоретикам с идеалистами ) В конкретном практическом деле надо ведь исходить из практики. На моей практике способ реализации наследования с помощью одной таблицы с полем типа оказался самым лучшим с т.з. сопровождения. (Конкретно в Постгрес.)

Оставим теорию теоретикам с идеалистами ) В конкретном практическом деле надо ведь исходить из практики. На моей практике способ реализации наследования с помощью одной таблицы с полем типа оказался самым лучшим с т.з. сопровождения. (Конкретно в Постгрес.)

А почему вы думаете, что я не идеалист? ;) Я вот, например, на практике перепробовал почти все эти подходы... и каждый раз мне потом казалось "нет, ну опять что-то нехорошо... уж в следующий раз сделаю как-то иначе, лучше как-то". Но вот что-то нет. :( Да и (почти всегда) то, что "есть много способов сделать <конкретно вот это>" обычно говорит только о том, что лучшего не найдено (либо вообще нет). :(

А почему вы думаете, что я не идеалист? ;) Я вот, например, на практике перепробовал почти все эти подходы... и каждый раз мне потом казалось "нет, ну опять что-то нехорошо... уж в следующий раз сделаю как-то иначе, лучше как-то". Но вот что-то нет. :( Да и (почти всегда) то, что "есть много способов сделать <конкретно вот это>" обычно говорит только о том, что лучшего не найдено (либо вообще нет). :(

На самом деле, у большинства творческих личностей есть особенность работать на пределе своих возможностей. Это как раз то, о чём Вы говорите. Стремление найти бескомпромисное решение - это похвально, т.к. это путь к появлению чего-то до селе не созданного, однако часто это идёт в разрез временных рамок и прочих ограничений реальности. Ничего идеального на практике нет и быть не может. В узкой сфере IT, которая ещё вообще находится в стадди зародыша - тем более.

На самом деле, у большинства творческих личностей есть особенность работать на пределе своих возможностей. Это как раз то, о чём Вы говорите. Стремление найти бескомпромисное решение - это похвально, т.к. это путь к появлению чего-то до селе не созданного, однако часто это идёт в разрез временных рамок и прочих ограничений реальности. Ничего идеального на практике нет и быть не может. В узкой сфере IT, которая ещё вообще находится в стадди зародыша - тем более.

> Ничего идеального на практике нет и быть не может. Ну, вот тут не соглашусь. Для многих конкретных задач (для сотен, а может, уже и тысяч их!) уже найдены лучшие решения (я про алгоритмы... и не только про них, кстати) , т.е. лучшее уже есть, и ничего другого уже не будет. ;) А, возвращаясь к теме... вы видели какие-нибудь "необычные" модели для наследования (или чего-то ему подобного), или же какие-нибудь здравые идеи по обеспечению ссылочной целостности на эту тему?

> Ничего идеального на практике нет и быть не может. Ну, вот тут не соглашусь. Для многих конкретных задач (для сотен, а может, уже и тысяч их!) уже найдены лучшие решения (я про алгоритмы... и не только про них, кстати) , т.е. лучшее уже есть, и ничего другого уже не будет. ;) А, возвращаясь к теме... вы видели какие-нибудь "необычные" модели для наследования (или чего-то ему подобного), или же какие-нибудь здравые идеи по обеспечению ссылочной целостности на эту тему?

Я знаю 6 способов реализации наследования. Об одном я уже сказал. Один из других способов - хранить все данные опять же в одной таблице, но для каждого подтипа создать отдельную таблицу, в которой будут храниться только ключи сущностей конкретного типа. И тогда на приём к гинекологу будут попадать только сущности из таблицы "женщины".

Я знаю 6 способов реализации наследования. Об одном я уже сказал. Один из других способов - хранить все данные опять же в одной таблице, но для каждого подтипа создать отдельную таблицу, в которой будут храниться только ключи сущностей конкретного типа. И тогда на приём к гинекологу будут попадать только сущности из таблицы "женщины".

Да, вот этот способ мне субъективно нравится больше других. ;) Или, может быть, его модификация, когда поля, относящиеся, например, только к женщинам ("срок беременности"), хранятся в соответствующей таблице. Но, как гарантировать следующее: . Что в какой-либо из таблиц ("женщины" или "мужчины") для данного человека вообще есть запись? . Что запись есть только в одной из этих таблиц? Я видел такие решения: 1. Person(id, FK_to_women NULL REFERENCES women, FK_to_men REFERENCES men, <other>) + constraint, что только одно из полей FK должно быть NOT NULL. 2. Просто триггерами, правами доступа и т.п. обеспечить, чтобы вставка шла только через "дочерние" таблицы, и автоматически запись попадала в родительскую (но много ручной работы). Мне в ней нравится ссылочная целостность — можно просто ссылаться на то, что нужно (таблицу).

Да, вот этот способ мне субъективно нравится больше других. ;) Или, может быть, его модификация, когда поля, относящиеся, например, только к женщинам ("срок беременности"), хранятся в соответствующей таблице. Но, как гарантировать следующее: . Что в какой-либо из таблиц ("женщины" или "мужчины") для данного человека вообще есть запись? . Что запись есть только в одной из этих таблиц? Я видел такие решения: 1. Person(id, FK_to_women NULL REFERENCES women, FK_to_men REFERENCES men, <other>) + constraint, что только одно из полей FK должно быть NOT NULL. 2. Просто триггерами, правами доступа и т.п. обеспечить, чтобы вставка шла только через "дочерние" таблицы, и автоматически запись попадала в родительскую (но много ручной работы). Мне в ней нравится ссылочная целостность — можно просто ссылаться на то, что нужно (таблицу).

Таблица Person связывается с таблицами Men, Women как 1:1, при этом, на таблицах Men и Woman простые триггеры, которые работают с полем типа Person.type. И потом можно ссылаться на Men и Woman откуда угодно.

Т.е. без триггеров никак не обойтись. Просто после всех таких телодвижений при работе с пачками таблиц приходит решение хранить все сущности в одной таблице и использовать универсальные триггеры ограничений. Например, на таблице "прием к гинекологу" действует триггер "только женщины". Довольно просто и эффективно.

Таблица Person связывается с таблицами Men, Women как 1:1, при этом, на таблицах Men и Woman простые триггеры, которые работают с полем типа Person.type. И потом можно ссылаться на Men и Woman откуда угодно.

> на таблицах Men и Woman простые триггеры, которые работают с полем типа Person.type А что эти триггеры конкретно проверяют? Хотя... если использовать person.type, а всё "значимые" поля хранить только в Person, добавить type в men/women (константного значения), можно вообще использовать обычные FK (men <-> person, women <-> person), да и всё. Тупо, конечно, что в наследниках есть поле-константа... Другие минусы модели — NULL-ов много в основной таблице, и при запросах не так очевидно, какое поле к какому виду объектов отностится. Зато в остальном, вроде, и тоже ничего. ;) Спасибо!

> на таблицах Men и Woman простые триггеры, которые работают с полем типа Person.type А что эти триггеры конкретно проверяют? Хотя... если использовать person.type, а всё "значимые" поля хранить только в Person, добавить type в men/women (константного значения), можно вообще использовать обычные FK (men <-> person, women <-> person), да и всё. Тупо, конечно, что в наследниках есть поле-константа... Другие минусы модели — NULL-ов много в основной таблице, и при запросах не так очевидно, какое поле к какому виду объектов отностится. Зато в остальном, вроде, и тоже ничего. ;) Спасибо!

Был рад пообщаться ) Спокойной ночи, ограничения реальности склоняют меня ко сну )

Был рад пообщаться ) Спокойной ночи, ограничения реальности склоняют меня ко сну )

Взаимно. Спокойной ночи!

hi, is there anyone who can give a sql beginner some guides for a detail question? thx I can not speak Russian, but there is almost no active member in the postgresql group for english speakers.

hi, is there anyone who can give a sql beginner some guides for a detail question? thx I can not speak Russian, but there is almost no active member in the postgresql group for english speakers.

Mah boi u should take tutorials with online excersises

Its 8 am in Moscow eve1 is asleep

Try out sql bolt

hi, is there anyone who can give a sql beginner some guides for a detail question? thx I can not speak Russian, but there is almost no active member in the postgresql group for english speakers.

Understanding SQL Martin Gruber. This book must be read for creating SQL-query

hi, is there anyone who can give a sql beginner some guides for a detail question? thx I can not speak Russian, but there is almost no active member in the postgresql group for english speakers.

https://www.datacamp.com/courses/intro-to-sql-for-data-science

https://www.datacamp.com/courses/joining-data-in-postgresql

https://www.datacamp.com/courses/joining-data-in-postgresql

thx，my certain specific questions have been answered.

ERROR: S client not available

thx，my certain specific questions have been answered.

You could try #postgresql (on IRC) for PostgreSQL-specific questions, it's usually pretty crowded. ;)

Привет всем! Может кто-нибудь подсказать как правильно создать группу, которая бы обладала правами на изменение схемы? я правильно понимаю, что это так делается GRANT USAGE ON SCHEMA public TO xxx;

hi there. I have a problem with postgresql. I want to try to synchronize 3 databases accross a central pivot db. For example. If i insert a row in DB1, Db1 send a query to pivot with the extension postgresql_fdw and it send this insert query to db2 and db3. I have create 3 trigger after insert every database (one for database). The problem is: if i send a insert query in db1, the pivot send this query to db2 and db3 but fire alredy the trigger insert in db2 and db3. Infinite loop :). How can i solve this problem? thanx

Привет всем! Может кто-нибудь подсказать как правильно создать группу, которая бы обладала правами на изменение схемы? я правильно понимаю, что это так делается GRANT USAGE ON SCHEMA public TO xxx;

Что подразумевается под "изменением схемы"?

Что подразумевается под "изменением схемы"?

Я уже нашел https://severalnines.com/blog/postgresql-privileges-and-security-locking-down-public-schema. Спасибо.

hi there. I have a problem with postgresql. I want to try to synchronize 3 databases accross a central pivot db. For example. If i insert a row in DB1, Db1 send a query to pivot with the extension postgresql_fdw and it send this insert query to db2 and db3. I have create 3 trigger after insert every database (one for database). The problem is: if i send a insert query in db1, the pivot send this query to db2 and db3 but fire alredy the trigger insert in db2 and db3. Infinite loop :). How can i solve this problem? thanx

If I understand you correctly, the solution is obvious - neither db1, nor db2 nor db3 should propagate the changes to the pivot if these changes comes from pivot.

If I understand you correctly, the solution is obvious - neither db1, nor db2 nor db3 should propagate the changes to the pivot if these changes comes from pivot.

it's bidirectional.....i wan insert data in db2 and propagate to db1 and db3 and i want insert data in db3 and propagate data in db1 and db2

it's bidirectional.....i wan insert data in db2 and propagate to db1 and db3 and i want insert data in db3 and propagate data in db1 and db2

I see. The triggers in dbN must consult with some kind of synchronization mechanisms. For example, you may define the special column in pivot to indicate that the record is propagated from dbN.

the special column in pivot are idref1 idref2 and idref3. where idrefx is the id of row in the source db. The problem is in insert trigger. When, for example db1 send a data to pivot, the trigger in pivot database send a query to db2 and db3 but db2 and db3 have a insert trigger and send that row alredy to the pivot and the pivot send to db1

it's loop

the special column in pivot are idref1 idref2 and idref3. where idrefx is the id of row in the source db. The problem is in insert trigger. When, for example db1 send a data to pivot, the trigger in pivot database send a query to db2 and db3 but db2 and db3 have a insert trigger and send that row alredy to the pivot and the pivot send to db1

I see. Again, the triggers on db2 and db3 in this case must check the special column of pivot to determine whether this record is propagated from db1 or not? If it is actually has been propagated, the triggers on db2 and db3 must do nothing to prevent the loop.

can u send me a schematic example?

-- create type src as enum('db1', 'db2', 'db3'); -- create table pivot(id int not null, propagated_from src null); create table db1(id int, propagated_from src null); create table db2(id int, propagated_from src null); create table db3(id int, propagated_from src null); -- Then just define your trigger function to check the "propagated_from" column.

If you don't want to add the special column to the dbN tables, then your trigger function can use FDW or dblink to access the "propagated_from" column in the pivot table.

i have thinked for a boolean column in the pivot

i have thinked for a boolean column in the pivot

You got it.

if is true then nothing, if is false then insert row in db2 for example

right?

right?

Yes.

cool. i try it now

i have created one column in db1 and 1 column in db2. the condition of trigger is when (new.propagate = false)?

false = not propagated

true = propagated

i have created one column in db1 and 1 column in db2. the condition of trigger is when (new.propagate = false)?

You may also check the condition in the trigger function. Moreover, you can check the propagation flag by quering the pivot table via FDW or dblink.

But to avoid additional lookup, you can just define the flag column in each dbN and check it with your trigger (in WHEN or in the trigger function).

work man!!!!

thanks a lot

thanks a lot

No problem. You did it yourself ?