PostGIS - Changing Column Data Types

By David Crowther

Question:

I am trying to change the column type in my PostGIS table, but when I run the update I get an error – SQL State: 42804 – what is causing this?

Answer:

This issue is likely caused because the column already has some values in it, and so won’t allow you to change the data types.

In this example I have a PostGIS table of Planning Apps and I am using a column called app_id to store their unique id, but when I created the field, by mistake I set the column type to Character Varying.

Because values already exist in the apps_id field we can’t simply change the column type to be Integer. Instead we will need to use a USING expression to CAST the column type as Integer.

If we now view the attributes again for the table, we can see that the app_id column is now type Integer.

Note - The USING expression can also be used if you need to concatenate two or more fields and their column types vary. We will explore this in another FAQ.