How to Change Column Type in PostgreSQL

Galaxy Glossary

How do I change a column’s data type in PostgreSQL without losing data?

ALTER TABLE ... ALTER COLUMN ... TYPE lets you convert an existing column to a new data type without dropping or recreating the table.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why would you change a column’s data type?

Adjusting a column’s type fixes earlier design decisions, aligns with new business rules, improves storage efficiency, or unlocks built-in functions unavailable for the old type.

What is the official syntax?

Use ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type. Add USING expression to control how old values cast to the new type.

Basic syntax example

ALTER TABLE Orders ALTER COLUMN total_amount TYPE numeric(12,2);

Using the USING clause

ALTER TABLE Products ALTER COLUMN price TYPE numeric(10,2) USING price::numeric; casts each price value explicitly, preventing implicit-cast errors.

How do I change a numeric column to text?

ALTER TABLE Orders ALTER COLUMN total_amount TYPE text USING total_amount::text; converts money figures to string while preserving values.

How do I convert text to timestamp safely?

ALTER TABLE Customers ALTER COLUMN created_at TYPE timestamp USING to_timestamp(created_at,'YYYY-MM-DD'); guarantees proper parsing of the existing date strings.

Can I rename and convert in one statement?

Yes. Chain commands with commas: ALTER TABLE Orders RENAME COLUMN total TO total_amount, ALTER COLUMN total_amount TYPE numeric(12,2);

Best practices

1) Always test casting with SELECT column::new_type first. 2) Wrap change in a transaction. 3) Create backups for production tables. 4) Add indexes only after the type change to avoid double work.

What are common pitfalls?

1) Ignoring USING when implicit casts fail causes errors. 2) Changing a type referenced by foreign-key or index may block for longer; plan maintenance windows.

Why How to Change Column Type in PostgreSQL is important

How to Change Column Type in PostgreSQL Example Usage


-- Convert quantity from smallint to integer in OrderItems
BEGIN;
ALTER TABLE OrderItems
    ALTER COLUMN quantity TYPE integer
    USING quantity::integer;
COMMIT;

How to Change Column Type in PostgreSQL Syntax


ALTER TABLE <table_name>
    ALTER [COLUMN] <column_name> TYPE <new_data_type>
        [USING <expression>]
    [SET/DROP DEFAULT <value>]
    [SET NOT NULL | DROP NOT NULL];

-- Ecommerce examples
-- 1. Convert price from integer cents to numeric dollars
ALTER TABLE Products
    ALTER COLUMN price TYPE numeric(10,2)
    USING price / 100.0;

-- 2. Change order_date from text to date
ALTER TABLE Orders
    ALTER COLUMN order_date TYPE date
    USING to_date(order_date,'YYYY-MM-DD');

Common Mistakes

Omitting the USING clause Why it’s wrong: PostgreSQL attempts an implicit cast. If any value cannot convert, the whole command fails. Fix: Provide an explicit USING expression: price::numeric or to_date(order_date,'YYYY-MM-DD').
Running on a live table during peak hours Why it’s wrong: Type changes rewrite the whole table, holding exclusive locks and blocking reads/writes. Fix: Schedule maintenance windows or use partitioning to minimize lock time.

Frequently Asked Questions (FAQs)

Does ALTER COLUMN TYPE lock the table?

Yes. PostgreSQL takes an ACCESS EXCLUSIVE lock, blocking concurrent queries. For large tables, schedule off-hours or use logical replication for zero-downtime migrations.

Can I revert a type change?

Yes, but you must run another ALTER COLUMN TYPE back to the original type, possibly with a USING clause. Always keep a backup before altering.

Is data copied or rewritten?

Most type changes rewrite the entire table. PostgreSQL 13+ can skip rewriting for some binary-compatible conversions (e.g., varchar length increase).

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie