O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required