Не работает update postgresql

UPDATE — изменить строки таблицы

Синтаксис

Описание

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

Изменить строки в таблице, используя информацию из других таблиц в базе данных, можно двумя способами: применяя вложенные запросы или указав дополнительные таблицы в предложении FROM . Выбор предпочитаемого варианта зависит от конкретных обстоятельств.

Предложение RETURNING указывает, что команда UPDATE должна вычислить и возвратить значения для каждой фактически изменённой строки. Вычислить в нём можно любое выражение со столбцами целевой таблицы и/или столбцами других таблиц, упомянутых во FROM . При этом в выражении будут использоваться новые (изменённые) значения столбцов таблицы. Список RETURNING имеет тот же синтаксис, что и список результатов SELECT .

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

Параметры

Предложение WITH позволяет задать один или несколько подзапросов, на которые затем можно ссылаться по имени в запросе UPDATE . Подробнее об этом см. Раздел 7.8 и SELECT . имя_таблицы

Имя таблицы (возможно, дополненное схемой), строки которой будут изменены. Если перед именем таблицы добавлено ONLY , соответствующие строки изменяются только в указанной таблице. Без ONLY строки будут также изменены во всех таблицах, унаследованных от указанной. При желании, после имени таблицы можно указать * , чтобы явно обозначить, что операция затрагивает все дочерние таблицы. псевдоним

Альтернативное имя целевой таблицы. Когда указывается это имя, оно полностью скрывает фактическое имя таблицы. Например, в запросе UPDATE foo AS f дополнительные компоненты оператора UPDATE должны обращаться к целевой таблице по имени f , а не foo . имя_столбца

Имя столбца в таблице имя_таблицы . Имя столбца при необходимости может быть дополнено именем вложенного поля или индексом массива. Имя таблицы добавлять к имени целевого столбца не нужно — например, запись UPDATE table_name SET table_name.col = 1 ошибочна. выражение

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

Присвоить столбцу значение по умолчанию (это может быть NULL, если для столбца не определено некоторое выражение по умолчанию). вложенный_SELECT

Подзапрос SELECT , выдающий столько выходных столбцов, сколько перечислено в предшествующем ему списке столбцов в скобках. При выполнении этого подзапроса должна быть получена максимум одна строка. Если он выдаёт одну строку, значения столбцов в нём присваиваются целевым столбцам; если же он не возвращает строку, целевым столбцам присваивается NULL. Этот подзапрос может обращаться к предыдущим значениям текущей изменяемой строки в таблице. элемент_FROM

Табличное выражение, позволяющее обращаться в условии WHERE и выражениях новых данных к столбцам других таблиц. В нём используется тот же синтаксис, что и в предложении Предложение FROM оператора SELECT ; например, вы можете определить псевдоним для таблицы. Имя целевой таблицы повторять в предложении FROM нужно, только если вы хотите определить замкнутое соединение (в этом случае для данного имени должен определяться псевдоним). условие

Выражение, возвращающее значение типа boolean . Изменены будут только те стоки, для которых это выражение возвращает true . имя_курсора

Имя курсора, который будет использоваться в условии WHERE CURRENT OF . С таким условием будет изменена строка, выбранная из этого курсора последней. Курсор должен образовываться запросом, не применяющим группировку, к целевой таблице команды UPDATE . Заметьте, что WHERE CURRENT OF нельзя задать вместе с логическим условием. За дополнительными сведениями об использовании курсоров с WHERE CURRENT OF обратитесь к DECLARE . выражение_результата

Выражение, которое будет вычисляться и возвращаться командой UPDATE после изменения каждой строки. В этом выражении можно использовать имена любых столбцов таблицы имя_таблицы или таблиц, перечисленных в списке FROM . Чтобы получить все столбцы, достаточно написать * . имя_результата

Имя, назначаемое возвращаемому столбцу.

Выводимая информация

В случае успешного завершения, UPDATE возвращает метку команды в виде

Здесь число обозначает количество изменённых строк, включая те подлежащие изменению строки, значения в которых не были изменены. Заметьте, что это число может быть меньше количества строк, удовлетворяющих условию , когда изменения отменяются триггером BEFORE UPDATE . Если число равно 0, данный запрос не изменил ни одной строки (это не считается ошибкой).

Если команда UPDATE содержит предложение RETURNING , её результат будет похож на результат оператора SELECT (с теми же столбцами и значениями, что содержатся в списке RETURNING ), полученный для строк, изменённых этой командой.

Замечания

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

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

Примеры

Изменение слова Drama на Dramatic в столбце kind таблицы films :

Изменение значений температуры и сброс уровня осадков к значению по умолчанию в одной строке таблицы weather :

