Varchar to timestamp PostgreSQL

Question

What i want to do, is to change my date column type from varchar to timestamp w/o timezone, and migrate all my data. This is what im doing:

ALTER TABLE mytable ALTER COLUMN "datecol"
    TYPE timestamp without time zone USING(to_timestamp("datecol", 'YYYY-MM-DD')::timestamp without time zone);

This works fine if data in datecol is in date format. But if i have some not valid data, like random strings (e.g "abc") how can i validate and check if date format is good? I want to set default value for those invalid fields.

EDIT: Thanks Ludvig, i solved my problem:

create or replace function is_date(s varchar) returns boolean as $$
begin
perform s::date;
return true;
exception when others then
return false;
end;
$$ language plpgsql;

ALTER TABLE mytable ALTER COLUMN "datecol"
TYPE timestamp without time zone USING(
CASE WHEN is_date("datecol") = true
THEN to_timestamp("datecol", 'YYYY-MM-DD')::timestamp without time zone
ELSE '1970-01-01 00:00:00.000'
END
);

date format is good Which date formats are good? only like this: 'YYYY-MM-DD'? — Oto Shavadze
– Oto Shavadze, Commented May 3, 2017 at 9:50
No, to_timestamp identifies formats like: YYYY, YYYY-MM, YYYY-MM-DD, YYYY/MM/DD, YYYY.MM.DD etc. For those formats it works fine — Mindro
– Mindro, Commented May 3, 2017 at 9:56

Vao Tsun · Accepted Answer · 2017-05-03 13:17:04Z

0

You can't alter type for column with try/catch logic, so you either have to regexp all possible formats or you can:

add column "ts" of timestamp data type
add column "exc" boolean

do $$ 
  declare _r record; 
begin 
  for _r in (select * from mytable) loop 
    update mytable set "ts" = to_timestamp("datecol", 'YYYY-MM-DD') 
    where ...PK..=_r.PK; 
  when others 
    then update mytable set "exc" = true; 
 end loop; end; $$;

drop datecol, rename "ts" to "datecol"
deal with values where "exc"

The big minus would be changed order of columns in mytable. So poor apology that you can after create table tt as select NEEDED order from mytable; drop table mytable;alter table tt rename to mytable, but then you will have to rebuild all references and dependants as well of course. Or even more exotic way - you can start adding column in needed order, dropping renaming until you get the old set...

edited May 3, 2017 at 13:17

user330315

answered May 3, 2017 at 10:09

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

1 Comment

Mindro Over a year ago

Thanks, but changed order is not really what i need :D

score 0 · Accepted Answer · 2017-05-03 13:30:47Z

Instead of testing for a valid date I would create a function that tries casting using different formats and returns the default date in case none of them work:

create or replace function convert_date(s varchar) 
   returns timestamp 
as 
$$
begin
  -- test standard ISO format
  begin
    return to_timestamp(s, 'yyyy-mm-dd');
  exception when others then
      -- ignore
  end;

  begin
    return to_timestamp(s, 'yyyy.mm.dd');
  exception when others then
    -- ignore
  end;

  begin
    return to_timestamp(s, 'yyyy/mm/dd');
  exception when others then
    -- ignore
  end;

  begin
    return to_timestamp(s, 'yyyy-mm');
  exception when others then
    -- ignore
  end;


  begin
    return to_timestamp(s, 'yyyy');
  exception when others then
    -- ignore
  end;

  return timestamp '1970-01-01 00:00:00';
end
$$ language plpgsql;

Then use:

ALTER TABLE mytable 
    ALTER COLUMN "datecol" TYPE timestamp without time zone 
    USING (convert_date(datecol));

This won't be very efficient, but for a one-time job it should work

Collectives™ on Stack Overflow

Varchar to timestamp PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related