Postgres Pro
Downloads
Home   >   mailing lists

Thread: implicit cast of empty string to timestamp

implicit cast of empty string to timestamp

From
"James Harper"
Date:
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:

INSERT INTO SomeTable (timestampfield) VALUES ('')

Where timestampfield is of type typestamp. ?

From what I understand of the 'CREATE CAST' command, I can't just create
a cast that only kicks in on empty strings whilst leaving casts of other
strings as is...

Thanks

James

Re: implicit cast of empty string to timestamp

From
"A. Kretschmer"
Date:
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    ===

Re: implicit cast of empty string to timestamp

From
"James Harper"
Date:
> 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:
>
> > INSERT INTO SomeTable (timestampfield) VALUES ('')
>
> You can't insert a empty string into a timestamp, IIRC.

No, and if someone tries I want to put a NULL in there.

I had hoped the following might work (syntax is from memory):

CREATE SCHEMA fnord;
SET search_path TO fnord;
CREATE FUNCTION fnord.timestamp (text) RETURNS timestamp LANGUAGE SQL AS
$$
SELECT pg_catalog.timestamp('20010101') $$;

SELECT CAST(text '20060101' AS timestamp);
SELECT CAST(text '' AS timestamp);

I had hoped that my 'timestamp' function would have overridden the
unqualified function call in the cast, but it was not to be :(

Curiously tho, \df didn't even acknowledge the 'timestamp' function in
the database, even though it was in the pg_proc's table.

Oh well.

James

Re: implicit cast of empty string to timestamp

From
"James Harper"
Date:
How much trouble am I going to get into by modifying the pg_cast table
to call my function instead?

I created this function:

CREATE OR REPLACE FUNCTION pg_catalog.mssql_timestamp (text)
RETURNS timestamp
LANGUAGE SQL
AS $$
SELECT CASE WHEN $1 = '' THEN NULL ELSE pg_catalog.timestamp($1) END
$$;

And then updated the pg_cast table with this statement:

UPDATE pg_cast
SET castfunc = (SELECT Oid FROM pg_proc WHERE proname =
'mssql_timestamp') WHERE castfunc = (SELECT Oid FROM pg_proc WHERE
prosrc = 'text_timestamp')

Now the following work:
SELECT CAST(text '' AS timestamp);
SELECT CAST(char '' AS timestamp);
SELECT CAST(varchar '' AS timestamp);

But this doesn't yet:
SELECT CAST('' AS timestamp);

I can probably get it working, but is it really a good idea to be
fiddling with the pg_cast table like that?

Thanks

James

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of James Harper
> Sent: Friday, 10 February 2006 20:23
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] implicit cast of empty string to timestamp
>
> 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:
>
> INSERT INTO SomeTable (timestampfield) VALUES ('')
>
> Where timestampfield is of type typestamp. ?
>
> From what I understand of the 'CREATE CAST' command, I can't just
create
> a cast that only kicks in on empty strings whilst leaving casts of
other
> strings as is...
>
> Thanks
>
> James
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

Re: implicit cast of empty string to timestamp

From
Tom Lane
Date:
"James Harper" <james.harper@bendigoit.com.au> writes:
> How much trouble am I going to get into by modifying the pg_cast table
> to call my function instead?

You can doubtless hack it to work if you slash-and-burn hard enough.
The question is why don't you fix your buggy application instead ...

            regards, tom lane

Re: implicit cast of empty string to timestamp

From
"James Harper"
Date:
Not my application :(

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Saturday, 11 February 2006 01:59
> To: James Harper
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] implicit cast of empty string to timestamp
>
> "James Harper" <james.harper@bendigoit.com.au> writes:
> > How much trouble am I going to get into by modifying the pg_cast
table
> > to call my function instead?
>
> You can doubtless hack it to work if you slash-and-burn hard enough.
> The question is why don't you fix your buggy application instead ...
>
>             regards, tom lane