In this syntax, you add a comma ( ,) after each ALTER COLUMN clause. Code language: SQL (Structured Query Language) ( sql ) 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 ) When you can afford some downtime the “using” syntax is what you want to do and it is working quite fast.Summary: this tutorial shows you step by step how to change the data type of a column by using the ALTER TABLE statement. HINT: Perhaps you need a different "datestyle" setting.Ĭonclusion: Converting a column from one data type to another is nothing you want to do when you system is live. In session 1 you’ll notice that the statement is blocked and finally you get this: Time: 0.453 msĮRROR: date/time field value out of range: "01012017" In session 2 I am doing the conversion: Time: 0.453 > alter table t1 alter column a type date using to_date(a,'DDMMYYYY') Then, in session 1 I am doing this: Time: 0.453 > select count(*) from t1 where a = '01012017' and so on and so on > insert into t1 select * from t1 Insert into t1 (a) values > insert into t1 select * from t1 The remaining question is: Does this operation block others from reading the table while it is being executed? Lets generate some more date to make the select operation a bit longer and then “watch” the statement in a separate session while we execute the conversion: Time: 0.453 ms Time: 20.972 > explain analyze select a from t1 where a = to_date('01012017','dd.mm.yyyy') Time: 3.344 > insert into t1 select * from t1 Time: 1.432 > insert into t1 select * from t1 Time: 1.455 > insert into t1 select * from t1 Time: 1.072 > insert into t1 select * from t1 Time: 1.110 > insert into t1 select * from t1 Time: 39.653 > insert into t1 select * from t1 Time: 2.373 > insert into t1 select * from t1 Looks fine as well, lets do a quick test if the index is really usable: Time: 0.453 > insert into t1 select * from t1 "i1" btree > select indisvalid,indisready,indislive from pg_index where indexrelid = 'i1'::regclass "i1" btree > alter table t1 alter column a type date using to_date(a,'DDMMYYYY') When there is an index on the column, what happens to the index? > d t1 That’s really cool: You can pass a function (this mean a self written function as well) to the alter command to specify on how you want to do the conversion. What will really help here is the “using” keyword because you can do things like this: > alter table t1 alter column a type date using to_date(a,'DDMMYYYY') When you look at the “alter table” command in PostgtreSQL you’ll notice something like this: where action is one of:ĪDD column_name data_type ]ĭROP column_name ĪLTER column_name TYPE data_type We now have a varchar column holding the date values as a string. Insert into t1 (a) values > select * from t1 To start with lets generate some test data: drop table if exists t1 This for sure will work but it is not the most convenient way: What happens if the application is somehow dependent on the order of the columns? Shouldn’t be the case if the application is written well, but you never know. How can we do that in PostgreSQL? The first answer that came to me mind is: Add a new column (of data type date) and populate it with the converted string from the source column, drop the source column and rename the new column to the name of the dropped column. When we want to convert this column to be a real date data type in Oracle we can use dbms_redefinition. Last week at a customer the following question came up: We have a varchar2 column in Oracle that contains a date but actually is stored as a string.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |