How to alter a column's data type in a PostgreSQL table?

Question

Entering the following command into a PostgreSQL interactive terminal results in an error:

ALTER TABLE tbl_name ALTER COLUMN col_name varchar (11);

What is the correct command to alter the data type of a column?

Lebbie

Answer 1

See documentation here: http://www.postgresql.org/docs/current/interactive/sql-altertable.html

ALTER TABLE tbl_name ALTER COLUMN col_name TYPE varchar (11);

Brammer

For some other cases, you might need to specify the way to cast like ALTER TABLE tbl_name ALTER COLUMN col_name TYPE integer USING col_name::integer; – Demission

@Demission why do we have to do this in some cases and what are those cases? – Hippocras

@Hippocras you might need to do this when there is data already existing in the column that can't be cast automatically. – Skiascope

Another example for casting: I had a jsonb column with existing data that I needed to convert to text. – Weeks

in some cases you might also need to combine the cast with nullif expression, particularly if you've already had a record with a null value on the field/column that you're looking to change. In my case, i was trying to change the column type from varchar to uuid, but i have null value in the field that i'd like to change. So i got an invalid input syntax error. To resolve the situation i combine the alter statement with a nullif expression such follow: alter table user alter column branch_id type uuid using (nullif(branch_id,''))::uuid; – Dowel

it's worth mentioning you may want to cast twice in some cases as follows col_name::text::real; – Bencion

Answer 2

If data already exists in the column you should do:

ALTER TABLE tbl_name ALTER COLUMN col_name TYPE integer USING (NULLIF(col_name, '')::integer);

As pointed out by @nobu and @jonathan-porter in the comments to @derek-kromm's answer, somewhat cryptically.

Walkabout

What about if the column was a NUMERIC with a DEFAULT value. How do you remove or change the default values – Thymic

Answer 3

Cool @derek-kromm, Your answer is accepted and correct, But I am wondering if we need to alter more than the column. Here is how we can do.

ALTER TABLE tbl_name 
ALTER COLUMN col_name TYPE varchar (11), 
ALTER COLUMN col_name2 TYPE varchar (11),
ALTER COLUMN col_name3 TYPE varchar (11);

Documentation

Cheers!! Read Simple Write Simple

Lodicule