![]() PostgreSQL Modify Column Type Examplesīefore proceeding, we will need a table having columns of a variety of data types. Let’s see some examples now so that you will understand its real-time usage. To modify the multiple columns’ data type, write multiple ALTER COLUMN statements separated by a comma. If the existing data type is not convertible to the new data type, you will get an error message. The ‘type’ keyword must be specified in order to set the new data type. SET DATA TYPE – Here, SET DATA is an optional keyword.columnName – The name of the column that you want to change the data type of.If not, it will not throw the error message. IF EXISTS – It checks if the table name that you mentioned exists.In this tutorial, you have learned how to use the PostgreSQL RENAME COLUMN clause in the ALTER TABLE statement to rename a column.ALTER COLUMN columnName type newDataType Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) These statements rename two columns name and phone of the customers table to customer_name and contact_phone respectively: ALTER TABLE customers 3) Using multiple RENAME COLUMN to rename multiple columns example JOIN customer_groups g ON g.id = c.group_id Code language: Shell Session ( shell )Īs you can see in the view definition, the name column has been changed to group_name. ![]() +-+-+-+-Ĭustomer_group | character varying | | extended | Now, you can check whether the change of the name column was cascaded to the customer_data view: test=# \d+ customer_data Ĭolumn | Type | Modifiers | Storage | Description The name column is used in the customer_data view. This example uses the ALTER TABLE RENAME COLUMN statement to rename the name column of the customer_groups table to group_name. The following statement uses the ALTER TABLE RENAME COLUMN statement to rename the email column of the customers table to contact_email: ALTER TABLE customersĬode language: SQL (Structured Query Language) ( sql ) 2) Using RENAME COLUMN to rename a column that has dependent objects example INNER JOIN customer_groups g ON g.id = c.group_id Ĭode language: SQL (Structured Query Language) ( sql ) 1) Using RENAME COLUMN to rename one column example Then, create a new view named customer_data based on the customers and customer_groups tables. CREATE TABLE customer_groups (įOREIGN KEY ( group_id) REFERENCES customer_groups ( id) Setting the sample tablesįirst, create two new tables customers and customer_groups. Let’s take some examples of using the ALTER TABLE RENAME COLUMN to get a better understanding. If you rename a column referenced by other database objects such as views, foreign key constraints, triggers, and stored procedures, PostgreSQL will automatically change the column name in the dependent objects. To rename multiple columns, you need to execute the ALTER TABLE RENAME COLUMN statement multiple times, one column at a time: ALTER TABLE table_nameĬode language: SQL (Structured Query Language) ( sql ) Unfortunately that PostgreSQL does not provide the IF EXISTS option for the RENAME clause. ![]() RENAME column_name TO new_column_name Code language: SQL (Structured Query Language) ( sql )įor some reason, if you try to rename a column that does not exist, PostgreSQL will issue an error. The COLUMN keyword in the statement is optional therefore you can omit it like this: ALTER TABLE table_name
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |