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

SqlPostgresqlPsqlAlter TableAlter Column

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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

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

Solution 2 - Sql

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.

Solution 3 - Sql

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

Categories
Recommended Sql Solutions
Recommended Postgresql Solutions
Recommended Psql Solutions
Recommended Alter Table Solutions
Recommended Alter Column Solutions

Previous Solution:

The term "Context" in programming?

Language AgnosticProgramming LanguagesTerminology

Next Solution:

How to merge dictionaries of dictionaries?

PythonDictionaryMergeArray Merge

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionkn3lView Question on Stackoverflow
Solution 1 - SqlDerek KrommView Answer on Stackoverflow
Solution 2 - SqlandscharView Answer on Stackoverflow
Solution 3 - SqlLalit MohanView Answer on Stackoverflow