How to Use PostgreSQL to Change the Column Type
Introduction
There are several different commands used in PostgreSQL to change the column type. These include the ALTER TABLE,ALTER COLUMN, TYPE and USING clauses and all have specific functions. For example, theALTER TABLE statement is used to add, delete or modify data in an existing table and the ALTER COLUMN command is used to modify the existing data types in the columns. This tutorial on how to use PostgreSQL to change the column type will provide explanations, instructions and examples for executing these commands.
Prerequisites
PostgreSQL must be properly installed on the local computer to follow the instructions provided in this tutorial.
A good working knowledge of PostgreSQL is required in able to execute the PostgreSQL change column type function.
PostgreSQL Change Column Type Syntax
The following is an example of the PostgreSQL change column type syntax:
1 | ALTER TABLE TABLE_NAME ALTER COLUMN column_name TYPE new_data_type; |
Following is a breakdown of the above syntax:
- The name of the table is specified after the
ALTER TABLEstatement. - The name of the column is specified after the
ALTER COLUMNcommand. - The new data type of the column is set after the
TYPEcommand.
PostgreSQL Change Column Type Examples
First, create a table named “employees” containing five columns as shown here.
1 2 3 4 5 6 | CREATE TABLE employees ( id INT NOT NULL, name TEXT, age VARCHAR, POSITION TEXT, email TEXT ); |
With the table now created, executing the following statement will change the data type of the column name to a VARCHAR data type:
1 | ALTER TABLE employees ALTER COLUMN name TYPE VARCHAR; |
Now executing the \d employees; command will display the table and should produce the following results:
1 2 3 4 5 6 7 8 | Table "public.employees" Column | Type | Collation | Nullable | Default ----------+-------------------+-----------+----------+--------- id | integer | | not null | name | character varying | | | age | character varying | | | position | text | | | email | text | | | |
Notice that the character-type changes were successfully made.
Change the column type of multiple columns
The following example shows how to change the data types of multiple columns by using a comma (,) to separate the columns:
1 2 3 | ALTER TABLE employees ALTER COLUMN POSITION TYPE VARCHAR, ALTER COLUMN email TYPE VARCHAR; |
Again, executing the \d employees; command will display the table and should produce the following results:
1 2 3 4 5 6 7 8 | TABLE "public.employees" COLUMN | TYPE | Collation | NULLABLE | DEFAULT ----------+-------------------+-----------+----------+--------- id | INTEGER | | NOT NULL | name | CHARACTER VARYING | | | age | CHARACTER VARYING | | | POSITION | CHARACTER VARYING | | | email | CHARACTER VARYING | | | |
Changing the column type to an integer
Notice that the “age” column in the table from the previous section has a data type of VARCHAR that seems to be wrong. Executing the ALTER COLUMN command will change the data type to an INTEGER, as shown here:
1 2 3 | ALTER TABLE employees ALTER COLUMN age TYPE INT; ERROR: COLUMN "age" cannot be CAST automatically TO TYPE INTEGER HINT: You might need TO specify "USING age::integer". |
However, the system throws an error message after “age” with instructions after the word “hint” on how to correct it. As the message indicates, executing the USING clause will allow for converting the old column values to new values while changing the data type of the column with the conversion expression.
1 | ALTER TABLE employees ALTER COLUMN age TYPE INT USING age::INTEGER; |
Executing the \d employees; command again should now produce the following results:
1 2 3 4 5 6 7 8 | TABLE "public.employees" COLUMN | TYPE | Collation | NULLABLE | DEFAULT ----------+-------------------+-----------+----------+--------- id | INTEGER | | NOT NULL | name | CHARACTER VARYING | | | age | INTEGER | | | POSITION | CHARACTER VARYING | | | email | CHARACTER VARYING | | | |
Notice that the changes were successfully and the value is now displayed as an INTEGER.
Conclusion
This tutorial explained how to use PostgreSQL to change the column type. The tutorial first explained and provided an example of the PostgreSQL change column type syntax. The article then explained how to create a sample table and provided PostgreSQL change column type examples using the ALTER TABLE,ALTER COLUMN and TYPE commands. The tutorial then explained how to change the column type of multiple columns, how to change the column type to an integer and how to correct an error message with the USING clause. Remember that a comma (,) must be used to separate the columns when changing the data types of multiple columns.
Jump to top
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started