Re: implicit cast of empty string to timestamp

am  10.02.2006, um 20:22:57 +1100 mailte James Harper folgendes:
> Is there anything I can do to make postgres allow an implicit cast of an
> empty string to a timestamp, so that a badly behaved application can do:

test=# select * from t1;
  x
-----

 foo
(2 rows)

test=# select case when x='' then to_char(now(),'DD-MM-YYYY HH:MM:SS')
else x end from t1;
          x
---------------------
 10-02-2006 10:02:37
 foo
(2 rows)



>
> INSERT INTO SomeTable (timestampfield) VALUES ('')

You can't insert a empty string into a timestamp, IIRC.

test=# create table t2 (id int, ts timestamp);
CREATE TABLE
test=# insert into t2 values (1, '');
ERROR:  invalid input syntax for type timestamp: ""

test=# insert into t2 values (1, NULL);
INSERT 0 1
test=# insert into t2 values (2, now());
INSERT 0 1
test=# select* from t2;
 id |             ts
----+----------------------------
  1 |
  2 | 2006-02-10 10:34:33.046152
(2 rows)

test=# select coalesce(ts, now()) from t2;
           coalesce
-------------------------------
 2006-02-10 10:35:03.426692+01
 2006-02-10 10:34:33.046152+01
(2 rows)

HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===