Sometimes it is necessary to just get a row into a table. If the row already exists, then it needs to be updated. If it doesn't it needs to be inserted.

All of the methodologies proposed in the past have had race conditions since PostgreSQL at this time does not support the MERGE command.

In general the technique is this: One does an update and if it fails because the row does not exist, then the row is inserted.

	      UPDATE people SET name = in_name, email=in_email WHERE id = in_id;
	      IF NOT FOUND THEN
	      	INSERT INTO people(id,name,email) VALUES (in_id, in_name, in_email);
	      END IF;

If the table in question has very few inserts or updates, this technique could work for you. For example, a table which gets only a couple of update/inserts per day (not all at once) or tables which only get updated in batch mode on a single connection.

To insure that the update/insert does not fail due to a race condition, then we need to take advantage of the relatively new feature of exception handling in plpgsql. The full implementation is a bit awkward. It requires a function per table (unless you are doing some pg_catalog queries to divine the column names for the INSERT and UPDATE statements.)

In this function, if the UPDATE fails, an INSERT is tried. If the INSERT fails due to something else during the race condition, then it loops back up and retries the UPDATE. It would take a lot of synchronized changes to this particular row to make the loop go around more than once. But if it needs to do so, then it will, doing the proper thing.

	CREATE TABLE people 
	(id INT PRIMARY KEY, name TEXT, email TEXT);

	CREATE FUNCTION merge_people(in_id INT, in_name TEXT, in_email TEXT) 
	RETURNS VOID AS
	$$
	BEGIN
	   LOOP
	      UPDATE people SET name = in_name, email=in_email WHERE id = in_id;
	      IF found THEN
	         RETURN;
	      END IF;

	      BEGIN
	      INSERT INTO people(id,name,email) VALUES (in_id, in_name, in_email);
	         RETURN;
	      EXCEPTION WHEN unique_violation THEN
	         -- do nothing
	      END;
	   END LOOP;
	END;
	$$
	LANGUAGE plpgsql;

	SELECT merge_people(1, 'david', 'david@gmail.com');     -- Adds id 1
	SELECT merge_people(1, 'david', 'david@hotmail.com');   -- Updates id 1
	SELECT merge_people(2, 'harold', 'harold@hotmail.com'); -- Adds id 2

The RETURN statements end the LOOP. Usually LOOP ... END LOOP; is just asking for trouble. The UPDATE statement uses the special variable FOUND which is set to TRUE for a successful update.

The exception handling enables this function to catch the case that allows the loop back. The syntax is this:

	BEGIN 
	   STATEMENT 
	   EXCEPTION WHEN condition THEN 
	      STATEMENT
	END;

The condition codes are listed in Appendix A of the manual. In our case we use the unique violation test because this is the error that would be raised if we tried to insert a row that already existed.

Contributors: david fetter at david at fetter.org Julian Scarfe julian at avbrief.com, Guy Rouillier guyr at masergy.com, Tony Wasson ajwasson at gmail.com, Andrew - Supernews andrew+nonews at supernews.com, elein at varlena.com

Beware the missing_from_clause in 8.1 [GENERAL] Query in postgreSQL version Windows 02-Jan-2006

Prior to 8.1, if you included a table name in a query PostgreSQL would add it to the FROM clause for you and leave you a NOTICE that you probably ignored. As of 8.1, you will get an error instead of a warning and your query will not work. So if you've been slacking about fixing those queries, now is the time to review them and fix them.

Here is an example of a query which has a missing FROM clause. This query validates whether a certain table and column have been defined.

	SELECT atthasdef
	FROM pg_attribute
	WHERE attrelid=pg_class.oid AND 
	   pg_class.relname='client_overtime' AND 
	   attname= 'id_client_overtime';

	select atthasdef
	from pg_attribute a, pg_class c
	WHERE attrelid=c.oid AND
	c.relname = 'client_overtime' AND
	a.attname = 'id_client_overtime';

This requirement for the FROM clause includes the UPDATE statement, too. Given the following a master and a detail table, we tried this SQL:

	UPDATE d126 set dt = m126.t || d126.d
	WHERE m126.i = d126.d and m126.t = 'a';

	UPDATE d126 set dt = m.t || d126.d
	FROM  m126 m
	WHERE m.i=d126.i AND m.t = 'a';

You must be sure to avoid the usual problems with UPDATES. The updated table, d126 cannot have an alias. The table in the FROM clause will be selected along with the updated table and you most probably want to have some qualification which joins the two tables. This clause is m.i=d126.i above. The lack of a WHERE clause is a loud signal that something is problem is about to go very wrong.

Contributors: Ardian Xharra axharra at boxxo.info, Michael Fuhr mike at fuhr.org, Tom Lane tgl at sss.pgh.pa.us, elein at varlena.com