Postgresql, update if row with some unique value exists, else insert
PostgresqlTriggersPlpgsqlPostgresql Problem Overview
I have a URLs table. They contain
(id int primary key, url character varying unique, content character varying, last analyzed date).
I want to create trigger or something(rule may be), so each time i make insert from my java program, it updates some single row if row with such URL exists. Else it should perform an Insert.
Please, can you provide a complete code in Postgresql. Thanks.
Postgresql Solutions
Solution 1 - Postgresql
This has been asked many times. A possible solution can be found here: https://stackoverflow.com/a/6527838/552671
This solution requires both an UPDATE
and INSERT
.
UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
SELECT 3, 'C', 'Z'
WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);
With Postgres 9.1 it is possible to do it with one query: https://stackoverflow.com/a/1109198/2873507
Solution 2 - Postgresql
If INSERTS
are rare, I would avoid doing a NOT EXISTS (...)
since it emits a SELECT
on all updates. Instead, take a look at wildpeaks answer: https://dba.stackexchange.com/questions/5815/how-can-i-insert-if-key-not-exist-with-postgresql
CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $$
DECLARE
BEGIN
UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2;
IF NOT FOUND THEN
INSERT INTO tableName values (value, arg1, arg2);
END IF;
END;
$$ LANGUAGE 'plpgsql';
This way Postgres will initially try to do a UPDATE
. If no rows was affected, it will fall back to emitting an INSERT
.
Solution 3 - Postgresql
I found this post more relevant in this scenario:
WITH upsert AS (
UPDATE spider_count SET tally=tally+1
WHERE date='today' AND spider='Googlebot'
RETURNING *
)
INSERT INTO spider_count (spider, tally)
SELECT 'Googlebot', 1
WHERE NOT EXISTS (SELECT * FROM upsert)
Solution 4 - Postgresql
Firstly It tries insert. If there is a conflict on url
column then it updates content and last_analyzed fields. If updates are rare this might be better option.
INSERT INTO URLs (url, content, last_analyzed)
VALUES
(
%(url)s,
%(content)s,
NOW()
)
ON CONFLICT (url)
DO
UPDATE
SET content=%(content)s, last_analyzed = NOW();
Solution 5 - Postgresql
create table urls (
url_id serial primary key,
url text unique,
content text,
last_analyzed timestamptz);
insert into urls(url) values('hello'),
('How'),('are'),
('you'),('doing');
By creating procedure, you also also do upsert.
CREATE OR REPLACE PROCEDURE upsert_url(_url text) LANGUAGE plpgsql
as $$
BEGIN
INSERT INTO URLs (url) values (_url)
ON CONFLICT (url)
DO UPDATE SET last_analyzed = NOW();
END
$$;
Test it through call the procedure.
call upsert_url('I am is ok');
call upsert_url('hello');