Skip to content

Instantly share code, notes, and snippets.

@clhenrick
Created April 15, 2016 03:40
Show Gist options
  • Save clhenrick/de68e7bf948c8e62b41e34b1340e0707 to your computer and use it in GitHub Desktop.
Save clhenrick/de68e7bf948c8e62b41e34b1340e0707 to your computer and use it in GitHub Desktop.
example postgresql bulk upsert query (from http://blog.cartodb.com/faster-data-updates-with-cartodb )
WITH
-- write the new values
n(ip,visits,clicks) AS (
VALUES ('192.168.1.1',2,12),
('192.168.1.2',6,18),
('192.168.1.3',3,4)
),
-- update existing rows
upsert AS (
UPDATE page_views o
SET visits=n.visits, clicks=n.clicks
FROM n WHERE o.ip = n.ip
RETURNING o.ip
)
-- insert missing rows
INSERT INTO page_views (ip,visits,clicks)
SELECT n.ip, n.visits, n.clicks FROM n
WHERE n.ip NOT IN (
SELECT ip FROM upsert
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment