我想做的是将日期列类型从varchar更改为不带时区的时间戳,并迁移所有数据.这是我在做什么:
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);如果datecol中的数据为日期格式,则此方法很好用.但是,如果我有一些无效数据,例如随机字符串(例如"abc"),如何验证并检查日期格式是否合适?我想为那些无效的字段设置默认值.
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.
感谢Ludvig,我解决了我的问题:
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 ); 推荐答案我将创建一个函数,尝试使用不同的格式进行强制转换,并在没有一个有效日期的情况下返回默认日期,而不是测试有效日期:
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;然后使用:
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
更多推荐
将Varchar时间戳记为PostgreSQL
发布评论