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 kommentar(er)
