Re: [GENERAL] case when evaluating else condition anyway?
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > Still why does the else condition get evaluated anyway when at least > one of the "when" condition is true? Because 'literal'::type is a literal of that type; it does not represent a run-time conversion. I think we document that under the discussion of constants in the syntax chapter. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case when evaluating else condition anyway?
On Wed, Nov 26, 2008 at 04:17:40PM +0100, Ivan Sergio Borgonovo wrote: > as a more self contained example: > > select case when ''='' then null else ''::timestamp end; Tee hee, I've just realized what you're doing. You've got the cast in the wrong place! Try: select case when ''='' then null else '' end::timestamp; I was reading it this way around automatically! Literals are always expanded immediately and hence you're getting the error. You want the case statement to work with strings and only cast it when you know it's actually safe to make the move from a string literal to a timestamp value. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case when evaluating else condition anyway?
On Wed, 26 Nov 2008 14:18:44 + Sam Mason <[EMAIL PROTECTED]> wrote: > On Wed, Nov 26, 2008 at 02:53:07PM +0100, Ivan Sergio Borgonovo > wrote: > > case when ''=extinput then null else extinput::timestamp end > > I'd tend to use nullif(extinput,'')::timestamp for this sort of Thanks it was on the top of my fingers... BTW it was a substituted literal ala printf before anything was piped to postgresql, so actually postgresql would receive something as the examples below. Still why does the else condition get evaluated anyway when at least one of the "when" condition is true? > thing. It's not going to do anything much different from what > you're doing, but may end up evaluating "extinput" less which may > be a good thing. > > > Is there any cleaner functional way that doesn't involve prepared > > statement etc... since the whole exercise is caused by an > > null-impaired DB API (aka MySQLish). > > As Tom said, maybe if you could send a larger example it would > help. as a more self contained example: select case when ''='' then null else ''::timestamp end; vs. select nullif('','')::timestamp; thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case when evaluating else condition anyway?
On Wed, Nov 26, 2008 at 02:53:07PM +0100, Ivan Sergio Borgonovo wrote: > case when ''=extinput then null else extinput::timestamp end I'd tend to use nullif(extinput,'')::timestamp for this sort of thing. It's not going to do anything much different from what you're doing, but may end up evaluating "extinput" less which may be a good thing. > Is there any cleaner functional way that doesn't involve prepared > statement etc... since the whole exercise is caused by an > null-impaired DB API (aka MySQLish). As Tom said, maybe if you could send a larger example it would help. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case when evaluating else condition anyway?
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > I've > case when ''=extinput then null else extinput::timestamp end > now when extinput='' it seems that else extinput::timestamp get > evaluated anyway and I get: > invalid input syntax for type timestamp: "" I think you are leaving off a large percentage of the truth. Is "extinput" actually a variable, or do you mean that you are substituting a literal string there? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] case when evaluating else condition anyway?
I've case when ''=extinput then null else extinput::timestamp end now when extinput='' it seems that else extinput::timestamp get evaluated anyway and I get: invalid input syntax for type timestamp: "" The purpose of all this gymnic would be to convert empty string to null and everything else to a timestamp. Is there any cleaner functional way that doesn't involve prepared statement etc... since the whole exercise is caused by an null-impaired DB API (aka MySQLish). -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general