I have varchar
data type column and date
data type column.
I have to update varchar
column data into date
column in PostgreSQL.开发者_JAVA技巧
Is it possible?
Thanks.
ALTER TABLE <tablename> ALTER COLUMN <columnname> TYPE DATE
using to_date(<columnname>, 'YYYY-MM-DD');
UPDATE tableName SET dateColumn=to_date(varcharColumn, 'DD MM YYYY')
Assuming you are saving "07 04 2010"
You can find further examples and explanation in the documentation:
http://www.postgresql.org/docs/current/interactive/functions-formatting.html
to_date('05 Dec 2000', 'DD Mon YYYY')
syntax for typecasting:
alter table table_name alter column_name
type converting_data_type using(column_name::converting_data_type)
converting from varchar to date
alter table table_name
alter column_name type date using(column_name::date)
To convert column type from timestamp
to date
in postgresql with explicit typecast:
Explicit typecast allows our existing data to be converted to our new type when the column type is updated.
There is slight change in syntax for explicit typecast for keyword USING
Syntax:
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type USING expression;
eg. Suppose we have table user_data with a column date_of_birth which took timestamp
earlier, but now we want it to store only date.
Query:
ALTER TABLE user_data
ALTER COLUMN date_of_birth TYPE date
USING date_of_birth::date;
精彩评论