postgresql to_timestamp 通过设计接受无效日期

Question

我正在尝试将字符串验证为来自多个 CSV 的时间戳，并且简单地将它们转换为 timestamptz 将失败，因为无法强制使用唯一的日期时间格式:

select '10/31/2010'::timestamp --fail due to "different datestyle" 
select '31/10/2010'::timestamp --works

我认为 to_timestamp() 可以解决问题，但像这样:

select to_timestamp('31/02/2014 14:30', 'DD/MM/YYYY HH24:MI'); 

将返回"2014-03-03 14:30:00-05"而不是抛出异常

所以我想到了用这个approach ，使用 to_char 将输出恢复为文本并将其与原始输入进行比较，但是像 'DD/MM/YYYY HH24:MI' 这样的掩码将转换“06/03/2014 0:06"到 "06/03/2014 00:06"，字符串不一样!

CREATE OR REPLACE FUNCTION to_timestamp_safe(IN p_date text, IN p_format text, OUT r_date timestamp without time zone)
  RETURNS timestamp without time zone AS
$BODY$

    BEGIN
      r_date = TO_TIMESTAMP(p_date, p_format);
       IF TO_CHAR(r_date, p_format) != p_date THEN
        RAISE EXCEPTION 'Input date % does not match output date %', p_date, r_date;
      END IF;
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

下面的例子在它应该工作的地方失败了:

select to_timestamp_safe ('06/03/2014 0:06', 'DD/MM/YYYY HH24:MI');

输出:

ERROR: Input date 06/03/2014 0:06 does not match output date 2014-03-06 00:06:00
SQL state: P0001

是否有一种聪明的方法可以安全地将字符串验证为 timestamptz 而没有上述陷阱？

Answer 1

FM modifier做到了这一点(感谢@ErwinBrandstetter)，这确实是一种通用解决方案，因为我们想出了为每种类型的 csv 创建配置文件的想法，并且可以为日期/时间列存储日期格式掩码，无论如何，大部分列都会转到 hstore 列，我们需要将它们的类型存储在某个地方。所以我能够创建这样的东西，其中 _colmask 是可能有也可能没有 FM 修饰符的日期时间格式掩码。然后我只需对我的登台表执行此功能(对每一列进行循环)

CREATE OR REPLACE FUNCTION validate_column_datatype(_colvalue text, _colname text, _type text,  _colmask text)
  RETURNS void AS
$BODY$
BEGIN

declare
    -- error stack
    _returned_sqlstate text := null;
    _column_name text := null;
    _constraint_name text := null;
    _pg_datatype_name text := null; 
    _message_text text := null;
    _table_name text := null;
    _schema_name text := null;
    _pg_exception_detail text := null;
    _pg_exception_hint text := null;
    _pg_exception_context text := null;

    BEGIN


    IF _type = 'timestamptz'  then    
          IF TO_CHAR(TO_TIMESTAMP(_colvalue, _colmask), _colmask) != _colvalue THEN
        RAISE EXCEPTION 'Input date % does not match output date', _colvalue;
          END IF;

    ELSEIF _type = 'timestamp'  then
          IF TO_CHAR(TO_TIMESTAMP(_colvalue, _colmask), _colmask) != _colvalue THEN
        RAISE EXCEPTION 'Input date % does not match output date', _colvalue;
          END IF;

    ELSEIF _type = 'numeric'  then
        perform _colvalue::numeric;   

    ELSEIF _type = 'integer'  then
        perform _colvalue::integer;    

   -- other types

        END IF;   

-- exception occurs      
    EXCEPTION WHEN OTHERS THEN
         get stacked diagnostics 
        _returned_sqlstate      = RETURNED_SQLSTATE,
        _column_name        = COLUMN_NAME,
        _constraint_name    = CONSTRAINT_NAME, 
        _pg_datatype_name   = PG_DATATYPE_NAME, 
        _message_text       = MESSAGE_TEXT,
        _table_name         = TABLE_NAME,
        _schema_name        = SCHEMA_NAME,
        _pg_exception_detail    = PG_EXCEPTION_DETAIL,
        _pg_exception_hint      = PG_EXCEPTION_HINT,
        _pg_exception_context   = PG_EXCEPTION_CONTEXT;

        _message_text :=  -- write something meaningful
        _pg_exception_detail = -- write something meaningful
        _pg_exception_hint := -- write something meaningful

        -- log to something

    END;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;