How to convert column type from varchar to date in PostgreSQL?

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;