Выполнение той же операции с получением изменённых записей:

Такое же изменение с применением альтернативного синтаксиса со списком столбцов:

Увеличение счётчика продаж для менеджера, занимающегося компанией Acme Corporation, с применением предложения FROM :

Выполнение той же операции, с вложенным запросом в предложении WHERE :

Изменение имени контакта в таблице счетов (это должно быть имя назначенного менеджера по продажам):

Подобный результат можно получить, применив соединение:

Однако если salesmen . id — не уникальный ключ, второй запрос может давать непредсказуемые результаты, тогда как первый запрос гарантированно выдаст ошибку, если найдётся несколько записей с одним id . Кроме того, если соответствующая запись accounts . sales_id не найдётся, первый запрос запишет в поля имени NULL, а второй вовсе не изменит строку.

Обновление статистики в сводной таблице в соответствии с текущими данными:

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

Изменение столбца kind таблицы films в строке, на которой в данный момент находится курсор c_films :

Совместимость

В некоторых других СУБД также поддерживается дополнительное предложение FROM , но предполагается, что целевая таблица должна ещё раз упоминаться в этом предложении. PostgreSQL воспринимает предложение FROM не так, поэтому будьте внимательны, портируя приложения, которые используют это расширение языка.

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

Источник

PostgreSQL 9.5: что нового? Часть 1. INSERT… ON CONFLICT DO NOTHING/UPDATE и ROW LEVEL SECURITY

INSERT… ON CONFLICT DO NOTHING/UPDATE

Он же в просторечии UPSERT. Позволяет в случае возникновения конфликта при вставке произвести обновление полей или же проигнорировать ошибку.

То, что раньше предлагалось реализовывать с помощью хранимой функции, теперь будет доступно из коробки. В выражении INSERT можно использовать условие ON CONFLICT DO NOTHING/UPDATE. При этом в выражении указывается отдельно conflict_target (по какому полю/условию будет рассматриваться конфликт) и conflict_action (что делать, когда конфликт произошел: DO NOTHING или DO UPDATE SET).

Полный синтаксис выражения INSERT будет такой:

Для нас самое интересное начинается после ON CONFLICT.

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

Выполним запрос на вставку

id	name	surname	address
1	Вася	Пупкин	Москва, Кремль

Здесь conflict_target — это (id), а conflict_action — DO NOTHING.
Если попытаться выполнить этот запрос второй раз, то вставки не произойдет, при этом и не выдаст никакого сообщения об ошибке:

Если бы мы не указали ON CONFLICT (id) DO NOTHING, то получили бы ошибку:

Такое же поведение (как и у ON CONFLICT (id) DO NOTHING) будет у запроса:

В нем мы уже берем значение id по умолчанию (из последовательности), но указываем другой conflict_target — по трем полям, на которые наложено ограничение уникальности.

Как упоминалось выше, также можно указать conflict_target с помощью конструкции ON CONSTRAINT, указав непосредственно имя ограничения:

Особенно полезно это в случае, если у вас есть исключающее ограничение (exclusion constraint), к которому вы можете обратиться только по имени, а не по набору колонок, как в случае с ограничением уникальности.

Если у вас построен частичный уникальный индекс, то это также можно указать в условии. Пусть в нашей таблице уникальными сочетания фамилия+адрес будут только у людей с именем Вася:

Тогда мы можем написать такой запрос:

Ну и, наконец, если вы хотите, чтобы DO NOTHING срабатывал при любом конфликте уникальности/исключения при вставке, то это можно записать следующим образом:

Стоит заметить, что задать несколько conflict_action невозможно, поэтому если указан один из них, а сработает другой, то будет ошибка при вставке:

Перейдем к возможностям DO UPDATE SET.

Для DO UPDATE SET в отличие от DO NOTHING указание conflict_action обязательно.

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

id	name	surname	address
1	Петя	Петров	Москва, Кремль

id	name	surname	address
1	Петя (бывший Вася)	Петров (бывший Пупкин)	Москва, Кремль

id	name	surname	address
1	NULL	NULL	Москва, Кремль

Также может быть использовано условие WHERE. Например, мы хотим, чтобы поле name не обновлялось, если в поле address в строке таблицы уже содержится текст «Кремль», в противном же случае — обнловлялось:

А если хотим, чтобы поле name не обновлялось, если в поле address во вставляемых данных содержится текст «Кремль», в противном же случае — обнловлялось:

id	name	surname	address
1	Вася	NULL	Москва, Кремль

ROW LEVEL SECURITY

Row-level security или безопасность на уровне строк — механизм разграничения доступа к информации к БД, позволяющий ограничить доступ пользователей к отдельным строкам в таблицах.

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

