Changing column datatypes is not an everyday task, thankfully. But when we do have to do it, we need to know all the details so that we can perform the conversion on a production system without error.
Let's start with a simple example table as follows:
postgres=# select * from birthday; name | dob -------+-------- simon | 690926 (1 row)
It is created using the following:
CREATE TABLE birthday ( name TEXT , dob INTEGER);
Let's say we want to change the
dob column to another data type. Let's try with a simple example first, which is as follows:
postgres=# ALTER TABLE birthday postgres-# ALTER COLUMN dob SET DATA TYPE text; ALTER TABLE
This works fine. Let's just put that back to integer, so ...