Thread: help: now() + N is now failing!
help. does anybody know how, without using date_pli, i can make now() + N
work in newer versions of postgres?
we ported our old data to a new db.
VALUE = NULL doesn't work anymore, had to use IS NULL. had to modify
hundred liners of scripts :(
i don't wanna go about modifying all the scripts again to modify now() + N
in my old scripts.
i've been a victim of backwards compatibility issue and late side-effect of
my DB infancy sydrome years ago :(
TIA!
--OLD postgres
old_postgres=# select now();
now
------------------------
2003-07-29 16:11:11+08
(1 row)
old_postgres=# select now() + 2;
?column?
------------
2003-07-31
(1 row)
-- NEW postgres
new_postgres=# select now();
now
-------------------------------
2003-07-29 16:31:39.494534+08
(1 row)
new_postgres=# select now() + 2;
ERROR: Unable to identify an operator '+' for types 'timestamp with time
zone' and 'integer'
You will have to retype this query using an explicit cast
"Mel Jamero" <mel@gmanmi.tv> writes:
> help. does anybody know how, without using date_pli, i can make now() + N
> work in newer versions of postgres?
Sure, make a custom operator.
regression=# select now() + 5;
ERROR: operator does not exist: timestamp with time zone + integer
HINT: No operator matches the given name and argument type(s). You may need to
add explicit typecasts.
regression=# create function tstz_plus_int(timestamp with time zone, int)
regression-# returns date as
regression-# 'select $1::date + $2' language sql;
CREATE FUNCTION
regression=# create operator + (
regression(# procedure = tstz_plus_int,
regression(# leftarg = timestamp with time zone,
regression(# rightarg = int);
CREATE OPERATOR
regression=# select now() + 5;
?column?
------------
2003-08-03
(1 row)
regression=#
regards, tom lane
Yeah... Looks weird. For some reason, even date_pli(now(), 2) doesn't work any more - you have to do date_pli(now()::date,2) I guess, the now()+2 has the same problem - for some reason, it fails to coerce timestamptz into date automatically :-( Why is that? There is only one function called date_pli(), and there is an unambigous conversion date(timestamptz)... Why does it now force the user to cast explicitly??? Dima Mel Jamero wrote: >help. does anybody know how, without using date_pli, i can make now() + N >work in newer versions of postgres? > >we ported our old data to a new db. > >VALUE = NULL doesn't work anymore, had to use IS NULL. had to modify >hundred liners of scripts :( > >i don't wanna go about modifying all the scripts again to modify now() + N >in my old scripts. > >i've been a victim of backwards compatibility issue and late side-effect of >my DB infancy sydrome years ago :( > >TIA! > >--OLD postgres > >old_postgres=# select now(); > now >------------------------ > 2003-07-29 16:11:11+08 >(1 row) > >old_postgres=# select now() + 2; > ?column? >------------ > 2003-07-31 >(1 row) > >-- NEW postgres > >new_postgres=# select now(); > now >------------------------------- > 2003-07-29 16:31:39.494534+08 >(1 row) > >new_postgres=# select now() + 2; >ERROR: Unable to identify an operator '+' for types 'timestamp with time >zone' and 'integer' > You will have to retype this query using an explicit cast > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
On Tue, 29 Jul 2003, Dmitry Tkach wrote: > Yeah... Looks weird. > For some reason, even date_pli(now(), 2) doesn't work any more - you > have to do date_pli(now()::date,2) > I guess, the now()+2 has the same problem - for some reason, it fails to > coerce timestamptz into date automatically :-( > Why is that? There is only one function called date_pli(), and there is > an unambigous conversion date(timestamptz)... Why does it now force the > user to cast explicitly??? It's not an implicit cast any longer (see pg_cast). Implicit casts often result in unexpected behavior. For example, what should now()+2 mean? Converting to date and adding two days is fairly wierd behavior, I'd much more expect it to say add 2 seconds or error. now() + interval '2 days' or cast(now() as date)+2 both express the intent of adding 2 days much better.
Dmitry Tkach <dmitry@openratings.com> writes:
> I guess, the now()+2 has the same problem - for some reason, it fails to
> coerce timestamptz into date automatically :-(
> Why is that? There is only one function called date_pli(), and there is
> an unambigous conversion date(timestamptz)... Why does it now force the
> user to cast explicitly???
Because it's an information-losing coercion.
To reduce the odds that a surprising interpretation will be chosen, we
have tightened the casting rules so that only up-promotions and not
down-promotions will happen implicitly within expressions.
As a comparison point, you would like float8 + int4 to be done in
float8 rather than int4 arithmetic, would you not? If both directions
of casting are equally implicit then there's no basis for the parser to
choose the preferred operator.
An alternative approach for Mel to fix his problem is to change the
pg_cast entry for timestamptz-to-date to allow it to happen implicitly,
but (a) that might have unforeseen side effects in other contexts, and
(b) it wouldn't get dumped by pg_dump, as a custom operator will.
regards, tom lane
thanks to all you guys. now (and now() + 2), things are clearer =)
tom, thank you!
mel
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, July 29, 2003 11:20 PM
To: Dmitry Tkach
Cc: mel@gmanmi.tv; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] help: now() + N is now failing!
Dmitry Tkach <dmitry@openratings.com> writes:
> I guess, the now()+2 has the same problem - for some reason, it fails to
> coerce timestamptz into date automatically :-(
> Why is that? There is only one function called date_pli(), and there is
> an unambigous conversion date(timestamptz)... Why does it now force the
> user to cast explicitly???
Because it's an information-losing coercion.
To reduce the odds that a surprising interpretation will be chosen, we
have tightened the casting rules so that only up-promotions and not
down-promotions will happen implicitly within expressions.
As a comparison point, you would like float8 + int4 to be done in
float8 rather than int4 arithmetic, would you not? If both directions
of casting are equally implicit then there's no basis for the parser to
choose the preferred operator.
An alternative approach for Mel to fix his problem is to change the
pg_cast entry for timestamptz-to-date to allow it to happen implicitly,
but (a) that might have unforeseen side effects in other contexts, and
(b) it wouldn't get dumped by pg_dump, as a custom operator will.
regards, tom lane
Tom Lane wrote: >Dmitry Tkach <dmitry@openratings.com> writes: > > >>I guess, the now()+2 has the same problem - for some reason, it fails to >>coerce timestamptz into date automatically :-( >>Why is that? There is only one function called date_pli(), and there is >>an unambigous conversion date(timestamptz)... Why does it now force the >>user to cast explicitly??? >> >> > >Because it's an information-losing coercion. > >To reduce the odds that a surprising interpretation will be chosen, we >have tightened the casting rules so that only up-promotions and not >down-promotions will happen implicitly within expressions. > Ouch! Without getting into arguing the rationale of this decision, isn't it going to be *tons* of the application code, that will stop working after the migration? :-( > >As a comparison point, you would like float8 + int4 to be done in >float8 rather than int4 arithmetic, would you not? If both directions >of casting are equally implicit then there's no basis for the parser to >choose the preferred operator. > Well... the basis would be to prefer up-promotion over a down-promotion, for example... Or, if they both are the same direction, then, yes, I agree, that it is ambigous, and should cause an error... But in cases like date_pli (now(), 2) - there is only one alternative, thus no ambiguity - why not just do it? Dima
Stephan Szabo wrote: >On Tue, 29 Jul 2003, Dmitry Tkach wrote: > > > >>Yeah... Looks weird. >>For some reason, even date_pli(now(), 2) doesn't work any more - you >>have to do date_pli(now()::date,2) >>I guess, the now()+2 has the same problem - for some reason, it fails to >>coerce timestamptz into date automatically :-( >>Why is that? There is only one function called date_pli(), and there is >>an unambigous conversion date(timestamptz)... Why does it now force the >>user to cast explicitly??? >> >> > >It's not an implicit cast any longer (see pg_cast). Implicit casts often >result in unexpected behavior. > >For example, what should now()+2 mean? Converting to date and adding two >days is fairly wierd behavior, I'd much more expect it to say add 2 >seconds or error. now() + interval '2 days' or cast(now() as date)+2 both >express the intent of adding 2 days much better. > > Ok, granted that, but what's wrong with date_pli (now(), 2)? Dima
Dmitry Tkach <dmitry@openratings.com> writes: > But in cases like date_pli (now(), 2) - there is only one alternative, > thus no ambiguity - why not just do it? No, there are zero alternatives. And we're actually moving slowly on this issue to make the transition less painful for people. If I had my druthers 7.3 would have been much more draconian about implicit casts. For an example of surprising behavior that is still there, reflect on this open bug report: http://archives.postgresql.org/pgsql-bugs/2001-10/msg00103.php http://archives.postgresql.org/pgsql-bugs/2001-10/msg00108.php The details have changed since 7.1, but we still end up comparing the values as if they were text strings; and there is no way to avoid this except to stop treating casts to text as implicitly invocable. regards, tom lane
On Tue, 29 Jul 2003, Dmitry Tkach wrote:
> Stephan Szabo wrote:
>
> >On Tue, 29 Jul 2003, Dmitry Tkach wrote:
> >
> >
> >
> >>Yeah... Looks weird.
> >>For some reason, even date_pli(now(), 2) doesn't work any more - you
> >>have to do date_pli(now()::date,2)
> >>I guess, the now()+2 has the same problem - for some reason, it fails to
> >>coerce timestamptz into date automatically :-(
> >>Why is that? There is only one function called date_pli(), and there is
> >>an unambigous conversion date(timestamptz)... Why does it now force the
> >>user to cast explicitly???
> >>
> >>
> >
> >It's not an implicit cast any longer (see pg_cast). Implicit casts often
> >result in unexpected behavior.
> >
> >For example, what should now()+2 mean? Converting to date and adding two
> >days is fairly wierd behavior, I'd much more expect it to say add 2
> >seconds or error. now() + interval '2 days' or cast(now() as date)+2 both
> >express the intent of adding 2 days much better.
> >
> >
> Ok, granted that, but what's wrong with date_pli (now(), 2)?
It's a similar issue. As a potential counter example, what about
date_pli('foo', 2) or date_pli(inet '1.1.1.1', 2)? There's a cast
from text->date and a cast inet->text. If you allow downcasts and
arbitrary casts you can get some really odd behavior.
>
>
>>Ok, granted that, but what's wrong with date_pli (now(), 2)?
>>
>>
>
>It's a similar issue. As a potential counter example, what about
>date_pli('foo', 2) or date_pli(inet '1.1.1.1', 2)? There's a cast
>from text->date and a cast inet->text. If you allow downcasts and
>arbitrary casts you can get some really odd behavior.
>
Well... this would be a two-step cast.. That was never allowed (was
it?), and I never argued that it should...
Dima
>
>
>
>
Stephan Szabo wrote:
>The first doesn't, date_pli('foo', 2) that's just text->date
>
But you can't cast 'foo' into date, can you? I mean, there is a cast,
but it will fail, right.
And if you used a valid date textual representation, then, I don't see
any reason why
date_pli ('29/07/03', 2) should not work
And, as a matter of fact, it *does*, unless you add '::text' to it (in
7.3 - 7.2 is still OK with it).
> (the other
>does, but it was a more rediculous example using two casts - I don't
>see much stretch from allowing all casts implicitly to allowing multiple
>casts really in the abstract anyway)
>
Well... I don't know about the 'stretch' :-)
I am just trying to use the analogy with programming language (like C++,
or Java) that allow function/operator overloading - they do allow
single-step casts (as long as it is no ambigous), but not multiple step
... they also allow downcasting (e.g. using a superclass of the declared
argument argument), even for "primitive" types actually (it will
implicitly coerce a double into an int for example if you send it into a
function that expects an int, even though it will complain about it
first, which, BTW I believe to be inconsistent - if you can coerce a
subclass into a superclass, there is not much difference in casting
double into an int really)...
So, I just have a little trouble understanding why postgres cannot use
those same rules, and has instead to "reinvent the bycicle" and come up
with some of its own...
Dima
On Tue, 29 Jul 2003, Dmitry Tkach wrote:
> >
> >
> >>Ok, granted that, but what's wrong with date_pli (now(), 2)?
> >>
> >>
> >
> >It's a similar issue. As a potential counter example, what about
> >date_pli('foo', 2) or date_pli(inet '1.1.1.1', 2)? There's a cast
> >from text->date and a cast inet->text. If you allow downcasts and
> >arbitrary casts you can get some really odd behavior.
> >
> Well... this would be a two-step cast.. That was never allowed (was
> it?), and I never argued that it should...
The first doesn't, date_pli('foo', 2) that's just text->date (the other
does, but it was a more rediculous example using two casts - I don't
see much stretch from allowing all casts implicitly to allowing multiple
casts really in the abstract anyway)
Tom Lane wrote: >Dmitry Tkach <dmitry@openratings.com> writes: > > >>But in cases like date_pli (now(), 2) - there is only one alternative, >>thus no ambiguity - why not just do it? >> >> > >No, there are zero alternatives. And we're actually moving slowly on >this issue to make the transition less painful for people. If I had my >druthers 7.3 would have been much more draconian about implicit casts. >For an example of surprising behavior that is still there, reflect on >this open bug report: >http://archives.postgresql.org/pgsql-bugs/2001-10/msg00103.php >http://archives.postgresql.org/pgsql-bugs/2001-10/msg00108.php > > >The details have changed since 7.1, but we still end up comparing the >values as if they were text strings; and there is no way to avoid this >except to stop treating casts to text as implicitly invocable. > > Couldn't those problems be fixed by simply adding an implicit type conversion from numerical types to interval? I understand, that disallowing implicit casts alltogether is easier, and more general (so that you don't need to care about every case separately), but doesn't it take out lots of flexibility (and, what's worse, break existing code, that used to work before)? I don't know if this is a valid comparison, but a parallel with C++ comes to mind - if you have a two classess - Date and Timestamp:Date, and a function date_pli (Date, int) (or operator + (Date, int)), returning Date, it is perfectly valid to pass a Timestamp into it, right? Why should it be different in postgres (I know, you'll tell me that I can't do this trick with a double (Timestamp) -> int (Date) cast in C++, but that's an implementation detail - from the user perspective, why is it supposed to matter how exactly dates and timestamps are represented internally?) Dima
On Tue, 29 Jul 2003, Dmitry Tkach wrote:
> Stephan Szabo wrote:
>
> >The first doesn't, date_pli('foo', 2) that's just text->date
> >
> But you can't cast 'foo' into date, can you? I mean, there is a cast,
> but it will fail, right.
> And if you used a valid date textual representation, then, I don't see
> any reason why
> date_pli ('29/07/03', 2) should not work
>
> And, as a matter of fact, it *does*, unless you add '::text' to it (in
> 7.3 - 7.2 is still OK with it).
That's because it's actually unknown, not text. But, should
date_pli(textcolumn, 2) work if and only if all the values of the column
happen to be date like? That's pretty brittle and even more so since
there's no obvious conversion being done.
You use an example of C++ and inheritance, but Timestamp is not a subclass
of Date or vice versa. Effectively we have a Date(Timestamp) explicit
constructor. You can't call a date expecting function with a timestamp
because the conversion is not legal unless explicitly made. And C++ at
least does allow multiple conversions, just not multiple user-defined
conversions (for example int->double, double->classtype is allowed).
Stephan Szabo wrote:
>On Tue, 29 Jul 2003, Dmitry Tkach wrote:
>
>
>
>>Stephan Szabo wrote:
>>
>>
>>
>>>The first doesn't, date_pli('foo', 2) that's just text->date
>>>
>>>
>>>
>>But you can't cast 'foo' into date, can you? I mean, there is a cast,
>>but it will fail, right.
>>And if you used a valid date textual representation, then, I don't see
>>any reason why
>>date_pli ('29/07/03', 2) should not work
>>
>>And, as a matter of fact, it *does*, unless you add '::text' to it (in
>>7.3 - 7.2 is still OK with it).
>>
>>
>
>That's because it's actually unknown, not text.
>
I know :-)
> But, should
>date_pli(textcolumn, 2) work if and only if all the values of the column
>happen to be date like? That's pretty brittle and even more so since
>there's no obvious conversion being done.
>
Well... This sounds like a different topic alltogether to me -
generally, the question is should it be possible at all to *cast*
(whether explicitly or implicitly) type A into type B when the
conversion is only possible for a small subset of possible values of type A.
I mean, something like parse_date(text) is fine, but textcolumn::date
is, indeed, questionable.
I don't really know the answer to this question (but I do lean toward
the position, that conversions like this should not be possible as casts
per se)...
But my point is that if you can (unambigously) cast a value into a
target type, then in the tradition of commonly used programming
languages, it would be reasonable to expect it to be possible to invoke
a function that expects that type as an argument with the original value
as a parameter, and have it get casted implicitly to match the function
declaration.
My understanding is that this was, pretty much, the whole idea behind
the 'polymorphism', and function overloading in general -
you define an 'operator +' for your basic types, and expect it to work
on anything you send in... Having to define it separately for every
possible combination of types (and having the code do the exact same
thing in most of the cases) kinda defeats the purpose, doesn't it?
>
>You use an example of C++ and inheritance, but Timestamp is not a subclass
>of Date or vice versa.
>
I knew, you'd say that :-)
That example was meant as just an analogy.
The timestamp being or not being a subclass of date is an implementation
detail. From the user's standpoint, for all intents and purposes, it
*is* - timestamp is a date, with some additional information...
>Effectively we have a Date(Timestamp) explicit
>constructor.
>
Ok, that beats your previous point, right? :-)
I mean, in C++ it is enough to have a constructor like that defined,
even if timestamp is not not a subclass of date, it can still be
implicitly converted, as long as the constructor is defined.... :-)
>You can't call a date expecting function with a timestamp
>because the conversion is not legal unless explicitly made. And C++ at
>least does allow multiple conversions, just not multiple user-defined
>conversions (for example int->double, double->classtype is allowed).
>
>
>
Yep. You are right, I forgot about that...
Well.. perhaps, postgres should allow that too eventually then :-)
But, just allowing single-step conversions for now would be agood
starting point anyway... :-)
Dima
Dmitry Tkach <dmitry@openratings.com> writes:
> Tom Lane wrote:
>> The details have changed since 7.1, but we still end up comparing the
>> values as if they were text strings; and there is no way to avoid this
>> except to stop treating casts to text as implicitly invocable.
>>
> Couldn't those problems be fixed by simply adding an implicit type
> conversion from numerical types to interval?
No, that only masks the problem: the real problem IMHO is precisely that
the system may select a surprising conversion if the conversion you were
expecting doesn't exist. (Sometimes even if it *does* exist.) The more
implicit conversions there are, the bigger the risk that the parser will
do something you didn't expect.
If we go down the path you propose, we might as well not have a type
system at all, because we will end up with anything implicitly
convertible to anything else. But long before then, it'll stop being
a useful system, because the parser will be unable to pick any operators
due to having too many possible interpretations.
> I don't know if this is a valid comparison, but a parallel with C++
> comes to mind - if you have a two classess - Date and Timestamp:Date,
> and a function
> date_pli (Date, int) (or operator + (Date, int)), returning Date, it is
> perfectly valid to pass a Timestamp into it, right?
One of the problems people have with using C++ that way is that the
compiler tends to pick unexpected interpretations --- which is exactly
the problem I'm complaining about for Postgres. Ask anyone who's worked
on large systems in C++, they'll have some horror stories to tell you...
regards, tom lane
On Tue, 29 Jul 2003, Dmitry Tkach wrote:
> Stephan Szabo wrote:
>
> >On Tue, 29 Jul 2003, Dmitry Tkach wrote:
> >
> >
> >
> >>Stephan Szabo wrote:
> >>
> >>
> >>
> >>>The first doesn't, date_pli('foo', 2) that's just text->date
> >>>
> >>>
> >>>
> >>But you can't cast 'foo' into date, can you? I mean, there is a cast,
> >>but it will fail, right.
> >>And if you used a valid date textual representation, then, I don't see
> >>any reason why
> >>date_pli ('29/07/03', 2) should not work
> >>
> >>And, as a matter of fact, it *does*, unless you add '::text' to it (in
> >>7.3 - 7.2 is still OK with it).
> >>
> >>
> >
> >That's because it's actually unknown, not text.
> >
> I know :-)
>
> > But, should
> >date_pli(textcolumn, 2) work if and only if all the values of the column
> >happen to be date like? That's pretty brittle and even more so since
> >there's no obvious conversion being done.
> >
> Well... This sounds like a different topic alltogether to me -
> generally, the question is should it be possible at all to *cast*
> (whether explicitly or implicitly) type A into type B when the
> conversion is only possible for a small subset of possible values of type A.
> I mean, something like parse_date(text) is fine, but textcolumn::date
> is, indeed, questionable.
I don't mind an explicit conversion as much because at least you know
that you're getting it. Implicit conversions mean that a user has no
reason to know (apart from name in this case) that the query should fail
if you put 'T' in the column whereas a query like textcol::date is a
pretty big hint.
> But my point is that if you can (unambigously) cast a value into a
> target type, then in the tradition of commonly used programming
> languages, it would be reasonable to expect it to be possible to invoke
> a function that expects that type as an argument with the original value
> as a parameter, and have it get casted implicitly to match the function
> declaration.
>
> My understanding is that this was, pretty much, the whole idea behind
> the 'polymorphism', and function overloading in general -
> you define an 'operator +' for your basic types, and expect it to work
> on anything you send in... Having to define it separately for every
> possible combination of types (and having the code do the exact same
> thing in most of the cases) kinda defeats the purpose, doesn't it?
You don't have to do one for every combination of types, only one for the
reasonable ends of casting chains that make sense.
The problem with downcasts is that the source type doesn't (always/often)
give you reasonable values in the destination type. int8->int2 for
example is probably unspecified behavior in C (for the signed type) for
almost all values in int8. You can call that short function with your
long long, but the value you get in isn't likely to be what you expect in
most cases.
> >You use an example of C++ and inheritance, but Timestamp is not a subclass
> >of Date or vice versa.
> >
> I knew, you'd say that :-)
> That example was meant as just an analogy.
> The timestamp being or not being a subclass of date is an implementation
> detail. From the user's standpoint, for all intents and purposes, it
> *is* - timestamp is a date, with some additional information...
I guess the problem is that I don't agree. To me timestamps and dates are
different but related things. I'll agree that a timestamp may contain a
date or the data that a date contains, though. ;)
> >Effectively we have a Date(Timestamp) explicit
> >constructor.
> >
> Ok, that beats your previous point, right? :-)
> I mean, in C++ it is enough to have a constructor like that defined,
> even if timestamp is not not a subclass of date, it can still be
> implicitly converted, as long as the constructor is defined.... :-)
No. I meant explicit in the C++ meaning, the constructor is not considered
for implicit conversions but is available to be called explicitly like
Date(timestampval).
Stephan Szabo wrote: >I don't mind an explicit conversion as much because at least you know >that you're getting it. Implicit conversions mean that a user has no >reason to know (apart from name in this case) that the query should fail >if you put 'T' in the column whereas a query like textcol::date is a >pretty big hint. > Whatever... I have my reservations regarding how much of a hit this really is (I mean a person who tries to compare a text column to a date, and expects it to just magically always work, hardly deserves to be expected to see anything behind that '::date' thing other then a weird syntax construct :-) But, as I said, I don't really have an opinion on this one - whether parsing a text string into a date should be called a 'cast' or not... My point is that if you do call certain type conversions 'a cast', and you do allow implicit conversions in *some* cases (e.g. select * from table where textcol < 3), then it is actually *more* confusing to the user when you "hand-pick" some of the type combinations and disallow those conversions, then it would be, if you just had some simple (and commonly accepted) rule - like allow any unambigous single-step conversions for example... Or, for that matter - just never do any implicit conversions at all - this would not be, of course something I'd like to happen :-), but, at least, it would not make me wonder 'is this going to work or not' every time I type something into psql... >You don't have to do one for every combination of types, only one for the >reasonable ends of casting chains that make sense. > > Yeah... but they are not always "chains" per se - they could be trees, they could even have loops... I guess, I could inspect all those graphs, decide what operators I want defined, then make sure that they are not already defined in postgres, then create all of those... This begs the question though - why have *any* predefined operators at all - if you did not have any, this task would actually be easier, because, at least, I would not have to check my 'wish-list' of operators against what's already defined in pg :-) >The problem with downcasts is that the source type doesn't (always/often) >give you reasonable values in the destination type. int8->int2 for >example is probably unspecified behavior in C (for the signed type) for >almost all values in int8. You can call that short function with your >long long, but the value you get in isn't likely to be what you expect in >most cases. > Ok. No disagreement here... "C" gives you a warning in such case... and postgres could do the same, or it could even refuse to do this completely - fine with me. *But* if there is an explicitly defined conversion function (like date(timestamp) in our case), that *does* work for all the values (granted, that you don't know if it actually does, but, the point being - if it is defined explicitly, you should be able to assume that) - if such a function is defined, it can safely be used for a type conversion. Once again, if you want to argue against *any* implicit type conversion *ever* - that would be a different thing. But the way it is now, just seems very confusing, because *sometimes* it does work, and sometimes it doesn't, and I really fail to see any difference at all - why, for example select * from mytable where timestampcol < 3 .. works, but select * from mytable where timestampcol + 1 < 4 does *not*? Both queries make equally little sense... :-) This seems to be a bug *whichever* way you look at it - either you should make the first one fail, or you should make both of them work as expected. 7.2 seems to be much closer to the latter - since it has a date(int) conversion, and it allows timestamp + int... so, the only thing missing is timestamp(int)... And 7.3 seems to be actually *worse* in this respect - it is as much (if not more) confusing, *and* some of the stuff one used to be able to do in 7.2 is not longer possible :-( > > >>>Effectively we have a Date(Timestamp) explicit >>>constructor. >>> >>> >>> >>Ok, that beats your previous point, right? :-) >>I mean, in C++ it is enough to have a constructor like that defined, >>even if timestamp is not not a subclass of date, it can still be >>implicitly converted, as long as the constructor is defined.... :-) >> >> > >No. I meant explicit in the C++ meaning, the constructor is not considered >for implicit conversions but is available to be called explicitly like >Date(timestampval). > > Right... but "in C++ meaning" having such a constructor is enough to have the parameter be implicitly converted when necessary - so that timestamp doesn't need to be a subclass of date - just having a Date(Timestamp) thing is enough. Dima
On Tue, 29 Jul 2003, Dmitry Tkach wrote: > Stephan Szabo wrote: > > >I don't mind an explicit conversion as much because at least you know > >that you're getting it. Implicit conversions mean that a user has no > >reason to know (apart from name in this case) that the query should fail > >if you put 'T' in the column whereas a query like textcol::date is a > >pretty big hint. > > > Whatever... I have my reservations regarding how much of a hit this > really is (I mean a person who tries to compare a text column to a date, > and expects it to just magically always work, hardly deserves to be > expected to see anything behind that '::date' thing other then a weird > syntax construct :-) > > But, as I said, I don't really have an opinion on this one - whether > parsing a text string into a date should be called a 'cast' or not... > > My point is that if you do call certain type conversions 'a cast', and > you do allow implicit conversions in *some* cases (e.g. select * from > table where textcol < 3), > then it is actually *more* confusing to the user when you "hand-pick" > some of the type combinations and disallow those conversions, then it > would be, if you just had some simple (and commonly accepted) rule - > like allow any unambigous single-step conversions for example... > Or, for that matter - just never do any implicit conversions at all - > this would not be, of course something I'd like to happen :-), but, at > least, it would not make me wonder 'is this going to work or not' every > time I type something into psql... You have to anyway - see my notes on your queries from below. > But the way it is now, just seems very confusing, because *sometimes* it > does work, and sometimes it doesn't, and I really fail to see any > difference at all - > why, for example > select * from mytable where timestampcol < 3 > .. works, but In 7.3 this does a conversion to text. In 7.2 it does a conversion to abstime. > select * from mytable where timestampcol + 1 < 4 > does *not*? On my 7.2 machine this does end up using text comparison. That's probably not what you meant to compare. ;)
"Tom Lane" <tgl@sss.pgh.pa.us> wrote: > One of the problems people have with using C++ that way is that the > compiler tends to pick unexpected interpretations --- which is exactly > the problem I'm complaining about for Postgres. Ask anyone who's worked > on large systems in C++, they'll have some horror stories to tell you... I agree partialy with you, implicit cast save you a lot of work and people that had horror stories are people that are not using the "explicit" keyword in the constructor. May be is a good idea to permit this kind of hint in the function declaration for postgres: create function foo( explicit timestamptz, int ); so either if the cast text->timestamptz exist you can not call select foo( text, int ). Regards Gaetano Mendola