Postgresql alter table used by view7/23/2023 ![]() In this tutorial, you have learned how to use the ALTER TABLE ALTER COLUMN statement to change the type of a column. 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, An ACCESS EXCLUSIVE lock is acquired unless explicitly noted.Note that the lock level required may differ for each subform. There are several subforms described below. The SET DATA TYPE and TYPE are equivalent.
0 Comments
Leave a Reply. |