update vachar column to date in postgreSQL

Question

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

Answer 1

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);