postgresql 迄今为止的强制转换 varchar 类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13287618/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cast varchar type to date
提问by Lo Bizzarri
I'd like to change a specific column in my PostgreSQL database from character_varyingtype to type date. Date is in the format yyyy:mm:dd
我想将 PostgreSQL 数据库中的特定列从character_varyingtype 更改为 type date。日期是格式yyyy:mm:dd
I tried to do:
我试图做：
alter table table_name
alter column date_time type date using (date_time::text::date);
But I received an error message:
但我收到一条错误消息：
date/time field value out of range: "2011:06:15"
日期/时间字段值超出范围：“2011:06:15”
回答by Erwin Brandstetter
When you cast textor varcharto date, the default date format of your installation is expected - depending on the datestylesetting in your postgresql.conf.
当你投射text或varchar到时date，你安装的默认日期格式是预期的——取决于datestyle你的postgresql.conf.
Generally, colon (:) is a timeseparator, In a simple cast, PostgreSQL will probably try to interpret '2011:06:15' as time - and fail.
通常，冒号 ( :) 是时间分隔符，在简单的转换中，PostgreSQL 可能会尝试将 '2011:06:15' 解释为时间 - 并且失败。
To remove ambiguity use to_date()with a matching pattern for your dates:
要消除歧义，请使用to_date()日期的匹配模式：
ALTER TABLE table_name
ALTER COLUMN date_time type date
USING to_date(date_time, 'YYYY:MM:DD'); -- pattern for your example
