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?
See documentation here: http://www.postgresql.org/docs/current/interactive/sql-altertable.html
ALTER TABLE tbl_name ALTER COLUMN col_name TYPE varchar (11);
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.
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
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- PostgreSQL "DESCRIBE TABLE"
- How can I add a column that doesn't allow nulls in a Postgresql database?
- How do you change the datatype of a column in SQL Server?
- Check if a temporary table exists and delete if it exists before creating a temporary table
- How can I drop all the tables in a PostgreSQL database?
- Altering a column to be nullable
- How to exit from PostgreSQL command line utility: psql
- Run a PostgreSQL .sql file using command line arguments
- How to change PostgreSQL user password?
ALTER TABLE tbl_name ALTER COLUMN col_name TYPE integer USING col_name::integer;some casesand what are those cases?