Re: [SQL] ORDER BY and NULLs

2004-10-30 Thread Murphy Pope
>> You should just cross out that whole section. It's just flatly wrong. >> >> I had always assumed it was just people bringing assumptions over from >> Oracle where it is true. Perhaps this book is to blame for some of the >> confusion. Which book is it? >> >> Postgres indexes NULLs. It can use

Re: [SQL] ORDER BY and NULLs

2004-10-30 Thread Murphy Pope
>> I just dug out the PostgreSQL book again because I thought I might've >> garbled it: >> >> Quote: "PostgreSQL will not index NULL values. Because an index will >> never include NULL values, it cannot be used to satisfy the ORDER BY >> clause of a query that returns all rows in a table." > > Yo

Re: [SQL] ORDER BY and NULLs

2004-10-30 Thread Murphy Pope
> You should just cross out that whole section. It's just flatly wrong. > > I had always assumed it was just people bringing assumptions over from > Oracle where it is true. Perhaps this book is to blame for some of the > confusion. Which book is it? > > Postgres indexes NULLs. It can use them fo

Re: [SQL] ORDER BY and NULLs

2004-09-20 Thread T E Schmitz
Hello, Greg Stark wrote: Tom Lane <[EMAIL PROTECTED]> writes: The paragraph continues: "If the SELECT command included the clause WHERE phone NOT NULL, PostgreSQL could use the index to satisfy the ORDER BY clause. An index that covers optional (NOT NULL) columns will not be used to speed table

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread Greg Stark
Tom Lane <[EMAIL PROTECTED]> writes: > > The paragraph continues: > > "If the SELECT command included the clause WHERE phone NOT NULL, > > PostgreSQL could use the index to satisfy the ORDER BY clause. > > An index that covers optional (NOT NULL) columns will not be used to > > speed table join

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread T E Schmitz
Hello Tom, Tom Lane wrote: T E Schmitz <[EMAIL PROTECTED]> writes: Greg Stark wrote: Which book is it? PostgreSQL by Korry Douglas + Susan Douglas, ISBN 0-7357-1257-3; Feb 2003 Hmm, I've heard of that book but never seen it. The authors are not participants in the PG community --- AFAICT neither

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread Tom Lane
T E Schmitz <[EMAIL PROTECTED]> writes: > Greg Stark wrote: >> Which book is it? > PostgreSQL by Korry Douglas + Susan Douglas, ISBN 0-7357-1257-3; Feb 2003 Hmm, I've heard of that book but never seen it. The authors are not participants in the PG community --- AFAICT neither of them have ever p

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread T E Schmitz
Hello Greg, Greg Stark wrote: T E Schmitz <[EMAIL PROTECTED]> writes: Quote: "PostgreSQL will not index NULL values. Because an index will never include NULL values, it cannot be used to satisfy the ORDER BY clause of a query that returns all rows in a table." You should just cross out that whole

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread Tom Lane
T E Schmitz <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Whatever you were reading had it pretty badly garbled :-( > I just dug out the PostgreSQL book again because I thought I might've > garbled it: > Quote: "PostgreSQL will not index NULL values. Because an index will > never include NUL

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread Greg Stark
T E Schmitz <[EMAIL PROTECTED]> writes: > I just dug out the PostgreSQL book again because I thought I might've garbled > it: > > Quote: "PostgreSQL will not index NULL values. Because an index will never > include NULL values, it cannot be used to satisfy the ORDER BY clause of a > query that r

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread T E Schmitz
Hello Tom, Tom Lane wrote: T E Schmitz <[EMAIL PROTECTED]> writes: I read somewhere that an Index is not going to improve the performance of an ORDER BY if the sort column contains NULLs because NULLs aren't indexed? Whatever you were reading had it pretty badly garbled :-( I just dug out the Pos

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread Tom Lane
T E Schmitz <[EMAIL PROTECTED]> writes: > You must've been reading my mind. I was just wondering what to do about > indexing on that particular table. I read somewhere that an Index is not > going to improve the performance of an ORDER BY if the sort column > contains NULLs because NULLs aren't

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread T E Schmitz
T E Schmitz Sent: Sunday, September 19, 2004 10:58 AM To: [EMAIL PROTECTED] Subject: [SQL] ORDER BY and NULLs Hello, I am using PostgreSQL 7.4.2 and as I understand NULL values always sort last. However, I have a table from which select using two numerical sort keys "FROM" and "TO&qu

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread Jean-Luc Lachance
EFORE the first most value that your TO result can return. Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of T E Schmitz Sent: Sunda

Re: [SQL] ORDER BY and NULLs

2004-09-19 Thread terry
gt; Sent: Sunday, September 19, 2004 10:58 AM > To: [EMAIL PROTECTED] > Subject: [SQL] ORDER BY and NULLs > > > Hello, > > I am using PostgreSQL 7.4.2 and as I understand NULL values > always sort > last. > > However, I have a table from which select using two nume

[SQL] ORDER BY and NULLs

2004-09-19 Thread T E Schmitz
Hello, I am using PostgreSQL 7.4.2 and as I understand NULL values always sort last. However, I have a table from which select using two numerical sort keys "FROM" and "TO". "TO" might be NULL and I would like to display those rows first (without sorting the column in descending order). Is the