git.squeep.com Git - squeep-indie-auther/blob - src/db/postgres/sql/scope-upsert.sql

   1 --
   2 -- N.B. weirdness with postgres empty string null equivalence and coalesce
   3 INSERT INTO scope (
   4         scope, application, description, is_manually_added
   5 ) VALUES (
   6         $(scope),
   7         CASE WHEN $(application) IS NULL THEN '' ELSE $(application) END,
   8         CASE WHEN $(description) IS NULL THEN '' ELSE $(description) END,
   9         COALESCE($(manuallyAdded), false)
  10 ) ON CONFLICT (scope) DO UPDATE
  11 SET
  12         application = CASE WHEN $(application) IS NULL THEN EXCLUDED.application ELSE $(application) END,
  13         description = CASE WHEN $(description) IS NULL THEN EXCLUDED.description ELSE $(description) END,
  14         is_manually_added = EXCLUDED.is_manually_added OR COALESCE($(manuallyAdded), false)