2

Trying to update a column with varchar datatype e.g. '1950-08-14' to a date datatype using

UPDATE tablename SET columnname = to_date(columnname, 'YYYY-MM-DD');

or

ALTER TABLE tablename ALTER COLUMN columnname TYPE DATE USING to_date(columnname, 'YYYY-MM-DD');

but both return the error message

ERROR:  invalid value "columnname" for "YYYY"
DETAIL:  Value must be an integer.

Referencing http://www.postgresql.org/docs/9.4/static/functions-formatting.html

acmyers
  • 91
  • 2
  • 11
  • You sure you're not quoting the column name? Could you show the actual query you're running? – Schwern Sep 25 '15 at 23:26
  • for the first instance of 'columnname' I've tried using the same column i.e. the one being changed to_date() and also a new empty columnname of type 'date' – acmyers Sep 25 '15 at 23:50

1 Answers1

5

The query:

ALTER TABLE tablename 
    ALTER COLUMN columnname TYPE DATE USING to_date(columnname, 'YYYY-MM-DD');

is correct.

The error message means that you have invalid value in column columnname (actualy the invalid value is 'columnname'). All the values in the column must have the format 'YYYY-MM-DD', e.g. '2015-01-01' (or be null).

Solution with a new column.

Create a function to convert varchar to date which returns null for invalid values:

create or replace function varchar_to_date_or_null(str varchar)
returns date language plpgsql as $$
begin
    return to_date(str, 'YYYY-MM-DD');
exception
    when invalid_datetime_format then return null;
end $$;

Add a new column and update it using the function:

alter table tablename add new_column date;

update tablename
set new_column = varchar_to_date_or_null(columnname);
klin
  • 112,967
  • 15
  • 204
  • 232
  • any idea how I can bypass this? I have a table of 45,000 rows with a column of dates in text data type. The one's I've looked at all seem to follow the format 'YYYY-MM-DD'... perhaps some are missing for incorrectly input? should I remove strings that don't fit that format first? – acmyers Sep 26 '15 at 00:29
  • Nice, thanks! Conveniently I've been trying to get better at working with 'create or replace function' as well. – acmyers Sep 26 '15 at 01:03