- Trending Categories
- Data Structure
- Operating System
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to change the type of a column in PostgreSQL?
In order to change the type of a column, the syntax is
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type USING expression;
Please note that altering the type of a column is not recommended generally, especially if your table has a lot of entries already.
The USING part of the syntax is optional. It is used when you need to use an expression for converting the existing entries in that column from the current type to the new type. For instance, if you convert a column of type INTEGER to type BIGINTEGER, you need not use any expression for the conversion of existing entries to the new types.
However, if you convert a TEXT or a VARCHAR entry to INTEGER, you will not be able to do that by default, unless you provide an expression for converting the existing TEXT/VARCHAR values to an integer.
Let us understand both the cases using an example. Let us create a new table marks −
CREATE TABLE marks( serial_no INTEGER PRIMARY KEY, name VARCHAR, roll_no INTEGER, marks_obtained INTEGER, perc_marks DOUBLE PRECISION, max_marks INTEGER, date_of_entry DATE );
Now, let us populate it
INSERT INTO marks(serial_no,name, roll_no, marks_obtained, perc_marks, max_marks, date_of_entry) VALUES (1,'Yash', 26, 42, 42.0, 100, current_date), (2,'Isha', 56, 175, 87.5, 200, current_date);
Now, suppose we want to convert the serial_no column from type INTEGER to BIGINTEGER. We need not use any expression for converting the column type.
ALTER TABLE marks ALTER COLUMN serial_no TYPE BIGINT;
Now, for understanding the cases where we need to use an expression, let us first convert the roll_no column to VARCHAR type.
ALTER TABLE marks ALTER COLUMN roll_no TYPE VARCHAR;
This statement will go through. Because integer to text conversion does not confuse PostgreSQL. It simply replaces the digit with the character representing that digit. 26 becomes ‘26’. Now, let us try to convert the roll_no column back to an integer
ALTER TABLE marks ALTER COLUMN roll_no TYPE INTEGER;
PostgreSQL will now throw up an error. This is because the conversion of text to integer is not that straightforward for PostgreSQL. Specifically, the error will read −
ERROR: column "roll_no" cannot be cast automatically to type integer HINT: You might need to specify "USING roll_no::integer". SQL state: 42804
This is great. PostgreSQL is even providing us with a hint. It is asking us to cast the roll_no values into integer type explicitly. :: represents CAST operator in PostgreSQL.
Let us take PostgreSQL’s hint and run −
ALTER TABLE marks ALTER COLUMN roll_no TYPE INTEGER USING roll_no::integer;
It will work now.
- How can we change the data type of the column in MySQL table?
- How to add column to an existing table in PostgreSQL?
- How to change the type of a field in MongoDB?
- How To Change PostgreSQL Data Folder Location on Ubuntu 16.04
- How to combine different columns of a table to yield a single column in query output in PostgreSQL?
- How to create a table in PostgreSQL?
- How to alter the data type of a MySQL table’s column?
- How to change each column width of a JTable in Java?
- Extract day, hour, minute, etc. from a datetime column in PostgreSQL?
- How to change the column position of MySQL table without losing column data?
- How to Query a DB in pgAdmin in PostgreSQL?
- How to update a MySQL date type column?
- How to change the name of single column using setNames in R?
- How to insert a Python tuple in a PostgreSql database?
- How to change any data type into a string in Python?