One question here, if we have defined one column as a fixed length data type "integer" and slowly we noticed the length of data keeps increasing (in case of a sequence generated PK column which will keep increasing), and we want to alter the column to "bigint" now. In such scenario, will it append/pad the additional spaces to all the existing values which were already stored with integer type initially in the table. And that would be then an increase to the existing table storage. Please correct me if I'm wrong.
ALTER TABLE foo ALTER COLUMN bar BIGINT; rewrites the whole table. Do yourself a favor, and start with BIGINT.
Not yet tried, but that's interesting. I was initially thinking as it will be increasing the length, so it would be just a metadata change and finish within seconds.
But as you mentioned, it seems to be the effect of "fixed length data type" which is why it's going to rewrite whole table even we just increases the column length. Hope it won't be the case in variable length data type.
