Thread: setting the where clause

setting the where clause

From
johnf
Date:
Hi,
I'm am programming in python using the Dabo modules.  www.dabodev.com if your 
interested.  Dabo is a framework that provides an easy way to build desktop 
app's.  To clear a data entry form. I have been setting the where clause 
to "where 1=0".  This of course retrieves 0 records and my form will display 
nothing.  It has been suggested that it might be better to set the where 
clause to a primary key value that does not exist in the table  "where PK 
= -9999999".   

I wonder what you guys think.  How might it effect performance?
-- 
John Fabiani


Re: setting the where clause

From
Tom Lane
Date:
johnf <jfabiani@yolo.com> writes:
> I'm am programming in python using the Dabo modules.  www.dabodev.com if your 
> interested.  Dabo is a framework that provides an easy way to build desktop 
> app's.  To clear a data entry form. I have been setting the where clause 
> to "where 1=0".  This of course retrieves 0 records and my form will display 
> nothing.  It has been suggested that it might be better to set the where 
> clause to a primary key value that does not exist in the table  "where PK 
> = -9999999".   

Whoever said that doesn't know what they're talking about.  It's not
faster (because it forces an actual index probe to happen) and it's not
safer (what if one day that PK value exists?).

I'd personally go with "where false"; why not say what you mean rather
than forcing humans and computers to deduce that the condition is
constant false?
        regards, tom lane


Re: setting the where clause

From
Rob Sargent
Date:
Tom Lane wrote:
> johnf <jfabiani@yolo.com> writes:
>   
>> I'm am programming in python using the Dabo modules.  www.dabodev.com if your 
>> interested.  Dabo is a framework that provides an easy way to build desktop 
>> app's.  To clear a data entry form. I have been setting the where clause 
>> to "where 1=0".  This of course retrieves 0 records and my form will display 
>> nothing.  It has been suggested that it might be better to set the where 
>> clause to a primary key value that does not exist in the table  "where PK 
>> = -9999999".   
>>     
>
> Whoever said that doesn't know what they're talking about.  It's not
> faster (because it forces an actual index probe to happen) and it's not
> safer (what if one day that PK value exists?).
>
> I'd personally go with "where false"; why not say what you mean rather
> than forcing humans and computers to deduce that the condition is
> constant false?
>
>             regards, tom lane
>
>   

Of course you would do the world a favour if you wrote a client-side 
clear-the-screen routine that didn't pester the net and the server 
un-necessarily.


dynamic columns in a query

From
"Jyoti Seth"
Date:
Hi All,

Is there any way in postgres to write a query to display the result in
matrix form. (where column names are dynamic)

For eg.


Employee Name    Client1    Client2    Client3    Client4
Emp1            100        102        90        23
Emp2            56        0        23        98
Emp3            34        45        76        0


Here Client1, Client2... are the values from the database.

Thanks,
Jyoti




Re: dynamic columns in a query

From
"A. Kretschmer"
Date:
In response to Jyoti Seth :
> Hi All,
> 
> Is there any way in postgres to write a query to display the result in
> matrix form. (where column names are dynamic)
> 
> For eg.
> 
> 
> Employee Name    Client1    Client2    Client3    Client4
> Emp1            100        102        90        23
> Emp2            56        0        23        98
> Emp3            34        45        76        0
> 
> 
> Here Client1, Client2... are the values from the database.

There is a contrib-modul, tablefunc. It contains a crosstab-function.
Maybe this can help you. Other solution: write a function in plpgsql and
build a string that contains your query, and EXECUTE that string.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net


Re: dynamic columns in a query

From
Pavel Stehule
Date:
Hello

2009/6/11 Jyoti Seth <jyotiseth2001@gmail.com>:
> Hi All,
>
> Is there any way in postgres to write a query to display the result in
> matrix form. (where column names are dynamic)
>

look on http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html

regards
Pavel Stehule

> For eg.
>
>
> Employee Name   Client1 Client2 Client3 Client4
> Emp1                    100             102             90              23
> Emp2                    56              0               23              98
> Emp3                    34              45              76              0
>
>
> Here Client1, Client2... are the values from the database.
>
> Thanks,
> Jyoti
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: setting the where clause

From
Jasen Betts
Date:
On 2009-06-10, johnf <jfabiani@yolo.com> wrote:
> Hi,
> I'm am programming in python using the Dabo modules.  www.dabodev.com if your 
> interested.  Dabo is a framework that provides an easy way to build desktop 
> app's.  To clear a data entry form. I have been setting the where clause 
> to "where 1=0".  This of course retrieves 0 records and my form will display 
> nothing.  It has been suggested that it might be better to set the where 
> clause to a primary key value that does not exist in the table  "where PK 
>= -9999999".   

"where PK=NULL" is better as anything=NULL is never true. PK=-9999999
may be true sometimes.

As Tom says  PK=-9999999  causes postgres to look for a record that
matches, PK=NULL doesn't cause needless search.

that said if -9999999 is outside of the valid range for PK then the
search will finish very quickly as at worst only a sigle btree page
will need to be loaded.

in order of preference.
 no query at all where FALSE where PK=NULL where PK=-9999999 (note that this one may not work)