Postgresql Datatype mismatch default for column cannot be cast to type integer

When converting a table column from varchar to integer I ran into a problem.

[code lang=text]
SQLSTATE[42804]: Datatype mismatch: 7 ERROR: default for column "column_name" cannot be cast to type integer
[/code]

The solution I could find on (Stackoverflow)[http://stackoverflow.com/questions/13170570/change-type-of-varchar-field-to-integer-cannot-be-cast-automatically-to-type-i] was not working:

[code lang=sql]
ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer);
[/code]

Looking closer at the error I realised – as is often the case – the error was indicating exactly what the problem was. The default of the column could not be converted.

Not knowing how to convert the default, I simply changed it to something that could be converted:

[code lang=sql]
ALTER TABLE the_table ALTER COLUMN col_name SET DEFAULT 0;
[/code]

Despite it being a varchar column, the integer value 0 was accepted. After which the earlier CAST worked.

Install Debian wheezy-backports repository

The Debian backports repository contain packages or newer versions of packages that did not make it into the original release. You can read more about backports on the Debian website.

To install wheezy-backports you need to either add the repository line:

deb http://http.debian.net/debian wheezy-backports main

to your /etc/apt/sources.list file.

Alternatively you can create a new sources list file with this command (run as root):

# echo 'deb http://http.debian.net/debian wheezy-backports main' > /etc/apt/sources.list.d/wheezy-backports.list

Once installed, run apt-get update:

# apt-get update

Select individual packages to install:

# apt-get -t wheezy-backports install "package"

Photo credit: Giulio Turetta