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?

score 386 · Accepted Answer · edited Aug 25 '16 at 07:14

386

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

edited Aug 25 '16 at 07:14

Martijn Pieters

answered Aug 23 '11 at 14:49

Derek

81

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;` – Nobu May 01 '14 at 17:45
6

@Nobu why do we have to do this in `some cases` and what are those cases? – Darth.Vader Oct 12 '17 at 17:11
6

@Darth.Vader you might need to do this when there is data already existing in the column that can't be cast automatically. – Jonathan Porter Mar 29 '19 at 13:40
1

Another example for casting: I had a jsonb column with existing data that I needed to convert to text. – jmathew Oct 21 '20 at 17:29
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;` – yauritux Jun 01 '23 at 01:58

andschar · Answer 2 · 2021-06-24T18:48:02.633

60

If data already exists in the column you should do:

ALTER TABLE tbl_name ALTER COLUMN col_name TYPE integer USING col_name::integer;

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

edited Jun 24 '21 at 18:48

answered Feb 18 '21 at 14:02

andschar

What about if the column was a NUMERIC with a DEFAULT value. How do you remove or change the default values – TheRealChx101 Mar 15 '22 at 23:49

score 14 · Answer 3 · answered Feb 18 '21 at 11:33

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);

Cheers!! Read Simple Write Simple

3 Answers3