Change column name Postgresql

In PostgreSQL, while performing operations, you need to create relations. After the creation of the tables, you may want to edit the attributes you have provided. One of the examples is to modify the column name of the table. The column name can be changed in PostgreSQL by using the “ALTER” command on the table.

First, we provide the table name and then the column name after the rename clause. Thirdly, the name that is to be placed is written.

Create a table named “dishes” having all attributes written in the command with the data types.

>> CREATE TABLE dishes (id INTEGER PRIMARY KEY, name CHAR (10), origin CHAR(10), TYPE CHAR(10));

To see the content of this data, use a select statement in the psql shell.

We need two tables here, so create a new table “desi_dishes” and form a “join” with the previously created table. Because in the coming examples we will need them as a joint.

>> CREATE desi_dishes(IN inger, name CHAR(10), taste CHAR(10), dishes_id INT, FORIGN KEY(dishes_id) REFERENCES dishes(id));

>> SELECT * FROM desi_dishes;

After creating both the tables, we will now create a view of both the tables for the user.

>> CREATE VIEW ALL_dishes AS d.id, d.name, dishes FROM desi_dishes INNER JOIN Desi_dishes d ON d.id = d.dishes_id;

The view of both the tables is created by using an inner join. This join is applied on the id of the dishes table and on the “dishes_id” which is the foreign key of the same table.

If we want to change anything in the table, we use an alter command, for example, to update or delete a specific row or column of the table. Similarly, to change the table’s column name, we need to use the “ALTER” command.

>> ALTER TABLE dishes RENAME COLUMN name TO d_name;

From the resultant table, it is visible that the name of the table dishes is updated as “d_name”. You can check it by using a select statement. Similarly, we have also changed the name of the column in the second table. The taste is changed to “dd_taste”.

>> ALTER TABLE Desi_dishes RENAME COLUMN taste TO dd_taste;

To know about the table’s columns and description, we use a command to describe the relation’s command.

You can see from the resultant table that the table’s columns are displayed with the column type and the storage type. All these columns are only those we have selected for the creation of view by combining both tables. Some other features like Collation, default are also mentioned but don’t have any value.

In the storage column, for integer, it is “plain” which means it is limitless. We can use any number we want. But in the case of name and dishes, it is a character that is “extended”, because we have applied the limit for the number of characters. The definition of the view explains the command used for the creation of the view.

Now, if we want to see the information regarding “desi_dishes” to see the change in the column, we will apply the command on this table. You can also check the column names by applying the select command to fetch all the records. But this command shows the additional features regarding column names.

The view creation displays that either we have changed the column of a single table, but as we have formed the join, the change is clearly shown in the view.

All the column names are enlisted. The taste column is changed to the “dd_taste” name, as we have renamed the column name before by using the query. Just like the previous example, the storage column shows the plain or extended forms of the columns.

There are some more examples regarding the change in column attributes. Let’s have a look at them. Consider a table named “technology”. You can see its values from the select command

>> SELECT * FROM technology;

As there are 4 columns. For example, some have “char”, or some have an “integer”. For the column “models” we have used “varchar”. We want to change the datatype from varchar to numeric or integers only.

For this instance, we will use an alter command applied to the table “technology”.

>> ALTER TABLE technology ALTER COLUMN models TYPE NUMERIC;

This command will change the column data type. You can ensure the change by going to the pgAdmin pannel and expanding the database and tables to the “technology” table, and selecting the column you want to check, that is, “models”. Then right-clicking will lead you to the dialogue box that will show the details of data types and the column names.

You can see that the data type is converted into a numeric form.

Similar to renaming the column name, we can also change the table name by using the “Alter” command. Suppose we have a table named “sample1”

>> ALTER TABLE sample1 RENAME TO samples;

The message “ALTER TABLE” shows that the table’s name has changed.

Consider another example of that table whose name was changed to “samples”. Now, if we want to change the table in such a way that the column of the table is to be removed. Then we use the syntax for the “drop” statement.

>> ALTER TABLE(TABLE name) DROP COLUMN (COLUMN name);

Similar to removing the columns, we can also add the columns by using ADD “column names”.

>> ALTER TABLE samples ADD version INTEGER, ADD release_date DATE;

You can see the whole table through the select statement to see the newly added columns. You can see in the image that two blank columns are added to the table, whereas the column “age” is removed.

Via pgAdmin

Start the pgAdmin by providing the password to create a connection with the server. Now expand the database and go to the table’s portion so that you can edit the column. Here we have selected the table “example1”. Upon right-clicking, a drop-down is opened. Select the first option, “create” then select the option “column”.

From the image, you can understand the above discussion and let the new table be created. Furthermore, if you want to rename any column or change the column’s data type, you can navigate towards the specific column name on which you want to apply operations. Then expand the table “example1” and further expand the columns.

You will then select a specific column to be modified. For example, select the column price. Select the properties option. Then a dialogue box will appear. From this, you can rename the column or apply different constraints to it.

Conclusion

“Change column name PostgreSQL” allows the user to modify the existing name of the column in the table. This article shows the modification by using JOINS in tables, creating a view, and using an ALTER command. All the methods used are briefly explained to clarify their existence and usage.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.

PostgreSQL

Show Comments

Change column name Postgresql

Via pgAdmin

Conclusion

About the author

Aqsa Yasin

Other stories

How to backup AWS EC2 instance

Best Router for Xfinity Today

Press ESC to close

Via pgAdmin

Conclusion

About the author

Aqsa Yasin

Share Article:

You might also like

How To Install PostgreSQL on MacOS

PostgreSQL Built-in Range Data Types

PostgreSQL DateTime Data Types

Other stories

How to backup AWS EC2 instance

Best Router for Xfinity Today