Changing a Column's Data Type

Objectives

By the end of this lesson, you should be able to:

• Change a column’s data type after it already exists
• Explain what PostgreSQL checks before allowing the change

💡 Why this matters: A VARCHAR(50) picked early on sometimes turns out too short, or a column needs to become more flexible. ALTER COLUMN ... TYPE changes it without recreating the table.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from Module 3.

Changing a Type

ALTER TABLE employees ADD COLUMN notes VARCHAR(100);
ALTER TABLE employees ALTER COLUMN notes TYPE TEXT;

 column_name | data_type
-------------+-----------
 notes       | text

notes widened from a 100-character limit to unlimited text. Existing data is preserved, PostgreSQL only needs to convert each existing value to fit the new type.

When PostgreSQL Says No

Widening a type (like VARCHAR(50) to VARCHAR(100), or VARCHAR to TEXT) is always safe, every existing value already fits. Narrowing one, or converting to an incompatible type, only succeeds if every existing value can actually convert. Shrinking a VARCHAR(100) column down to VARCHAR(10) while a row holds a 50-character value fails the same way a too-long INSERT would (Module 3), and converting a text column full of non-numeric values to INTEGER fails outright, there’s nothing sensible to convert 'Engineering' into as a number.

Try It

1. Add a middle_name column of type VARCHAR(30) to employees.
2. Change middle_name’s type to VARCHAR(60), and confirm it succeeds.
3. Insert a value into middle_name longer than 30 characters but shorter than 60, confirming the widened limit actually applies.
4. Explain, in your own words, why shrinking a column’s type is riskier than widening it.

Recap

• ALTER TABLE ... ALTER COLUMN name TYPE new_type changes a column’s data type, existing data is preserved when possible.
• Widening a type is always safe, narrowing one only succeeds if every existing value can actually convert.

Next lesson: SET/DROP DEFAULT and SET/DROP NOT NULL, adjusting constraints on a column that already exists.