Hi
Our customer reported an issue related to quiet precision lost when ALTER
TABLE ALTER COLUMN was executed.
The ALTER TABLE ALTER COLUMN TYPE is sometimes safe
(2025-03-04 08:02:32) postgres=# create table t1(a varchar(10));
CREATE TABLE
(2025-03-04 08:02:43) postgres=# INSERT INTO t1 VALUES('AHOJ');
INSERT 0 1
(2025-03-04 08:02:57) postgres=# ALTER TABLE t1 ALTER COLUMN a TYPE
varchar(2);
ERROR: value too long for type character varying(2)
(2025-03-04 08:03:28) postgres=#
but sometimes not
(2025-03-04 08:03:28) postgres=# CREATE TABLE t2(a double precision);
CREATE TABLE
(2025-03-04 08:04:09) postgres=# INSERT INTO t2 VALUES(3.14);
INSERT 0 1
(2025-03-04 08:04:24) postgres=# ALTER TABLE t2 ALTER COLUMN a TYPE int;
ALTER TABLE
(2025-03-04 08:04:36) postgres=# CREATE TABLE t3(a numeric(10,4));
CREATE TABLE
(2025-03-04 08:05:14) postgres=# INSERT INTO t3 VALUES(3.3333);
INSERT 0 1
(2025-03-04 08:05:53) postgres=# ALTER TABLE t3 ALTER COLUMN a TYPE
NUMERIC(10);
ALTER TABLE
(2025-03-04 08:06:19) postgres=# SELECT * FROM t3;
┌───┐
│ a │
╞═══╡
│ 3 │
└───┘
(1 row)
He asked about the possibility of raising an error in these cases. I
understand that implemented behaviour can be messy, more when the user has
not good knowledge about implicit, explicit casts, ANSI/SQL standard and
some other postgresql internals.
Can be nice to have a possibility to execute ALTER with some options like
NOREWRITE or SAFE REWRITE [WITH CHECK] - numeric(6,2) -> numeric(10,4) is
safe, but in the inverse direction it is not safe, and the exception can be
raised (possibly can be checked in runtime).
Regards
Pavel
