CODEWITHSUNDEEP
sqlpostgresqlpsqlalter-tablealter-column
tree em
tree em

Reputation: 21781

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

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

Answers (3)

andschar
andschar

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

Lalit Mohan
Lalit Mohan

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

Documentation

Cheers!! Read Simple Write Simple

Upvotes: 23

Derek
Derek

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

Related Questions