hi,

i have a zip code field in a table that is used by "international"
customers, which lead to very "random" data there. but a query should be
possible "by number" if there is only a number in the field. for
queriing the table an admin user can generate queries with a visual
interface - but the optimizier then kills my logic:

[fragment from the query - plz is the zip-code field]

...
) AND (
eintrag.email like '%example.com' -- #1
OR (
eintrag.plz ~ '^[0-9]{1,9}$'
AND
int4(eintrag.plz) = '0'
) -- #2
)
...

an EXPLAIN shows me the result:

...
) AND (
(
(plz)::text ~ '^[0-9]{1,9}$'::text
) OR (
email ~~ '%example.com'::text
)
) AND (
(
int4((plz)::text) = 0
) OR (
email ~~ '%example.com'::text
)
)
...

of course this somehow "correct" assuming only boolean-logic - but not
for someone thinking in terms of a C-programmer.

please note: i can not reorder the #1 and #2 query parts, as the user
gives the order. i can only influence the generated sql-code to some
extends. of course another option would be to "fix the cast, if its
numbers" - but i have lots of other query parts, that contain more then
one compare itself that depend on each other.

--
cu

Search Discussions

  • Tom Lane at Sep 14, 2005 at 2:03 pm

    Christoph Frick writes:
    i have a zip code field in a table that is used by "international"
    customers, which lead to very "random" data there. but a query should be
    possible "by number" if there is only a number in the field. for
    queriing the table an admin user can generate queries with a visual
    interface - but the optimizier then kills my logic:
    Please see
    http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
    which specifically disclaims any promise of left-to-right evaluation
    order.

    Having said that, 8.0 and up no longer try to force the WHERE clause
    into canonical AND-of-ORs form, so it's possible that what you would
    like would happen in a newer version.

    But my advice is to find another way to do it (maybe put the logic into
    a plpgsql function?). You will get absolutely zero sympathy for any
    request to constrain evaluation order of a WHERE clause --- if we did
    that it would completely defeat the ability to do query optimization of
    any kind. Accordingly, if future changes break your code again, you
    won't have any recourse.

    regards, tom lane
  • Christoph Frick at Sep 14, 2005 at 2:11 pm

    On Wed, Sep 14, 2005 at 10:03:37AM -0400, Tom Lane wrote:

    http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
    thanks for this url - i can use the described CASE syntax for my case. i
    just have to assure using the regexp, that the later cast to int4 will
    work. i was not aware of the possibility to use the CASE also "behind"
    the WHERE and not only "before".

    --
    cu

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedSep 14, '05 at 9:21a
activeSep 14, '05 at 2:11p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Christoph Frick: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase