Changing the data type of a column
Thankfully, changing column data types is not an everyday task, but we must understand the behavior to ensure we can execute the change without any problem when we need to do it.
Getting ready
Let’s start with a simple example of a table, with just one row, as follows:
CREATE TABLE birthday
( name TEXT
, dob INTEGER);
INSERT INTO birthday VALUES ('simon', 690926);
postgres=# select * from birthday;
This gives us the following output:
name | dob
-------+--------
simon | 690926
(1 row)
How to do it…
Let’s say we want to change the dob
column to another data type. Let’s try this with a simple example first, as follows:
postgres=# ALTER TABLE birthday
postgres-# ALTER COLUMN dob SET DATA TYPE text;
ALTER TABLE
This works fine. Let’s just change that back to the integer
type so that we can try something more complex, such as a date
data type:
postgres...