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 ===