PostgreSQL Syntax is a Mystery Only Exceeded by its Power


I just ran across this rather powerful PostgreSQL example statement from the manual. It reads

Increment the sales count of the salesperson who manages the account for Acme Corporation, and record the whole updated row along with current time in a log table:

WITH upd AS (
  UPDATE employees 
  SET sales_count = sales_count + 1 
  WHERE id = (
    SELECT sales_person 
    FROM accounts 
    WHERE name = 'Acme Corporation'
  )
  RETURNING *
)
INSERT INTO employees_log 
SELECT *, current_timestamp 
FROM upd;

This database keeps amazing me. A single statement allows for updating one table, taking the updated record(s) including all generated values as input for an insert into another table. But at the same time, PostgreSQL does not yet implement the SQL:2003 MERGE statement.

Crazy database…

Tags: , , , , ,

4 responses to “PostgreSQL Syntax is a Mystery Only Exceeded by its Power”

  1. agentgt says :

    UPSERTING aka MERGE is a complicated subject for truly acid compliant databases. The databases that implement MERGE are generally sacrificing something (performance or consistency). Thus blindly using MERGE because the database supports it is not necessarily a good idea.

    However Postgres could offer some solution that maybe fits 80% but I see the two use cases of upserting very divided e.g. stats that matter like money which will sacrifice speed or stats that don’t matter like facebook likes.

    That being said “upserting” is very useful for real time analytics and I have run into Postgres issues with transaction/lock performance. I think this is where databases like VoltDB or NoSQL options like Redis really shine (than can do it generally because they are in memory and synchronous).

    • lukaseder says :

      Yeah, a powerful MERGE execution touching millions of records is certainly not a good idea. But if you know you’ll operate only on one record (if it exists), then there is not that big of a performance loss. I particularly like the H2 MERGE syntax, which is really a very simple UPSERT.

      Do you have any experience with VoltDB or other of Stonebraker’s NewSQL stores?

      • Andreas says :

        The complex part of UPSERT is retaining ACID while still having good performance and concurrency. These are the concerns which prevented UPSERT from being included in 9.4.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,241 other followers

%d bloggers like this: