Database Administration - PostgreSQL 9 Administration - page 159

Changing datatype of a column

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.

Getting ready

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);

How to do it...

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 that we can try moving to something
more complex, such as a date datatype, like the following:

postgres=# ALTER TABLE birthday
postgres-# ALTER COLUMN dob SET DATA TYPE integer;
ERROR: column "dob" cannot be cast to type integer

Oh! What went wrong? Let's try using an explicit conversion with the USING clause as follows:

postgres=# ALTER TABLE birthday
ALTER COLUMN dob SET DATA TYPE integer
USING dob::integer;
ALTER TABLE