title: postgresql to_timestamp accepts invalid dates by design
body: <p>I'm trying to validate strings to timestamps from several CSVs and simply casting them to timestamptz will fail due to the impossibility of forcing an unique datetime format:</p>
  
  <pre><code>select '10/31/2010'::timestamp --fail due to "different datestyle" 
  select '31/10/2010'::timestamp --works
  </code></pre>
  
  <p>I thought <em>to_timestamp()</em> would do the trick, but something like:</p>
  
  <pre><code>select to_timestamp('31/02/2014 14:30', 'DD/MM/YYYY HH24:MI'); 
  </code></pre>
  
  <p>will return <strong>"2014-03-03 14:30:00-05"</strong> instead of throwing an exception</p>
  
  <p>So I thought of using this <a href="http://sql-info.de/postgresql/notes/to_date-to_timestamp-gotchas.html" rel="nofollow">approach</a>, that reverts back the output to text using to_char and comparing it with the original input, but a mask like <em>'DD/MM/YYYY HH24:MI'</em> will cast "06/03/2014 <strong>0</strong>:06" to "06/03/2014 <strong>00</strong>:06", the strings are different!</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The following example fails where it should work:</p>
  
  <pre><code>select to_timestamp_safe ('06/03/2014 0:06', 'DD/MM/YYYY HH24:MI');
  </code></pre>
  
  <p>output:</p>
  
  <pre><code>ERROR: Input date 06/03/2014 0:06 does not match output date 2014-03-06 00:06:00
  SQL state: P0001
  </code></pre>
  
  <p>Is there a smart way of safely validate strings to timestamptz without the above pitfalls?</p>