Работает это следующим образом: описываются правила для конкретной таблицы, согласно которым ограничивается доступ к конкретным строкам при выполнении определнных команд, с помощью выражения CREATE POLICY. Каждое правило содержит некое логическое выражение, которое должно быть истинным, чтобы строка была видна в запросе. Затем правила активируются с помощью выражения ALTER TABLE… ENABLE ROW LEVEL SECURITY. Затем при попытке доступа, например при запросе SELECT, проверяется, имеет ли пользователь право на доступ к конкретной строке и если нет, то они ему не показываются. Суперпользователь по умолчанию может видеть все строки, так как у него по умолчанию выставлен флаг BYPASSRLS, который означает, что для данной роли проверки осуществляться не будут.

Синтаксис выражения CREATE POLICY такой:

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

После TO — для каких ролей, по умолчанию — PUBLIC, то есть для всех ролей.

Далее, в выражении USING указывается булевское выражение, которое должно быть true, чтобы конкретная строка была видна пользователю в запросах, которые используют уже имеющиеся данные (SELECT, UPDATE, DELETE). Если булевское выражение вернуло null или false, то строка видна не будет.

В выражении WITH CHECK указывается булевское выражение, которое должно быть true, чтобы запрос, добавляющий или изменяющий данные (INSERT или UPDATE), прошел успешно. В случае, если булевское выражение вернет null или false, то будет ошибка. Выражение WITH CHECK выполняется после триггеров BEFORE (если они присутствуют) и до любых других проверок. Поэтому, если триггер BEFORE модифицирует строку таким образом, что условие не вернет true, будет ошибка. Для успешного выполнения UPDATE необходимо, чтобы оба условия вернули true, в том числе, если в запросе INSERT… ON CONFILCT DO UPDATE произойдет конфликт и запрос попытается модифицировать данные. Если выражение WITH CHECK опущено, вместо него будет подставляться условие из выражения USING.
В условиях нельзя использовать аггрегирующие или оконные функции.

Обычно, требуется управлять доступом, исходя из того, какой пользователь БД запрашивает данные, поэтому нам пригодятся функции, возвращающие информацию о системе (System Information Functions).

Перейдем к примерам:

Добавим в таблицу account поле db_user, заполним это поле для уже существующей записи и добавим новые записи:

Создадим правило и включим RLS на таблице:

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

Выполним запрос от пользователя postgres:

id	name	surname	address	db_user
1	Вася	Пупкин	Москва, Кремль	pupkin
5	Петр	Петров	Москва, Красная площадь	petrov
6	Иван	Сидоров	Санкт-Петербург, Зимний дворец	sidorov

Выполним тот же запрос от пользователя pupkin:

id	name	surname	address	db_user
1	Вася	Пупкин	Москва, Кремль	pupkin

Создадим правило, по которому строки с фамилией «Пупкин» может вставлять только пользователь pupkin:

Попробуем выполнить запрос от пользователя pupkin:

id	name	surname	address	db_user
1	Вася	Пупкин	Москва, Кремль	pupkin

Оп-па! Мы забыли указать поле db_user и запись, которую мы вставили, мы уже не увидим. Что ж, давайте исправим такую логику с помощью триггера, в котором будем заполнять поле db_user именем текущего пользователя:

id	name	surname	address	db_user
1	Вася	Пупкин	Москва, Кремль	pupkin
21	Иван	Пупкин	Киев, Майдан	pupkin

Попробуем изменить данные о Иване Пупкине пользователем petrov:

Как видим, данные не изменились, это произошло потому, что условие USING из правила select_self не выполнилось.

Если одному запросу соответствует несколько правил, то они объединяются через OR.

Стоит отметить, что правила применяются только при явным запросам к таблицам и не применяются при проверках, которые осуществляет система (constaints, foreign keys и т.п.). Это означает, что пользователь с помощью запросов, определить, что какое-либо значение существует в БД. Например, если пользователь может осуществлять вставку в таблицу, которая ссылается на другую таблицу, из которой он не может делать SELECT. В таком случае, он может попытаться сделать INSERT в первую таблицу и по результату (произошла вставка или же произошла ошибка при проверке ссылочной целостности) определить, существует ли значение во второй таблице.

Вариантов использования row-level security можно придумать множество:

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

В следующих частях я планирую рассмотреть такие новые фичи PostgreSQL 9.5, как:

Часть 2. TABLESAMPLE
SKIP LOCKED
BRIN-индексы
GROUPING SETS, CUBE, ROLLUP
Новые функции для JSONB
IMPORT FOREIGN SCHEMA
и другие

Источник

Не работает update postgresql

PostgreSQL UPDATE