Более гибкое решение для создания элементов и связывания событий ( source )
// creating a dynamic element (container div)
var $div = $("", {id: 'myid1', class: 'myclass'});
//creating a dynamic button
var $btn = $("
522
задан a_horse_with_no_name 3 March 2017 в 19:13
поделиться
CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
RETURNS boolean AS
$BODY$
BEGIN
UPDATE users SET name = _name WHERE id = _id;
IF FOUND THEN
RETURN true;
END IF;
BEGIN
INSERT INTO users (id, name) VALUES (_id, _name);
EXCEPTION WHEN OTHERS THEN
UPDATE users SET name = _name WHERE id = _id;
END;
RETURN TRUE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
Подобно большинству ответов, но работает немного быстрее:
WITH upsert AS (UPDATE spider_count SET tally=1 WHERE date='today' RETURNING *)
INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1 WHERE NOT EXISTS (SELECT * FROM upsert)
(источник: http://www.the-art-of-web.com/sql/upsert / )
Лично я установил «правило», прикрепленное к инструкции insert. Скажем, у вас была таблица «dns», в которой каждый раз записывались удары DNS для каждого клиента:
CREATE TABLE dns (
"time" timestamp without time zone NOT NULL,
customer_id integer NOT NULL,
hits integer
);
Вы хотели иметь возможность повторно вставлять строки с обновленными значениями или создавать их, если они не было уже. Ключ к customer_id и времени. Что-то вроде этого:
CREATE RULE replace_dns AS
ON INSERT TO dns
WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time")
AND (dns.customer_id = new.customer_id))))
DO INSTEAD UPDATE dns
SET hits = new.hits
WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));
Обновление: это может потерпеть неудачу, если происходят одновременные вставки, поскольку он будет генерировать уникальные исключения исключения. Однако транзакция без прерывания будет продолжаться и преуспеть, и вам просто нужно будет повторить завершенную транзакцию.
Однако, если есть много вложений, которые все время происходят, вам нужно будет заблокировать стол вокруг операторов вставки: Блокировка SHARE ROW EXCLUSIVE предотвратит любые операции, которые могут вставлять, удалять или обновлять строки в целевой таблице. Тем не менее, обновления, которые не обновляют уникальный ключ, безопасны, поэтому, если вы не выполните никаких действий, используйте вместо этого контрольные блокировки.
Кроме того, команда COPY не использует ПРАВИЛА, поэтому, если вы вставляете с COPY, вам нужно будет использовать триггеры.
Согласно документации PostgreSQL к оператору INSERT
, обработка случая ON DUPLICATE KEY
не поддерживается. Эта часть синтаксиса является проприетарным расширением MySQL.
MERGE
также действительно больше работает с OLAP; см. stackoverflow.com/q/17267417/398670 для объяснения. Он не определяет семантику параллелизма, и большинство людей, которые используют его для upsert, просто создают ошибки.
– Craig Ringer
8 May 2015 в 07:56
В PostgreSQL 9.5 и новее вы можете использовать INSERT ... ON CONFLICT UPDATE
.
См. документацию .
MySQL INSERT ... ON DUPLICATE KEY UPDATE
можно прямо перефразировать до a ON CONFLICT UPDATE
. Синтаксис SQL-стандарта не является, они оба расширения для конкретной базы данных. Есть веские причины MERGE
не использовались для этого , новый синтаксис не был создан только для удовольствия. (Синтаксис MySQL также имеет проблемы, которые означают, что он не был принят напрямую).
, например. заданная настройка:
CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);
запрос MySQL:
INSERT INTO tablename (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
становится:
INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;
Различия:
ON CONFLICT (columnname) DO
SET
, как если бы это был обычный UPDATE
оператор Он также имеет некоторые интересные функции:
WHERE
на вашем UPDATE
(позволяющее эффективно повернуть ON CONFLICT UPDATE
в ON CONFLICT IGNORE
для определенных значений) EXCLUDED
, которая имеет ту же структуру, что и целевая таблица. Вы можете получить исходные значения в таблице, используя имя таблицы. Поэтому в этом случае EXCLUDED.c
будет 10
(потому что мы пытались вставить), а "table".c
будет 3
, потому что это текущее значение в таблице. Вы можете использовать один или оба в выражениях SET
и WHERE
. Для фона на upsert см. Как выполнить UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE ) в PostgreSQL?
read committed
, если ваше приложение не проверяет, чтобы insert
или update
имели ненулевой ряд строк. См. dba.stackexchange.com/q/78510/7788
– Craig Ringer
7 October 2014 в 08:16
ON DUPLICATE KEY UPDATE
. Я загрузил Postgres 9.5 и реализовал ваш код, но, как ни странно, такая же проблема возникает в Postgres: серийное поле первичного ключа не является последовательным (есть промежутки между вставками и обновлениями). Любая идея, что здесь происходит? Это нормально? Любая идея, как избежать такого поведения? Спасибо.
– W.M.
7 August 2016 в 10:47
SERIAL
/ SEQUENCE
или AUTO_INCREMENT
, которые не имеют пробелов. Если вам нужны бесщеточные последовательности, они сложнее; вам обычно нужно использовать таблицу счетчиков. Google расскажет вам больше. Но имейте в виду, что безшовные последовательности предотвращают параллелизм вставки.
– Craig Ringer
8 August 2016 в 01:17
BEGIN ... EXCEPTION ...
работает в подтранзакционном режиме, который возвращается на ошибку, приращение вашей последовательности будет откат, если INSERT
не удалось.
– Craig Ringer
8 August 2016 в 01:18
Нет простой команды для этого.
Самый правильный подход - использовать функцию, такую как функция из docs .
Другое решение (хотя это и не так безопасно) заключается в том, чтобы выполнить обновление с возвратом, проверить, какие строки были обновлениями, и вставить остальные из них
Что-то по строкам:
update table
set column = x.column
from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)
where table.id = x.id
returning id;
: 2 было возвращено:
insert into table (id, column) values (1, 'aa'), (3, 'cc');
Конечно, он рано или поздно выйдет из строя (в параллельной среде), так как здесь есть явное состояние гонки, но обычно оно будет работать.
У меня такая же проблема для управления настройками учетной записи, как и пары значений имени. Критерии проектирования - это то, что разные клиенты могут иметь разные наборы настроек.
Мое решение, подобно JWP, состоит в том, чтобы объединить стирание и замену, создавая запись слияния в вашем приложении.
Это довольно пуленепробиваемая, независимая от платформы и поскольку на клиента не более 20 настроек, это всего лишь 3 довольно низкого уровня загрузки db-вызовов - возможно, самый быстрый метод.
Альтернатива обновления отдельных строк - проверка исключений то вставка - или некоторая комбинация отвратительного кода, медленная и часто ломающаяся, потому что (как упоминалось выше) нестандартная обработка исключений SQL, изменяющаяся с db на db - или даже релиз для выпуска.
#This is pseudo-code - within the application:
BEGIN TRANSACTION - get transaction lock
SELECT all current name value pairs where id = $id into a hash record
create a merge record from the current and update record
(set intersection where shared keys in new win, and empty values in new are deleted).
DELETE all name value pairs where id = $id
COPY/INSERT merged records
END TRANSACTION
REPLACE INTO
, чем INSERT INTO ... ON DUPLICATE KEY UPDATE
, что может вызвать проблему, если вы используете триггеры. В конечном итоге вы будете запускать удаление и вставлять триггеры / правила, а не обновлять их.
– cHao
15 May 2014 в 17:48
Я настраиваю функцию «upsert» выше, если вы хотите ВСТАВИТЬ И ЗАМЕНИТЬ:
`
CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)
RETURNS void AS
$BODY$
BEGIN
-- first try to insert and after to update. Note : insert has pk and update not...
EXECUTE sql_insert;
RETURN;
EXCEPTION WHEN unique_violation THEN
EXECUTE sql_update;
IF FOUND THEN
RETURN;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION upsert(text, text)
OWNER TO postgres;`
И после выполнения сделайте что-то вроде этого:
SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)
Важно поместить двойную долларовую запятую, чтобы избежать ошибок компилятора
Для слияния небольших множеств использование вышеуказанной функции в порядке. Однако, если вы объединяете большие объемы данных, я бы предложил посмотреть в http://mbk.projects.postgresql.org
Текущая лучшая практика, которую я известно о:
UPDATE вернет количество измененных строк. Если вы используете JDBC (Java), вы можете проверить это значение на 0 и, если никакие строки не были затронуты, вместо этого запустите INSERT. Если вы используете какой-либо другой язык программирования, возможно, количество модифицированных строк может быть получено, проверьте документацию.
Это может быть не так элегантно, но у вас гораздо более простой SQL, который более тривиально использовать из вызывающего кода. Иными словами, если вы пишете десять строк сценария в PL / PSQL, вы, вероятно, должны иметь единичный тест того или иного вида только для него.
Изменить: это не работает должным образом. В отличие от принятого ответа, это приводит к уникальным нарушениям ключа, когда два процесса неоднократно называют upsert_foo
одновременно.
Eureka! Я понял способ сделать это в одном запросе: используйте UPDATE ... RETURNING
, чтобы проверить, были ли затронуты какие-либо строки:
CREATE TABLE foo (k INT PRIMARY KEY, v TEXT);
CREATE FUNCTION update_foo(k INT, v TEXT)
RETURNS SETOF INT AS $$
UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1
$$ LANGUAGE sql;
CREATE FUNCTION upsert_foo(k INT, v TEXT)
RETURNS VOID AS $$
INSERT INTO foo
SELECT $1, $2
WHERE NOT EXISTS (SELECT update_foo($1, $2))
$$ LANGUAGE sql;
UPDATE
необходимо выполнить в отдельной процедуре, потому что, к сожалению, это синтаксическая ошибка:
... WHERE NOT EXISTS (UPDATE ...)
Теперь она работает по желанию:
SELECT upsert_foo(1, 'hi');
SELECT upsert_foo(1, 'bye');
SELECT upsert_foo(3, 'hi');
SELECT upsert_foo(3, 'bye');
Я использую эту функцию merge
CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
RETURNS void AS
$BODY$
BEGIN
IF EXISTS(SELECT a FROM tabla WHERE a = key)
THEN
UPDATE tabla SET b = data WHERE a = key;
RETURN;
ELSE
INSERT INTO tabla(a,b) VALUES (key, data);
RETURN;
END IF;
END;
$BODY$
LANGUAGE plpgsql
update
, а затем проверить количество обновленных строк. (См. Ответ Ахмада)
– a_horse_with_no_name
9 January 2015 в 11:47
Я искал то же самое, когда я пришел сюда, но отсутствие общей функции «upsert» немного меня беспокоило, поэтому я подумал, что вы можете просто передать обновление и вставить sql в качестве аргументов в эту функцию из руководства
, который будет выглядеть так:
CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
LOOP
-- first try to update
EXECUTE sql_update;
-- check if the row is found
IF FOUND THEN
RETURN;
END IF;
-- not found so insert the row
BEGIN
EXECUTE sql_insert;
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing and loop
END;
END LOOP;
END;
$$;
и, возможно, делать то, что вы изначально хотели сделать, пакетный «upsert», вы могли бы использовать Tcl для разделения sql_update и петли отдельного обновления, предел производительности будет очень небольшим, см. http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php
самая высокая стоимость выполняется запрос из вашего кода, на стороне базы данных стоимость выполнения намного меньше
DELETE
, если вы не заблокируете таблицу или не изолируете транзакцию SERIALIZABLE
от PostgreSQL 9.1 или выше.
– Craig Ringer
22 May 2013 в 11:00