sql - Postgres 数据类型转换

Question

我有一个 SQL 格式的数据集。但是 DATE 类型需要转换成不同的格式，因为我得到以下错误

CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR:  date/time field value out of range: "28-10-96"
LINE 58: ...040','2','10','','P13-00206','','','','','1-3-95','28-10-96'...
                                                          ^
HINT:  Perhaps you need a different "datestyle" setting.

我肯定已经阅读了有关日期格式的文档 http://www.postgresql.org/docs/current/static/datatype-datetime.html

但我的问题是如何将所有日期转换为正确的格式，而无需遍历所有 500 行左右的数据行并确保在插入数据库之前每个数据行都是正确的。后端由 rails 处理，但我认为最好在这里通过 SQL 进行清理。

我在该数据集上方有一个CREATE TABLE 语句，请注意，该数据集是通过 DBF 转换器/外部源提供的

这是我的数据集的一部分

INSERT INTO winery_attributes
  (ID,NAME,STATUS,BLDSZ_ORIG,BLDSZ_CURR,HAS_CAVE,CAVESIZE,PROD_ORIG,PROD_CURR,TOUR_TASTG,VISIT_DAY,VISIT_WEEK,VISIT_YR,VISIT_MKTG,VISIT_NMEV,VISIT_ALL,EMPLYEENUM,PARKINGNUM,WDO,LAST_UP,IN_CITYBDY,IN_AIASP,NOTES,SMLWNRYEXM,APPRV_DATE,ESTAB_DATE,TOTAL_SIZE,SUBJ_TO_75,GPY_AT_75,AVA,SUP_DIST)
VALUES
  (1,'ACACIA WINERY','PROD','8000','34436','','0','50000','250000','APPT','75','525','27375','3612','63','30987','22','97','x','001_02169-MOD_AcaciaWinery','','','','','1-11-79','1-9-82','34436','x','125000','Los Carneros','1');

INSERT INTO winery_attributes
  (ID,NAME,STATUS,BLDSZ_ORIG,BLDSZ_CURR,HAS_CAVE,CAVESIZE,PROD_ORIG,PROD_CURR,TOUR_TASTG,VISIT_DAY,VISIT_WEEK,VISIT_YR,VISIT_MKTG,VISIT_NMEV,VISIT_ALL,EMPLYEENUM,PARKINGNUM,WDO,LAST_UP,IN_CITYBDY,IN_AIASP,NOTES,SMLWNRYEXM,APPRV_DATE,ESTAB_DATE,TOTAL_SIZE,SUBJ_TO_75,GPY_AT_75,AVA,SUP_DIST)
VALUES
  ('2','AETNA SPRING CELLARS','PROD','2500','2500','','0','2000','20000','TST APPT','0','3','156','0','0','156','1','10','x','','','','','x','1-4-86','1-6-86','2500','','0','Napa Valley','3');

INSERT INTO winery_attributes
  (ID,NAME,STATUS,BLDSZ_ORIG,BLDSZ_CURR,HAS_CAVE,CAVESIZE,PROD_ORIG,PROD_CURR,TOUR_TASTG,VISIT_DAY,VISIT_WEEK,VISIT_YR,VISIT_MKTG,VISIT_NMEV,VISIT_ALL,EMPLYEENUM,PARKINGNUM,WDO,LAST_UP,IN_CITYBDY,IN_AIASP,NOTES,SMLWNRYEXM,APPRV_DATE,ESTAB_DATE,TOTAL_SIZE,SUBJ_TO_75,GPY_AT_75,AVA,SUP_DIST)
VALUES
  ('3','ALTA VINEYARD CELLAR','PROD','480','480','','0','5000','5000','NO','0','4','208','0','0','208','4','6','x','003_U-387879','','','','','2-5-79','1-9-80','480','','0','Diamond Mountain District','3');

INSERT INTO winery_attributes
  (ID,NAME,STATUS,BLDSZ_ORIG,BLDSZ_CURR,HAS_CAVE,CAVESIZE,PROD_ORIG,PROD_CURR,TOUR_TASTG,VISIT_DAY,VISIT_WEEK,VISIT_YR,VISIT_MKTG,VISIT_NMEV,VISIT_ALL,EMPLYEENUM,PARKINGNUM,WDO,LAST_UP,IN_CITYBDY,IN_AIASP,NOTES,SMLWNRYEXM,APPRV_DATE,ESTAB_DATE,TOTAL_SIZE,SUBJ_TO_75,GPY_AT_75,AVA,SUP_DIST)
VALUES
  ('4','BLACK STALLION','PROD','43600','43600','','0','100000','100000','PUB','50','350','18200','0','0','18200','2','45','x','P13-00391','','','','','1-5-80','1-9-85','43600','','0','Oak Knoll District of Napa Valley','3');

INSERT INTO winery_attributes
  (ID,NAME,STATUS,BLDSZ_ORIG,BLDSZ_CURR,HAS_CAVE,CAVESIZE,PROD_ORIG,PROD_CURR,TOUR_TASTG,VISIT_DAY,VISIT_WEEK,VISIT_YR,VISIT_MKTG,VISIT_NMEV,VISIT_ALL,EMPLYEENUM,PARKINGNUM,WDO,LAST_UP,IN_CITYBDY,IN_AIASP,NOTES,SMLWNRYEXM,APPRV_DATE,ESTAB_DATE,TOTAL_SIZE,SUBJ_TO_75,GPY_AT_75,AVA,SUP_DIST)
VALUES
  ('5','ALTAMURA WINERY','PROD','11800','11800','x','3115','50000','50000','APPT','0','20','1040','0','0','1040','2','10','','P13-00206','','','','','1-3-95','28-10-96','14915','x','50000','Napa Valley','4');

Answer 1

数据集中的日期采用字符串形式。由于它们不在默认的 datestyle(即 YYYY-MM-DD)中，您应该将它们显式转换为 date，如下所示:

to_date('1-5-80', 'DD-MM-YY')

如果您将数据存储在时间戳中，请使用

to_timestamp('1-5-80', 'DD-MM-YY')

如果您以显示的 INSERT 语句的形式获得数据集，则首先将所有数据作为简单字符串加载到 varchar 列中，然后添加date 列并执行 UPDATE(对于 integer 和 boolean 列也是如此):

UPDATE my_table
SET estab = to_date(ESTAB_DATE, 'DD-MM-YY'), -- column estab of type date
    apprv = to_date(APPRV_DATE, 'DD-MM-YY'), -- etc
    ...

更新完成后，您可以ALTER TABLE 删除带有日期(整数、 bool 值)的文本列。