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.
This question is related to
postgresql
triggers
plpgsql
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();
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
.
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)
Source: Stackoverflow.com