RENAME TO immediately makes the table available under its new name of sprocket. Here’s some copy/pastable migration code for renaming a table (in this example, chainwheel -> sprocket): BEGIN Īnd with a post-deploy (after all clients are rotated) follow up of: DROP VIEW chainwheel Luckily, Postgres makes this possible relatively easily. We’d like to rename things, but to also do so with zero downtime and zero user impact. Copy this codeīut to schema hygiene fanatics out there (like myself), that’s not a satisfactory answer. I’d hazard to guess that this is how most shops run – people would generally prefer to rename as appropriate, but in practice it’s more time, risk, and effort than it’s worth. Practically speaking, the easiest way to administer a production database is to never rename anything, and live with the fact that some names are less-than-optimal. It’s annoying for users, and painful if a service is doing some business critical. Anything that was still running against the old name when a rename takes place will immediately break, causing downtime and major user impact.Īn alternative would be to disable all clients temporarily and then do the rename, and indeed a “we’re down for maintenance” screen was a pretty common sight in the 2000s, but serious services in the 2020s aim to never have downtime at all. The problem isn’t in the database itself, but in database clients. Anyone who’s run a production database before will recognize that outside of an academic context, it’s actually kind of hard. The SQL is a dead simple one-liner of ALTER. In this tutorial, you have learned how to use the ALTER TABLE ALTER COLUMN statement to change the type of a column.At first glance, renaming entities in a database seems like it should be easy. The following statement adds the USING clause to the above statement: ALTER TABLE assetsĪLTER COLUMN asset_no TYPE INT USING asset_no:: integer Code language: SQL (Structured Query Language) ( sql ) HINT: You might need to specify "USING asset_no::integer". PostgreSQL issued an error and a very helpful hint: ERROR: column "asset_no" cannot be cast automatically to type integer To change the data type of the asset_no column to integer, you use the following statement: ALTER TABLE assetsĪLTER COLUMN asset_no TYPE INT Code language: SQL (Structured Query Language) ( sql ) The following statement changes the data types of description and location columns from TEXT to VARCHAR: ALTER TABLE assetsĪLTER COLUMN description TYPE VARCHAR Code language: SQL (Structured Query Language) ( sql ) To change the data type of the name column to VARCHAR, you use the following statement: ALTER TABLE assetsĪLTER COLUMN name TYPE VARCHAR Code language: SQL (Structured Query Language) ( sql ) ( 'UPS', '10002', 'Server room', '') Code language: SQL (Structured Query Language) ( sql ) INSERT INTO assets( name,asset_no,location,acquired_date) Let’s create a new table named assets and insert some rows into the table for the demonstration. The expression after the USING keyword can be as simple as column_name::new_data_type such as price::numeric or as complex as a custom function. In case the cast fails, PostgreSQL will issue an error and recommends you provide the USING clause with an expression for the data conversion. If you omit the USING clause, PostgreSQL will cast the values to the new ones implicitly. The USING clause specifies an expression that allows you to convert the old values to the new ones. PostgreSQL allows you to convert the values of a column to the new ones while changing its data type by adding a USING clause as follows: ALTER TABLE table_nameĪLTER COLUMN column_name TYPE new_data_type USING expression Ĭode language: SQL (Structured Query Language) ( sql ) In this syntax, you add a comma ( ,) after each ALTER COLUMN clause. To change the data types of multiple columns in a single statement, you use multiple ALTER COLUMN clauses like this: ALTER TABLE table_nameĪLTER COLUMN column_name1 TYPE new_data_type,ĪLTER COLUMN column_name2 TYPE new_data_type, The SET DATA TYPE and TYPE are equivalent. Third, supply the new data type for the column after the TYPE keyword.Second, specify the name of the column that you want to change the data type after the ALTER COLUMN clause.First, specify the name of the table to which the column you want to change after the ALTER TABLE keywords. Let’s examine the statement in a greater detail: To change the data type of a column, you use the ALTER TABLE statement as follows: ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type Code language: CSS ( css ) Summary: this tutorial shows you step by step how to change the data type of a column by using the ALTER TABLE statement.
0 Comments
Leave a Reply. |