Reputation: 21781
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?
Upvotes: 327
Views: 543101
Reputation: 3993
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.
Upvotes: 76
Reputation: 3315
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
Upvotes: 23
Reputation: 23318
See documentation here: http://www.postgresql.org/docs/current/interactive/sql-altertable.html
ALTER TABLE tbl_name ALTER COLUMN col_name TYPE varchar (11);
Upvotes: 496