Thread: setting the where clause
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
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
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.
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
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
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 >
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)