Postgresql, update if row with some unique value exists, else insert

postgresql triggers plpgsql
Roman Lebedev picture Roman Lebedev · Jun 21, 2012 · Viewed 107.1k times · Source

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.

Answer

Dave Halter picture Dave Halter · Jun 21, 2012

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

Related questions

ERROR: unterminated quoted string at or near

While executing below shown trigger code using ANT I am getting the error org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "' DECLARE timeout integer" Position: 57 I am able to sucessfully execute the below code through PGADmin (…

Read More
Postgres trigger after insert accessing NEW

I have a pretty simple trigger: CREATE OR REPLACE FUNCTION f_log_datei() RETURNS TRIGGER AS $$ BEGIN INSERT INTO logs (aktion, tabelle, benutzer_id) VALUES(TG_OP, 'dateien', NEW.benutzer_id); END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER log_datei AFTER INSERT …

Read More
Variable containing the number of rows affected by previous DELETE? (in a function)

I have a function that is used as an INSERT trigger. This function deletes rows that would conflict with [the serial number in] the row being inserted. It works beautifully, so I'd really rather not debate the merits of the …

Read More