Re: [HACKERS] Possible Typecasting Bug with coalesce()

2006-07-19 Thread Zeugswetter Andreas DCP SD

  = select now()coalesce('Jul 14 2006 9:16:47AM');

 The only bug I see here is that implicit coercions to text 
 are a bad idea :-( --- IMHO it would be better if your first 
 query failed instead of giving you unexpected behavior.

:-) We know that you think that Tom, but a lot of us do not 
want to go casting all our sql, especially where other db's don't
require it.

Would an equivalent CASE statement also do the early conversion to text
? 

Andreas

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] Possible Typecasting Bug with coalesce()

2006-07-19 Thread MotherMGA
You are correct, Andreas.

= select now()'Jul 14 2006 9:16:47AM';
 ?column?
--
 t
(1 row)

= select now()  CASE WHEN 'Jul 14 2006 9:16:47AM' IS NOT NULL THEN
'Jul 14 2006 9:16:47AM' END;
 ?column?
--
 f
(1 row)

I've also found that there must be more than one typecasting function
being used because the result can be different depending on the format
of the timestamp string:

= select now()coalesce('Jul 14 2006 9:16:47AM');
 ?column?
--
 f
(1 row)

= select now()coalesce('2006-07-14 9:16:47');
 ?column?
--
 t
(1 row)


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] Possible Typecasting Bug with coalesce()

2006-07-18 Thread Tom Lane
MotherMGA [EMAIL PROTECTED] writes:
 = select now()coalesce('Jul 14 2006 9:16:47AM');

The coalesce() function is going to resolve its datatype as text in
this situation, and then text dominates timestamp in the comparison
(ie, the result of now() is coerced to text).  When you write

  = select now()'Jul 14 2006 9:16:47AM';

the literal's type doesn't have to be resolved until it's compared to
now(), and that comparison is what gives the parser the hint that the
literal ought to be considered to be a timestamp rather than just text.

 = select now()coalesce('Jul 14 2006 9:16:47AM'::timestamp with time
 zone);

This is what you need to do if you want the literal to be treated as
timestamp right off.

The only bug I see here is that implicit coercions to text are a bad
idea :-( --- IMHO it would be better if your first query failed instead
of giving you unexpected behavior.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq