PostgreSQL - ERROR: column "date" cannot be cast to type date

Question

I want to cast a specific column in my PostgreSQL database from character_varying type to type date. phpPgAdmin gives me the following error:

ERROR: column "date" cannot be cast to type date

In Statement: ALTER TABLE "public"."tablename" ALTER COLUMN "date" TYPE date

What should I do ? Thanks

Denis de Bernardy · Accepted Answer · 2011-05-11 15:52:56Z

41

You might need to convert it to text first:

alter table "foo" alter column "date" type date using ("date"::text::date);

answered May 11, 2011 at 15:52

Denis de Bernardy

79.2k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Athanasios Emmanouilidis Over a year ago

Hi and thanks for the answer. I have another problem. The date column is used in one of my views so i get the following error: ERROR: cannot alter type of a column used by a view or rule How can i overcome this ?

Denis de Bernardy Over a year ago

In that case you cannot: you need to drop the view and recreate it.

Scott Marlowe Over a year ago

If you want to save or see the definition of the view, it's stored in pg_views btw.

acrogenesis Over a year ago

How can I achieve this if my column has dates saved as dd/mm/yyyy. I get this error message with your solution. PG::DatetimeFieldOverflow: ERROR: date/time field value out of range: "27/11/1992" HINT: Perhaps you need a different "datestyle" setting.

Denis de Bernardy Over a year ago

@acrogenesis: look into the to_date() function

|

Collectives™ on Stack Overflow

PostgreSQL - ERROR: column "date" cannot be cast to type date

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related