Conditional UPDATE And DELETE In PostgreSQL
One of the coolest PostgreSQL features: Conditional SELECTs/UPDATEs/DELETEs.
Lets say we have a table of distances, but we need to add a column for the distance type, ie, Miles or Kilometers. Conditional UPDATE to the rescue!!!:
UPDATE distances SET measurement =
(CASE WHEN (name ~* '^[0-9]+K|km.$|kilometers$')
THEN 'kilometers'
WHEN (name ~* '^[0-9]+M|mi.$|miles$')
THEN 'miles'
ELSE 'unknown' END);
They also work for DELETEs, but I haven’t found any real world situation where it makes a lot of sense. Regardless:
DELETE FROM table WHERE
(CASE WHEN col1 ~* '^foo|bar$'
THEN 't'::boolean
WHEN col2 ~* '^oof|rab$'
THEN 't'::boolean
ELSE 'f'::boolean END);
April 4th, 2008 at 3:03 pm
CASE appears to be part of the SQL standard. It’s definitely implemented in MySQL, and certainly not something that’s specific to PostgreSQL.
It’s really much more useful in stored procedures. The UPDATE above could also be done as:
UPDATE `distances` SET `measurement` = ‘kilometers’ WHERE `name` LIKE ‘%km.’ OR `name` LIKE ‘%kilometers’ OR `name` LIKE ‘%K’;
UPDATE `distances` SET `measurement` = ‘miles’ WHERE `name` LIKE ‘%mi.’ OR `name` LIKE ‘%miles’ OR `name` LIKE ‘%M’;
Which is to say that it doesn’t get you anything except that you can do it in a single query. You may want to avoid that to improve the legibility of your code. You can further refine it, if you use ‘k’ and ‘m’ as your identifiers, rather than the full word. This works in PostgreSQL, but not MySQL (it doesn’t have REGEXP_REPLACE()):
UPDATE `distances` SET `measurement` = REGEXP_REPLACE(’[^km]’, `name`, ”);
The delete query is even more straightforward, and you would certainly prefer WHERE over CASE for readability:
DELETE FROM `table` WHERE `col1` ~* ‘^foo|bar$’ OR `col2` ~* ‘^oof|rab$’;