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?
Add a comment
|
3 Answers
See documentation here: http://www.postgresql.org/docs/current/interactive/sql-altertable.html
ALTER TABLE tbl_name ALTER COLUMN col_name TYPE varchar (11);
7 Comments
Nobu
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;
Darth.Vader
@Nobu why do we have to do this in
some cases and what are those cases?
Jonathan Porter
@Darth.Vader you might need to do this when there is data already existing in the column that can't be cast automatically.
jmathew
Another example for casting: I had a jsonb column with existing data that I needed to convert to text.
Daniel Olson
it's worth mentioning you may want to cast twice in some cases as follows
col_name::text::real; |
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.
1 Comment
TheRealChx101
What about if the column was a NUMERIC with a DEFAULT value. How do you remove or change the default values
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
