ERROR: check constraint "to_uppercase" of relation "my_table" is violated by some row

I try to run following script:

ALTER TABLE my_table
    ADD CONSTRAINT to_uppercase CHECK (upper(my_name) = my_name);

but when I do I receive the error:

 ERROR: check constraint "to_uppercase" of relation "my_table" is violated by some row

This error quite expected because on some rows I have value which contains lowercase symbols.

Is there way to define on conflict to replace values to upperase in the same statement?

P.S. I understand that I can search rows using

select ...
where my_name !=  upper(my_name )

and do consequent update but I would like to find a solution using single stetement

Solution

Try the following, so what I think you should do is make sure all data in the my_name column of my_table meets the requirement of being in uppercase before applying a new constraint, I proposed a two-step process wrapped in a single transaction for atomicity. First, an UPDATE statement is executed to convert all existing entries in my_name to uppercase where necessary, ensuring no current data will violate the new constraint. Following this, an ALTER TABLE statement is used to add a CHECK constraint that enforces all new or updated rows in my_name to be uppercase. This approach, encapsulated within a BEGIN and COMMIT transaction, guarantees the database maintains consistency and all entries adhere to the constraint requirements before it is enforced.

BEGIN;

-- Temporarily store the uppercased version that doesn't create duplicates
CREATE TEMP TABLE updated_names AS
SELECT my_id, upper(my_name) AS new_name
FROM my_table
WHERE my_name != upper(my_name)
AND upper(my_name) NOT IN (SELECT my_name FROM my_table);

-- Update original table from the temp table where no duplicates would occur
UPDATE my_table
SET my_name = (SELECT new_name FROM updated_names WHERE updated_names.my_id = my_table.my_id)
WHERE EXISTS (
  SELECT 1 FROM updated_names WHERE updated_names.my_id = my_table.my_id
);

-- Handle remaining duplicates manually or via application logic

-- Add the constraint after resolving duplicates
ALTER TABLE my_table
ADD CONSTRAINT to_uppercase CHECK (upper(my_name) = my_name);

COMMIT;