> Did something change with how exclusion constraints are handled? I'm trying to troubleshoot a regression we are having with PostGIS raster support.
> As best I can guess, it's because exclusion constraints that used to work in past versions are failing in PostgreSQL 10 with an error something like > this: > ERROR: conflicting key value violates exclusion constraint "enforce_spatially_unique_test_raster_columns_rast" > ERROR: new row for relation "test_raster_columns" violates check constraint "enforce_coverage_tile_rast" > Unfortunately I don't know how long this has been an issue since we had an earlier test failing preventing the raster ones from being tested. > Thanks, > Regina I figured out the culprit was the change in CASE WHEN behavior with set returning functions Had a criteria something of the form: CASE WHEN some_condition_dependent_on_sometable_that_resolves_to_false THEN (regexp_matches(...))[1] ELSE ... END FROM sometable; One thing that seems a little odd to me is why these return a record SELECT CASE WHEN strpos('ABC', 'd') > 1 THEN (regexp_matches('a (b) c', 'd'))[1] ELSE 'a' END; SELECT CASE WHEN false THEN (regexp_matches('a (b) c', 'd'))[1] ELSE 'a' END FROM pg_tables; And this doesn't - I'm guessing it has to do with this being a function of the value of table, but it seems unintuitive >From a user perspective. SELECT CASE WHEN strpos(f.tablename, 'ANY (ARRAY[') > 1 THEN (regexp_matches('a (b) c', 'd'))[1] ELSE 'a' END FROM pg_tables AS f; Pre-PostgreSQL 10 this would return a row for each record in pg_tables Thanks, Regina -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers