![]() In the above syntax, the alter table statement is defined as add the new column using the alter table statement. Syntax: Alter table name_of_tableADD COLUMN column_name (Name of column which we have adding) data_type Below is the syntax of the alter table to add a new column are as follows. ![]() In the example below, we added a new column to the table using the alter table statement. In the above example, we have to change the table column name from stud_name to stud_name_new. Rename column is defined as rename an existing column with a new name.Ĭode: ALTER TABLE student RENAME COLUMN stud_name TO stud_name_new In the above syntax, the alter table statement is defined as changing the column’s name using the alter table statement. Syntax Alter table existing_table_name RENAME COLUMN old_column_name TO new_column_name Below is the syntax of the alter table to change the name of the column. In the below example, we have used alter table command to change an existing column’s name. Below is the table description of the student and stud2 table.īelow is the method of the same: 1. When you can afford some downtime the “using” syntax is what you want to do and it is working quite fast.Using the stud2 and student table to describe the example of altering a column in PostgreSQL is as follows. ![]() 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 |