On Thu, Aug 8, 2024 at 2:39 PM Lok P <loknath...@gmail.com> wrote: > Can anybody suggest any other possible way here. >
Sure - how about not changing the column type at all? > one of the columns from varchar(20) to varchar(2) ALTER TABLE foobar ADD CONSTRAINT twocharplease CHECK (length(mycol) <= 2) NOT VALID; > one of the columns from Number(10,2) to Numeric(8,2) ALTER TABLE foobar ADD CONSTRAINT eightprecision CHECK (mycol <= 10^8) NOT VALID; > two of the columns from varchar(20) to numeric(3) This one is trickier, as we don't know the contents, nor why it is going to numeric(3) - not a terribly useful data type, but let's roll with it and assume the stuff in the varchar is a number of some sort, and that we don't allow nulls: ALTER TABLE foobar ADD CONSTRAINT onekorless CHECK (mycol::numeric(3) is not null) NOT VALID; You probably want to check on the validity of the existing rows: see the docs on VALIDATE CONSTRAINT here: https://www.postgresql.org/docs/current/sql-altertable.html Cheers, Greg
