PostgreSQL - Change The Type Of A Column, A Walkthrough With Examples

Shrikaran — Tue, 10 Oct 2023 18:52:59 +0000

How to change a PostgreSQL column type without messing up your table, losing data, or taking down your database.

"In the world of databases, evolution is key. Altering tables is the chisel that sculpts raw data into refined information, ensuring your applications stay agile and responsive to the demands of tomorrow."

When operating a PostgreSQL database, we might at some point need to change the data type of a column. For example, maybe changing a TEXT type column into VARCHAR.

PostgreSQL provides a simple syntax to do this, which we’ll walk through in this article. If you’re operating a complex or large database there are some gotchas, also explored below.

Syntax for PostgreSQL Column Type Change

To change a column type we need to run an ALTER TABLE statement, specifying the column and the new data type. Unlike some other databases (eg. MySQL) you can change a column type without having to also change everything else, like nullability or the default value.

Here’s a basic example:

ALTER TABLE tableName ALTER COLUMN columnName SET DATA TYPE newDataType;

SET DATA TYPE can be shortened to simply TYPE, which I prefer:

ALTER TABLE tableName ALTER COLUMN columnName TYPE newDataType;

PostgreSQL Change Column Type Examples

First, for demonstration purposes, let’s create a books table and insert some data.

CREATE TABLE books (
    id serial PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL DEFAULT 'Unknown Author',
    pulished_date DATE NOT NULL
);

INSERT INTO books(id, title, author, pulished_date) 
VALUES(1001,'How to use PostgreSQL','Matthew Rathbone','1989-03-07'),(1001,'Sunshine is Lovely','Joe Bloggs','2020-02-05');

Suppose we want to change the data type of the title column from VARCHAR to TEXT. We can do this by executing the following PostgreSQL code.

ALTER TABLE book ALTER COLUMN title TYPE TEXT;

We can also change the types of more than one column at a time. This can be done in a single statement using commas for each column change.

ALTER TABLE book 
ALTER COLUMN title TYPE TEXT,
ALTER COLUMN author TYPE TEXT;

This is straightforward, and it works because Postgres allows changes between compatible data types implicitly, that means it converts the values automatically. This isn’t always true, which we’ll see below.

Constraint Problems Caused By Changing Column Type in PostgreSQL

If you are altering a column type, it may make some of your constraints incompatible. For example the DEFAULT value for a column, or any type of foreign key relationship. It is recommended that you at least review your constraints before changing a column type.

Changing a PostgreSQL Column To An Incompatible Data Type

PostgreSQL also allows us to change between incompatible data types while preserving the data. For example, suppose you want to change a TEXT data type column to INTEGER. You might think it was as simple as this:

ALTER TABLE book ALTER COLUMN title TYPE integer;

However, When we execute the statement, it will return an error.

ERROR: column "title" cannot be cast automatically to type integer SQL state: 42804. 
Hint: Specify a USING expression to perform the conversion

PostgreSQL doesn’t know how to cast the column data into an INTEGERfrom a VARCHAR. We’ll have to tell PostgreSQL how to do this explicitly with the USING keyword.

The syntax looks like this:

ALTER TABLE tableName
ALTER COLUMN columnName TYPE newDataType USING (expressionGoesHere);

Using ‘USING’ When Changing a Column Type In PostgreSQL

Grammar aside, USING allows us to explicitly convert column data when changing the column type.

ALTER TABLE book
ALTER COLUMN title TYPE INTEGER USING (char_length(title));

In this simple example we’re converting the value of the title column to be a character count for the original title using a built in PostgreSQL function.

While this example is fabricated, this is a foundation for much more complex and useful transformations. For example we could create a custom function that performs a complex operation and use this instead.

USING Is Not Always Required For Column Type Changes

PostgreSQL will implicitly cast the common values and the constraints to the new data type if you do not use the USING clause, but this doesn’t work for all column type changes. When we use the USING clause, we provide an explicit way to transform the values.

If you do not use the USING clause, castings can fail and throw errors, like we saw above.

WARNING: PostgreSQL Column Type Changes Will Lock The Table

When changing the type of a column to a type that has a different internal representation, PostgreSQL will lock the table with an ACCESS EXCLUSIVE lock in order to rewrite the whole table on disk. This is crazy slow and expensive, we do not want this to happen to a production database in the middle of the day!

There are some type changes that do not require a lock, but there’s no single place where these are documented (they’re not documented on the ALTER TABLE docs for example), so it’s kind of hard to tell what sort of pain your change will cause.

There are a few hints at exceptions in the PostgreSQL mailing lists, for example:

This Commit for not locking for VARCHAR -> TEXT conversions, and any other conversions that are binary compatible.
These Commits for not locking when increasing or removing type lengths (eg going from VARCHAR(100) to VARCHAR(255)).

Honestly it’s hard to know for certain if a table lock will be required by just looking through documentation.

Fear not, there is an alternative.

An Alternative to Changing Column Type in PostgreSQL - Adding a New Column Instead

Changing a column type is fairly simple, but for a large database with millions of records and a lot of concurrent queries it is also very expensive because of the ACCESS EXCLUSIVE table lock.

If we are working with a large or busy database, or we just want to have some extra assurances that we will not lose data, I prefer simply adding a new column rather than changing the type of an existing one.

This workflow looks something like:

1.Create a new column, let’s call it title_length:

ALTER TABLE books ADD COLUMN title_length INTEGER NOT NULL DEFAULT 0;

2.Set up a create/update trigger to write to this new column automatically when writing to the old one, something like this.
3.Backfill the new column manually with values from the old column. In our case

UPDATE books SET title_length = char_length(title);

4.Update all of your code to start using the new column.
5.Either rename the old column to deprecated_title and rename title_length to title, or just drop the old title column (and the temporary trigger) entirely.

Wow, that’s way more information than I thought we’d have to go through. It’s a lot to remember!

Remember, always review your constraints before making any column type changes. This will help ensure a smooth transition without compromising data integrity. Happy coding!

DEV Community: Shrikaran