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

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 INTEGER from 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.

Perform PostgreSQL Column Type Changes Without Remembering the SQL

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

Good news for your memory: If you’re just performing a simple column type change, or you are re-typing a column that does not cause a full table lock, you should probably just use Beekeeper Studio’s clean, modern UI instead. You can change column types quickly and easily. If you need to drop down to regular SQL, you can do that too.