• Blog posts

    Bio

    By David Crowther

    faq-postgis-changing-column-data-type-1.PNG

    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?

    faq-postgis-changing-column-data-type-2.png

    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.

    faq-postgis-changing-column-data-type-3.png

    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.

    faq-postgis-changing-column-data-type-4.png

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

    faq-postgis-changing-column-data-type-5.png

    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.