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
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.
Make PostgreSQL Fun Again With Beekeeper Studio
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
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
The syntax looks like this:
ALTER TABLE tableName ALTER COLUMN columnName TYPE newDataType USING (expressionGoesHere);
Using ‘USING’ When Changing a Column Type In PostgreSQL
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
TEXTconversions, and any other conversions that are binary compatible.
- These commits for not locking when increasing or removing type lengths (eg going from
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:
- Create a new column, let’s call it title_length:
ALTER TABLE books ADD COLUMN title_length INTEGER NOT NULL DEFAULT 0;
- Set up a create/update trigger to write to this new column automatically when writing to the old one, something like this
- Backfill the new column manually with values from the old column. In our case
UPDATE books SET title_length = char_length(title)
- Update all of your code to start using the new column.
- Either rename the old column 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.