By David Crowther
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?
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.