Ability to ALTER COLUMN changing data type

[hlcianfagna]

Problem Statement

After a system has been live and ingesting real data for a while, it may become desirable to change the data types on a table.
We may for instance want more precision to deal with new use cases, or we may realise that we can do with less precision and want to save space.

Possible Solutions

• ADD COLUMN, copy existing data, DROP COLUMN, rename column?
• an ALTER COLUMN statement that would allow forcing the cast on all existing values (like in other systems like in PostgreSQL - with ALTER COLUMN ... TYPE ... USING)

PostgreSQL Docs:

• https://www.postgresql.org/docs/current/sql-altertable.html

Example showing the behavior:

create table tbl (x text);
insert into tbl (x) values ('foo');
insert into tbl (x) values ('10');

alter table tbl alter column x type integer;
ERROR:  42804: column "x" cannot be cast automatically to type integer
HINT:  You might need to specify "USING x::integer".
LOCATION:  ATPrepAlterColumnType, tablecmds.c:12302

# alter table tbl alter column x type integer using x::integer;
ERROR:  22P02: invalid input syntax for type integer: "foo"
LOCATION:  pg_strtoint32, numutils.c:232
Time: 0.579 ms

# delete from tbl where x = 'foo';
# alter table tbl alter column x type integer using x::integer;

This implies re-indexing and transactions. We'd need an alternative way to handle this

Considered Alternatives

Move the data to a new table

[matriv]

Since we're going to use drop column and then re-add it with the new data type, we need to document that if the table is already populated, the existing data for the column are lost, we cannot re-index them using the new type, correct?

[jeeminso]

Maybe we can add > update > drop? No