Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example).

And I'm just not a psql expert at all to find out immediately, whether these solutions are old because they are well recommended or they are (well, almost all of them are) just toy examples not appropriate to production use.

So my question is following. Regarding to the fact that it is year 2012, what is the most common, most thread-safe way to implement UPSERT in PostgreSQL?

share|improve this question
1  
if you're using ruby, you might try the upsert ruby library - a python version is forthcoming –  Seamus Abshere Sep 10 '12 at 21:57
add comment

4 Answers 4

up vote 10 down vote accepted

The preferred method according a similar StackOverflow question is currently the following:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');
share|improve this answer
5  
I'd rather use a writeable CTE: stackoverflow.com/a/8702291/330315 –  a_horse_with_no_name Feb 21 '12 at 11:33
    
What's the advantage of a writable CTE vs a function? –  François Beausoleil Feb 21 '12 at 13:23
1  
@François for one thing, speed. Using a CTE you hit the database once. Doing it this way you might hit it two or more times. Also, the optimizer can't optimize pl/pgsql procedures as efficiently as pure SQL code. –  Adam Mackler Nov 23 '13 at 11:10
1  
@François For another thing, concurrency. Since the above example has multiple SQL statements you have to worry about race conditions (the reason for the klugey loop). A single SQL statement will be atomic. See this link –  Adam Mackler Nov 23 '13 at 11:16
1  
@FrançoisBeausoleil see here and here for why. Basically without a re-try loop, you either have to serialize or you have the possibility of failures due to the inherent race condition. –  Jack Douglas Apr 7 at 5:51
show 1 more comment

Here is an implementation I arrived upon when desiring to gain visibility into whether an insert or update occurred.

The definition of upsert_data is to consolidate the values into a single resource, rather than having to specify the price and item_id twice: Once for the update, again for the insert.

WITH upsert_data AS (
    SELECT
    '19.99'::numeric(10,2) AS price,
    'abcdefg'::character varying AS item_id
),
update_outcome AS (
    UPDATE pricing_tbl
    SET price = upsert_data.price
    FROM upsert_data
    WHERE pricing_tbl.item_id = upsert_data.item_id
    RETURNING 'update'::text AS action, entry_id
),
insert_outcome AS (
    INSERT INTO
        pricing_tbl
    (price, item_id)
    SELECT
        upsert_data.price AS price,
        upsert_data.item_id AS item_id
    FROM upsert_data
    WHERE NOT EXISTS (SELECT entry_id FROM update_outcome LIMIT 1)
    RETURNING 'insert'::text AS action, entry_id
)
SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome

If you don't like the use of upsert_data, here is an alternate implementation:

WITH update_outcome AS (
    UPDATE pricing_tbl
    SET price = '19.99'
    WHERE pricing_tbl.item_id = 'abcdefg'
    RETURNING 'update'::text AS action, entry_id
),
insert_outcome AS (
    INSERT INTO
        pricing_tbl
    (price, item_id)
    SELECT
        '19.99' AS price,
        'abcdefg' AS item_id
    WHERE NOT EXISTS (SELECT entry_id FROM update_outcome LIMIT 1)
    RETURNING 'insert'::text AS action, entry_id
)
SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome
share|improve this answer
    
How does it perform? –  jb. Dec 10 '13 at 17:54
    
@jb. not as well as I would like. You're going to see significant performance penalties vs. performing straight inserts. However for smaller batches (say 1000 or less,) this example should perform just fine. –  Joshua Burns Jan 22 at 17:12
add comment

This will let you know whether the insert or update happened:

with "update_items" as (
  -- Update statement here
  update items set price = 3499, name = 'Uncle Bob'
  where id = 1 returning *
)
-- Insert statement here
insert into items (price, name)
-- But make sure you put your values like so
select 3499, 'Uncle Bob'
where not exists ( select * from "update_items" );

If the update occurs, you'll get an insert 0, otherwise insert 1 or an error.

share|improve this answer
add comment

Provided that you have this table

CREATE TABLE test (id serial, v text)

you can use a single query to do an upsert

WITH
existing AS (
  SELECT * FROM test WHERE v = $1
),
new AS (
  INSERT INTO test ( v )
  SELECT $1 WHERE NOT EXISTS ( SELECT 1 FROM existing )
  RETURNING *
)
SELECT * FROM new
UNION ALL
SELECT * FROM existing

Replace $1 as needed with your values

share|improve this answer
    
Where's the UPDATE? –  MattDiPasquale Apr 13 at 3:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.