PostgreSQL Change Column Type

You may have heard or known about SQL. Technically SQL is a language for databases that allows managing the stored information in the database using the tables by querying these tables and other related objects. Likewise, SQL, PostgreSQL also support manipulation in the databases with the help of queries. A user can easily alter data types in PostgreSQL. In this article, our main focus is to examine the concept of PostgreSQL and other relevant information related to PostgreSQL. Most importantly we will discuss how to change column type in PostgreSQL using Windows 10 with some interesting examples that will clear the concept about PostgreSQL queries.

PostgreSQL allows a user to do multiple variations with the stored data. A user can add a column, delete a column, create an index, change the column name, and most interesting you can also change the column data type of the database. Furthermore, there are multiple commands in PostgreSQL through which we can change the column data type from one to another which includes ‘ALTER TABLE’, ‘USING’, ‘TYPE’, and ‘ALTER COLUMN. All these commands have their functionality such as the ‘ALTER TABLE’ statement is used to add, delete, and alter the data in the existing table whereas the statement ‘ALTER COLUMN’ is used for altering the column’s existing data types.

Now, let’s discuss the technical and coding part of PostgreSQL. The main aim of this article is to make you familiar with PostgreSQL and how to change the column type in PostgreSQL in Windows 10. Below are some easy-to-understand examples that you will find useful in the context of how to change column type in PostgreSQL using its queries. For manipulating the data type stored in the database using PostgreSQL queries and commands, we need a table. For this requirement, we have formed the below table and inserted some records into it.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-1.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

The above table consists of three columns namely; table_name, column_name, and data_type. Using this table, we will change the existing data types into another by using multiple methods.

Example_01: Changing column type from integer to varchar in Windows 10

In this example, we are going to change the data type of column name ‘phone’ from ‘integer’ to ‘varchar’. To get the desired output, write this query in PostgreSQL.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-2.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

In PostgreSQL, we use the statement ‘ALTER TABLE’ to change the data type of that particular table. The above command shows that it is altering the data type of a column “phone” from integer to varchar with user-defined length as a phone number consists of 11 digits. Below is the respective result of the query.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-3.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

Example_02: Changing multiple column types from character to varchar using a single statement

Example_02: Changing multiple column types from character to varchar using a single statement

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-4.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

In the above query, we are changing the data type to more than one column. In the above example, we have mentioned the particular table name ‘tblaqsayasin’ after the statement ‘ALTER TABLE’. Afterward, we have written the column names that are ‘first name’ and ‘last name’ right after the statement ‘ALTER COLUMN’ that will alter the data types of the specific columns. Now after the command ‘TYPE’, we have mentioned the desired data type of the columns which we have entered in VARCHAR (200). Here, we have ‘200’ is the user-defined string length of the first name and last name respectively. Complete the command line by putting the terminator at the end of it. After executing it, below is the desired output of the above example.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-5.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

Example_03: Changing column data type from varchar to integer

In this example, we will show you how to change any column data type from varchar to integer. This one example is slightly different from others as before changing any column type into an integer we have to cast the varchar data type into the integer with the help of command ‘USING’. To make it more clear why we are using the casting concept here, let’s consider the below commands.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-6.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

We have written the above query following the pattern through which we have executed the previous examples but when you will execute it, this particular error will occur.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-7.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

To counter this error, we use the casting concept to convert all the characters into integers first.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-8.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

In the above piece of query, we have used the keyword ‘USING’ to change the ‘deptno’ data type in the integer. Whereas the rest of the syntax remains the same. Below is the result of the above PostgreSQL query.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-9.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

Example_04: Changing column data type from varchar to Boolean and Jsonb

Now, to make you better understand the usage of the ‘USING’ keyword in terms of changing the varchar data type to any other data type. In this example, we have modified our table and inserted some additional data into it. Here is the updated table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-10.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

As you can see, we have added two new columns namely; isactive and description with data type varchar. In this example, our goal is to convert the varchar data type of the respective columns into a Boolean data type and JSON objects. To do so, follow the syntax of the below query:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-11.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

We have used the ‘ALTER TABLE’ statement to alter the table name ‘tblaqsayasin’. After the selected column name is mentioned which is ‘description’ whose data type will be manipulated with the help of the query from varchar to Jsonb. Complete the query by adding a terminator at the end and after executing it you will get the below resultant table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-12.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

Following the above syntax and format with the help of the ‘USING’ keyword, we will also change the data type of column name ‘isactive’ from varchar to ‘boolean’.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-13.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

Using the above query, we will get our desired output and then the resultant table looks like this after executing this query successfully.

OUTPUT:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/02/echo/PostgreSQL-Change-14.png" data-lazy- height="800" src="data:image/svg xml,” width=”1280″>

As you can see in the above table, the data type of column name ‘isactive’ is changed or updated from varchar to Boolean.

Conclusion:

In this article, we have discussed the basics of SQL and its purpose along with its comparison with the functionality and features of PostgreSQL. We have tried our level best to make you understand the concept of PostgreSQL and how to change column data types using PostgreSQL queries in Windows 10. Also, we have given you a base of all the commands or statements that are used for modifying the column types before explaining the examples for your convenience. In the above article, we have mentioned easy-to-understand multiple examples of how to change the data type of any column from integer to varchar and from varchar to Boolean, integer, and Jsonb with the help of the ‘USING’ keyword. I hope this article will help you in the implementation of the PostgreSQL queries.