![]() ![]() 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. :: represents CAST operator in PostgreSQL.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. It is asking us to cast the roll_no values into integer type explicitly. PostgreSQL is even providing us with a hint. Specifically, the error will read − ERROR: column "roll_no" cannot be cast automatically to type integer HINT: You might need to specify "USING roll_no::integer". This is because the conversion of text to integer is not that straightforward for PostgreSQL. Now, let us try to convert the roll_no column back to an integer ALTER TABLE marks It simply replaces the digit with the character representing that digit. ![]() Because integer to text conversion does not confuse PostgreSQL. Now, for understanding the cases where we need to use an expression, let us first convert the roll_no column to VARCHAR type. We need not use any expression for converting the column type. Now, suppose we want to convert the serial_no column from type INTEGER to BIGINTEGER. Now, let us populate it INSERT INTO marks(serial_no,name, roll_no, marks_obtained, Let us create a new table marks − CREATE TABLE marks( Let us understand both the cases using an example. However, if you convert a TEXT or a VARCHAR entry to INTEGER, you will not be able to do that by default, unless you provide an expression for converting the existing TEXT/VARCHAR values to an integer. For instance, if you convert a column of type INTEGER to type BIGINTEGER, you need not use any expression for the conversion of existing entries to the new types. It is used when you need to use an expression for converting the existing entries in that column from the current type to the new type. The USING part of the syntax is optional. Please note that altering the type of a column is not recommended generally, especially if your table has a lot of entries already. In order to change the type of a column, the syntax is ALTER TABLE table_nameĪLTER COLUMN column_name TYPE new_data_type USING expression ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |