0

I have a postgres db table connected to an application that runs validation checks before "running". These validations often fail due to incorrect default constraints (over or under the min max values). Instead of manually updating these, I'd like to run a query to find any default values that are either over or under the min/max and have them update to an arbitrary number in between (the application is self correcting so it doesn't matter where it lands).

Here is what I have but it isn't running:

do $$
begin
for i in (select id,* from constraints_table)
loop
if i.default_value < i.min_value or i.default_value>i.max_value
then
update constraints_table
set i.default_value = ((i.min_value + i.max_value)/2)
where id=i.id;
end if;
end loop;
end; $$

a) can someone help fix this query

b) is there an easier way? (python script?)

ProgrammingNoob
  • 1
  • I've also tried this (too good to be true): update constraints_table set default_value = CASE default_value when (default_value > max_value) then (default_value=max_value-1) when (default_value < min_value) then (default_value=min_value+1) end – ProgrammingNoob Sep 14 '21 at 04:07
  • Create a list using CTE (WITH) and update values as usual. https://stackoverflow.com/questions/36908495/update-with-result-from-cte-postgresql – sibert Sep 19 '21 at 05:15

1 Answers1

0

And why not just:

update constraints_table
set default_value = (min_value + max_value)/2
where default_value not between min_value and max_value;
Reinis Verbelis
  • 396
  • 3
  • 8