In this short article, you discovered how you can rename a column in PostgreSQL using the ALTER TABLE and RENAME TABLE statements.In my previous article, I explained, Query to Generate Parent-Child Relation Rows By Splitting String in SQL Server and PostgreSQL that you might like to read. The statement above should rename the and the references to the column. The command statement is as provided below: ALTER TABLE department RENAME COLUMN department_name TO dpt_name We can verify this by renaming the column department_name in the department's table to dpt_name. PostgreSQL Rename Column with DependenciesĪs mentioned, renaming a column with dependencies such as foreign keys will force PostgreSQL to update the references. We can see the new column name is set to current_location. The statement above should return the columns as shown: We can verify by running the command: SELECT * FROM employees The command above should rename the country column to current_location. An example code is as shown below: ALTER TABLE employees RENAME COLUMN country TO current_location We can use the ALTER TABLE clause to rename the country column to current_location. Notice the employees table has a reference the id column in the deparment table. One holding the department information and the other holding employee information. In the example code above, we have two tables. Let's start by adding a table as shown: CREATE TABLE department (įOREIGN KEY (department_id) REFERENCES department (id) This should connect to the new database and allow us to create new schemas. We can then swtich to the database by running: \c sample_database Feel free to rename the database to any name you wish. The query above should create a new database called sample_database. Let us start by creating a sample database as shown: CREATE DATABASE "sample_database" The best way to illustrate how to rename a column in PostgreSQL is using an example. For example, if the renamed column is referenced by other constraints such as foreign keys, PostgreSQL will point the child columns to the renamed column. PostgreSQL will also rename all other references that refer to the renamed column. In PostgreSQL, you do not have the IF EXISTS clause. Attempting to rename a column that does not exist will raise an error. Keep in mind that the specified column should exist on the target table. This allows us to specify the new name for the column. Similarly, this clauses allows us to specify the target column. The second part is the RENAME COLUMN clause.This allows us to specify in which table the column we wish to rename is located. The first step is the ALTER TABLE statement.Let us break down the command syntax above. The syntax is as shown below: Advertisements ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name In PostgreSQL, we can rename a column using the ALTER TABLE statement. This will allow you to avoid conflicting names when migrating database or adding new columns to the table. In this short tutorial, we will discuss how you can rename an existing table column.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |