4

I have a PostgreSQL table that contains a column of data type 'text', that column contains only dates.

I'm trying to convert it to date type, But it doesn't work.

publication_date
-----------
"9/16/2006"
"9/1/2004"
"11/1/2003"
"5/1/2004"
"9/13/2004"
"4/26/2005"
"9/12/2005"
"11/1/2005"
"4/30/2002"
"8/3/2004"

ALTER TABLE books 
ALTER COLUMN publication_date SET DATA TYPE date;

outputs: 

ERROR:  column "publication_date" cannot be cast automatically to type date
HINT:  You might need to specify "USING publication_date::date".
SQL state: 42804

1 Answers1

7

The error message tells you what to do:

ALTER TABLE books 
ALTER COLUMN publication_date SET DATA TYPE date
      USING to_date(publication_date, 'mm/dd/yyyy');
  • This raises another error: ERROR: date/time field value out of range: "11/31/2000" SQL state: 22008 –  Mar 24 '20 at 12:26
  • @yovelcohen: then obviously you have invalid date values in your column. See here for a solution: https://stackoverflow.com/questions/32791975 –  Mar 24 '20 at 12:32