[SQL] no ORDER BY in subselects?

2000-09-20 Thread Louis-David Mitterrand
auction=# SELECT (select b.lot from bid b where b.auction_id = a.id and b.person_id = buyer.id order by b.price limit 1) as last_lot,auction_status(a.id) > 0 AS current, a.lot, a.person_id, next_price(a.id), seller.mail AS seller_mail, buyer.mail AS buyer_mail, seller.locale AS seller_locale, b

[SQL] ERROR: replace_vars_with_subplan_refs (!?)

2000-09-20 Thread Louis-David Mitterrand
How should I interpret that error? auction=# SELECT (select max(b.price) from bid b where b.auction_id = a.id and b.person_id = buyer.id) as last_lot,auction_status(a.id) > 0 AS current, a.lot, a.person_id, next_price(a.id), seller.mail AS seller_mail, buyer.mail AS buyer_mail, seller.locale A

Re: [SQL] no ORDER BY in subselects?

2000-09-20 Thread Philip Warner
At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: > >ERROR: parser: parse error at or near "order" > >Aren't ORDER BY clauses allowed in subselects? > It is a very very sad fact, but, no, they're not. Philip Warner

Re: [SQL] no ORDER BY in subselects?

2000-09-20 Thread Tom Lane
Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > Aren't ORDER BY clauses allowed in subselects? No. This is per SQL92... regards, tom lane

Re: [SQL] ERROR: replace_vars_with_subplan_refs (!?)

2000-09-20 Thread Tom Lane
Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > How should I interpret that error? > ERROR: replace_vars_with_subplan_refs: variable not in subplan target list Looks like a bug to me. I think this may already be fixed in current sources, but not sure. Could I trouble you for the relevant

Re: [SQL] [GENERAL] Foreign Keys Help Delete!

2000-09-20 Thread Josh Berkus
Timothy, Tom: > >1. a. Create new record with new key value in hosts table with the > >desired value > > b. Update the routes record to reference the new value > > c. Delete the old record in the hosts table > > > > Yes, that's what I tried. > > 1. foo.old.com exists in "hosts" table and "r

Re: [SQL] ERROR: replace_vars_with_subplan_refs (!?)

2000-09-20 Thread Tom Lane
Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > On Wed, Sep 20, 2000 at 10:43:59AM -0400, Tom Lane wrote: >> Louis-David Mitterrand <[EMAIL PROTECTED]> writes: How should I interpret that error? ERROR: replace_vars_with_subplan_refs: variable not in subplan target list >> >> Looks

[Fwd: Re: [SQL] no ORDER BY in subselects?]

2000-09-20 Thread Josh Berkus
Ooops, posted this to Phillip rather than the list, sorry Phillip ... Folks, Philip Warner wrote: > > At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: > > > >ERROR: parser: parse error at or near "order" > > > >Aren't ORDER BY clauses allowed in subselects? > > > > It is a very very sad

[SQL] sql query not using indexes

2000-09-20 Thread User Lenzi
Hello, I am using last version of postgresql, 7.0.2 on a FreeBSD or Linux box I create a table: create table teste ( login text, datein datetime); create index teste1 on teste (login); if I start a query: explain select * from teste where login = 'xxx' results: Index Scan using te

Re: [SQL] [GENERAL] Foreign Keys Help Delete!

2000-09-20 Thread Stephan Szabo
On Wed, 20 Sep 2000, Josh Berkus wrote: > Timothy, Tom: > > > >1. a. Create new record with new key value in hosts table with the > > >desired value > > > b. Update the routes record to reference the new value > > > c. Delete the old record in the hosts table > > > > > > > Yes, that's what

Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]

2000-09-20 Thread Louis-David Mitterrand
On Wed, Sep 20, 2000 at 09:20:25AM -0700, Josh Berkus wrote: > > At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: > > > > > >ERROR: parser: parse error at or near "order" > > > > > >Aren't ORDER BY clauses allowed in subselects? > > > > > > > It is a very very sad fact, but, no, they're no

Re: [SQL] sql query not using indexes

2000-09-20 Thread Stephan Szabo
On Wed, 20 Sep 2000, User Lenzi wrote: > if I start a query: > > explain select * from teste where login = 'xxx' > results: > Index Scan using teste1 on teste (cost=0.00..97.88 rows=25 ) > > > however a query: > explain select * from teste where login > 'AAA' > results: > Seq Scan on teste .

Re: [SQL] sql query not using indexes

2000-09-20 Thread Mitch Vincent
I'm curious, I know PG doesn't have support for 'full' text indexing so I'm wondering at what point does indexing become ineffective with text type fields? -Mitch - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "User Lenzi" <[EMAIL PROTECTED]> Cc: "pgsql-sql" <[EMAIL P

Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]

2000-09-20 Thread Philip Warner
At 09:20 20/09/00 -0700, Josh Berkus wrote: >Ooops, posted this to Phillip rather than the list, sorry Phillip ... > >Folks, > >Philip Warner wrote: >> >> At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: >> > >> >ERROR: parser: parse error at or near "order" >> > >> >Aren't ORDER BY clause

Re: [SQL] [GENERAL] Foreign Keys Help Delete!

2000-09-20 Thread Timothy Covell
>Timothy, Tom: > >> >1. a. Create new record with new key value in hosts table with the >> >desired value >> > b. Update the routes record to reference the new value >> > c. Delete the old record in the hosts table >> > >> >> Yes, that's what I tried. >> >> 1. foo.old.com exists in "hosts" t

[SQL] How do I run a search on array

2000-09-20 Thread Indraneel Majumdar
Hi, I've been trying to do: select col1 from table while array_col[1][1:4]='2'; how do I do this sort of thing? There seems to be no docs ;-( my array is {{"1","2","4","2"},{"3","2","5"},{"6","3","7","9"}} I would also like to know that if I have an array as a large object, is it possible to

Re: [SQL] How do I run a search on array

2000-09-20 Thread Stephan Szabo
On Thu, 21 Sep 2000, Indraneel Majumdar wrote: > select col1 from table while array_col[1][1:4]='2'; > > how do I do this sort of thing? There seems to be no docs ;-( > > my array is {{"1","2","4","2"},{"3","2","5"},{"6","3","7","9"}} You'll want to check out the array utilities in the contri