Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
WITH
-- write the new values
new(the_geom,description,name) AS (
VALUES (ST_SetSRID(ST_Point(12,12),4326),'an update',123456),
(ST_SetSRID(ST_Point(13,13),4326),'an insert',100000)
),
-- update existing rows
upsert AS (
UPDATE {table} original
SET the_geom=new.the_geom, description=new.description
FROM new WHERE original.name = new.name
RETURNING original.name
)
-- insert missing rows
INSERT INTO {table} (the_geom,description,name)
SELECT new.the_geom, new.description, new.name FROM new
WHERE new.name NOT IN (
SELECT name FROM upsert
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment