Changing Column Data Types

Imagine you’re creating a students table for a university. Again! :)

At first, you decide that the field for age age will be an integer and set the type to SMALLINT (good for numbers from -32,768 to 32,767). But after a while, your database grows, and you start adding info about students from other countries who specify their age... in days since birth! Now your SMALLINT is getting cramped — time to switch, for example, to INTEGER.

Here are a few more common cases when you need to change a data type:

1. Increasing or decreasing the range of numbers.
2. Changing string length (for example, from VARCHAR(50) to VARCHAR(100)).
3. Switching to another data type for optimization (like converting TEXT to VARCHAR).
4. Mistake when initially picking the column type (like you set BOOLEAN instead of INTEGER).

Syntax for Changing Data Types

In PostgreSQL, changing a column’s data type is done with the ALTER TABLE command. It lets you adapt your table’s structure for new needs.

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type;

It’s super simple: you specify the table name, the exact column you want to change, and the new data type for it.

Example 1: Switching from INTEGER to BIGINT

Let’s say we have a students table:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER
);

Everything was fine until someone’s age went into the millions (don’t ask, it’s just an example!). To keep PostgreSQL happy, let’s change the age column from INTEGER to BIGINT:

ALTER TABLE students
ALTER COLUMN age TYPE BIGINT;

Example 2: Increasing String Length

You created a table to store courses and set their names to be up to 50 characters long:

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

But suddenly it turns out that course names are way more complicated and longer than you expected. No problem:

ALTER TABLE courses
ALTER COLUMN name TYPE VARCHAR(150);

Example 3: Type Conversion

Let’s say we had a table where the birth_date field was stored as text:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    birth_date TEXT
);

You realize that working with dates as TEXT is a pain, because you can’t filter or sort them. The fix? Convert TEXT to DATE:

ALTER TABLE employees
ALTER COLUMN birth_date TYPE DATE USING birth_date::DATE;

Notice the USING birth_date::DATE part. It tells PostgreSQL to convert the data before changing the type.

Why Do You Sometimes Need Explicit Data Conversion?

When PostgreSQL tries to change a type, it’ll try to automatically cast the existing data to the new type. If it can’t, you’ll get an error. For example, changing a TEXT field to INTEGER without telling it how to interpret the text will fail.

Example of a Problem

ALTER TABLE employees
ALTER COLUMN birth_date TYPE DATE;
-- Error: can’t convert value 'not a date' to type DATE.

There’s a fix for this. Add explicit data conversion using USING:

ALTER TABLE employees
ALTER COLUMN birth_date TYPE DATE USING to_date(birth_date, 'YYYY-MM-DD');

Here we use the to_date() function to convert strings to date format.

The USING Clause

In PostgreSQL, when you change a column’s type with ALTER TABLE ... ALTER COLUMN ... TYPE, sometimes you have to specify how to convert the existing data — and that’s where the USING keyword comes in.

Syntax:

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type
USING expression;

Explanation:

• USING lets you explicitly specify a conversion formula from the old type to the new one.
• This is especially handy when automatic conversion isn’t possible or is ambiguous.

Simple Example: String → Number

ALTER TABLE users
ALTER COLUMN age TYPE INTEGER
USING age::INTEGER;

Here age was originally TEXT, and we want to convert it to INTEGER. USING age::INTEGER is an explicit type cast.

Example: Text → Date

ALTER TABLE events
ALTER COLUMN event_date TYPE DATE
USING TO_DATE(event_date, 'YYYY-MM-DD');

If event_date was text like '2023-10-25', we’re telling PostgreSQL how to turn it into a DATE.

When is USING Required?

• When there’s no direct type cast.
• When you need to transform the data.
• When types are incompatible (TEXT → BOOLEAN, VARCHAR → INTEGER, etc.).

Common Mistakes When Changing Data Types

Error when not converting data. If the data can’t be automatically cast to the new type, you have to specify how to convert it using USING.

ALTER TABLE employees
ALTER COLUMN birth_date TYPE DATE;
-- Error: column 'birth_date' contains invalid values for type DATE

The operation locks the table. Keep in mind that changing a data type can lock the table for writes until the operation is done. This is especially important for big tables. Plan changes for low-traffic times.

Problems with related tables and foreign keys. If the column is part of a foreign key, changing its type can get tricky. PostgreSQL will require you to recreate the foreign keys.

Handy Tips

Always check your current data. Use queries like SELECT DISTINCT column_name before changing the type to see if the data can be converted without errors.

Test your changes. Make a temporary copy of the table and experiment with it before changing the main table. For example:

CREATE TEMP TABLE temp_students AS SELECT * FROM students;

Don’t forget about USING. It’s your lifesaver when the data type changes radically (like TEXT → NUMERIC).

Now you know how to change a column’s data type in PostgreSQL. Hope next time you need to rethink your data structure, you’ll feel confident. Tables are smart, but even they need an upgrade sometimes!