Mastering PostgreSQL 12 - Third Edition

Changing column definitions

Let's see what happens if you want to modify the table definition:

MariaDB [test]> ALTER TABLE data MODIFY data numeric(3, 2); Query OK, 1 row affected, 1 warning (0.06 sec) Records: 1 Duplicates: 0 Warnings: 1

You should see a problem here:

MariaDB [test]> SELECT * FROM data; +----+------+ | id | data | +----+------+ | 1  | 9.99 | +----+------+ 1 row in set (0.00 sec)

As you can see, the data has been modified again. It shouldn't have been there in the first place and has been changed all over again. Remember, you might have lost money again, or some other nice asset, because MySQL tried to be clever.

This is what happens in PostgreSQL:

test=# INSERT INTO data VALUES (1, 34.5678); INSERT  0 1 test=# SELECT * FROM ...