[SQL] no ORDER BY in subselects?
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, buyer.login AS buyer_login, num_bid(a.id), seller.login AS seller_login, t.name AS auction_type FROM auction* a, person seller, person buyer, auction_type t WHERE a.id = 84 AND seller.id = a.person_id AND COALESCE(a.type,1) = t.id AND buyer.id = 2; ERROR: parser: parse error at or near "order" Aren't ORDER BY clauses allowed in subselects? -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Black holes are where God divided by zero.
[SQL] ERROR: replace_vars_with_subplan_refs (!?)
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 AS seller_locale, buyer.login AS buyer_login, num_bid(a.id), seller.login AS seller_login, t.name AS auction_type FROM auction* a, person seller, person buyer, auction_type t WHERE a.id = 84 AND seller.id = a.person_id AND COALESCE(a.type,1) = t.id AND buyer.id = 2; ERROR: replace_vars_with_subplan_refs: variable not in subplan target list Thanks, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Marijuana is nature's way of saying, "Hi!".
Re: [SQL] no ORDER BY in subselects?
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| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [SQL] no ORDER BY in subselects?
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 (!?)
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 table declarations, so I can try the example without a lot of guessing? regards, tom lane
Re: [SQL] [GENERAL] Foreign Keys Help Delete!
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 "routes" table > 2. create foo.new.com in "hosts" table > 3. delete foo.old.com in "routes" table > 4. add foo.new.com into "routes" table > 5. try to delete foo.old.com and it complains! Tom - not to interrupt your coding :-) this sounds like a bug. Any thoughts? > >2. a. Drop the Foriegn Key constraint > > b. Update both the routes and hosts tables > > c. Re-establish the foriegn key constraint > > This is the part that I'm fuzzy on. I've tried this before > with complete DB corruption resulting. I had to dump each table > one by one, edit my schema with vi, create new DB, import tables > one by onevery painful! This also sounds like a problem. One should be able to drop a constraint, the re-create the restraint and check existing records against it. You can do this in MSSQL and Oracle. > PPS. As I replied to Stephan, I'm contracting at a company and I > don't have access to e-mail. Taking a schema home is NOT OK. > I already asked the manager if I could GPL my DNS-DB implementation. > As you might expect, the non-technical manager, didn't know what > GPL was, and he was NOT going to allow my work to be released to > publicAnd of course, higher ups in company may decide that > my solution breaks the "don't build if you can buy" policy, in which > case all of my work is for naught! ARGH!! Well, if they don't use it, you can easily re-create your work at home and GPL it. It also depends on the contract you signed ... -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] ERROR: replace_vars_with_subplan_refs (!?)
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 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 table >> declarations, so I can try the example without a lot of guessing? > Please find attached the full dump. OK, this is indeed fixed in current sources. I think you are running into the same problem you reported in June, namely that subselects appearing in the targetlist of an Append plan are misprocessed in 7.0.*. Append is mainly used for handling inherited queries, so it's the combination of inheritance and subselect in targetlist that's needed to trigger the bug. This probably also explains the other report you filed this morning; once the subselect is messed up, all sorts of things start to go wrong :-( I think you could back-patch the fix into 7.0.* just by dropping rev 1.64 of setrefs.c into the 7.0 source tree --- see http://www.postgresql.org/cgi/cvswebtest.cgi/pgsql/src/backend/optimizer/plan/setrefs.c for that version. regards, tom lane
[Fwd: Re: [SQL] no ORDER BY in subselects?]
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 fact, but, no, they're not. H ... can't say as I've ever seen an ORDER BY in a subselect before. Why would you want one? And if you do want one, Louis-David, you can always use a temporary table as previously described. -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
[SQL] sql query not using indexes
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 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 On a machine running version 6.5 both queries results index scan. this results that the version 6.5 is faster than version 7.0.2 on this kind of query. Any explanation??? Please???
Re: [SQL] [GENERAL] Foreign Keys Help Delete!
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 I tried.
> >
> > 1. foo.old.com exists in "hosts" table and "routes" table
> > 2. create foo.new.com in "hosts" table
> > 3. delete foo.old.com in "routes" table
> > 4. add foo.new.com into "routes" table
> > 5. try to delete foo.old.com and it complains!
>
> Tom - not to interrupt your coding :-) this sounds like a bug. Any
> thoughts?
Probably doesn't need to go all the way to Tom... :)
Hmm, on my 7.0.2 box,
sszabo=# create table hosts (fqdn varchar(30));
CREATE
sszabo=# create table routes (fqdn varchar(30),foreign key(fqdn)
references hosts(fqdn));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
sszabo=# insert into hosts values ('foo.old.com');
INSERT 181159 1
sszabo=# insert into routes values ('foo.old.com');
INSERT 181160 1
sszabo=# begin;
BEGIN
sszabo=# insert into hosts values ('foo.new.com');
INSERT 181161 1
sszabo=# delete from routes where fqdn='foo.old.com';
DELETE 1
sszabo=# insert into routes values ('foo.new.com');
INSERT 181162 1
sszabo=# delete from hosts where fqdn='foo.old.com';
DELETE 1
sszabo=# end;
COMMIT
--
To original complainant:
Since you won't be able to post the trigger information either probably,
can you check pg_trigger to make sure there are no dangling constraint
triggers?
You should have three rows that look like:
181144 | RI_ConstraintTrigger_181153 | 1644 | 21 | t | t
| |181120 | f| f | 6 |
| \000routes\000hosts\000UNSPECIFIED\000fqdn\000fqdn\000
181120 | RI_ConstraintTrigger_181155 | 1654 | 9 | t | t
| |181144 | f| f | 6 |
| \000routes\000hosts\000UNSPECIFIED\000fqdn\000fqdn\000
181120 | RI_ConstraintTrigger_181157 | 1655 | 17 | t | t
| |181144 | f| f | 6 |
| \000routes\000hosts\000UNSPECIFIED\000fqdn\000fqdn\000
Except that the relation oids are likely to be different (important ones
are the tgrelid and tgconstrrelid). The function oids (1644, 1654, 1655)
should be the same I believe.
> > >2. a. Drop the Foriegn Key constraint
> > > b. Update both the routes and hosts tables
> > > c. Re-establish the foriegn key constraint
> >
> > This is the part that I'm fuzzy on. I've tried this before
> > with complete DB corruption resulting. I had to dump each table
> > one by one, edit my schema with vi, create new DB, import tables
> > one by onevery painful!
>
> This also sounds like a problem. One should be able to drop a
> constraint, the re-create the restraint and check existing records
> against it. You can do this in MSSQL and Oracle.
Well, we don't have ALTER TABLE ... DROP CONSTRAINT right now. Dropping
the constraint requires removing the triggers manually. We can do an
ADD CONSTRAINT which will check the data, but not the corresponding DROP.
Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]
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 not. > > H ... can't say as I've ever seen an ORDER BY in a subselect before. > Why would you want one? If only to do a "LIMIT 1" on it. But this is probably considered very ugly to exprienced DB users (I'm only recently self-taught on that subject). > And if you do want one, Louis-David, you can always use a temporary > table as previously described. I found another workaround to the problem, finally. Thanks -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Your mouse has moved. Windows must be restarted for the change to take effect. Reboot now?
Re: [SQL] sql query not using indexes
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 > > > On a machine running version 6.5 both queries results index scan. > > this results that the version 6.5 is faster than version 7.0.2 on this > kind of > query. > > > Any explanation??? Have you done a vacuum analyze on the table? Also, what does the row count for the second query look like? It's probably deciding that there are too many rows that will match login >'AAA' for index scan to be cost effective. So, actually, also, what does select count(*) from teste where login>'AAA" give you on the 7.0.2 box.
Re: [SQL] sql query not using indexes
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 PROTECTED]> Sent: Wednesday, September 20, 2000 11:23 AM Subject: Re: [SQL] sql query not using indexes > 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 > > > > > > On a machine running version 6.5 both queries results index scan. > > > > this results that the version 6.5 is faster than version 7.0.2 on this > > kind of > > query. > > > > > > Any explanation??? > > Have you done a vacuum analyze on the table? Also, what does the row > count for the second query look like? It's probably deciding that > there are too many rows that will match login >'AAA' for index scan > to be cost effective. So, actually, also, what does > select count(*) from teste where login>'AAA" give you on the 7.0.2 box. > > >
Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]
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 clauses allowed in subselects? >> > >> >> It is a very very sad fact, but, no, they're not. > >H ... can't say as I've ever seen an ORDER BY in a subselect before. >Why would you want one? > The main reason I use them is to find the 'next' or 'previous' record in a list (eg. next date, next ID). eg. select , (select ID from table where id > this.id order by id asc limit 1) as next_id ... OR select , (select Start_Date from table where Start_Date > this.Start_Date Order By Start_Date asc limit 1) as End_Date >And if you do want one, Louis-David, you can always use a temporary >table as previously described. It is A LOT less clean. The fact that Dec RDB, Oracle and SQL/Server all allow it probably means that there is a reasonable user base out there who think it's a good idea. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [SQL] [GENERAL] Foreign Keys Help Delete!
>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 "routes" table >> 2. create foo.new.com in "hosts" table >> 3. delete foo.old.com in "routes" table >> 4. add foo.new.com into "routes" table >> 5. try to delete foo.old.com and it complains! > >Tom - not to interrupt your coding :-) this sounds like a bug. Any >thoughts? No, Tom's not needed. I double checked things again today, and was able to delete the problem records today I'll blame it on gremlins. ;-) I suppose that I got lost in the data and the gremlins must have cleaned it up while I slept last night;-) Sorry to get any feathers ruffled Still, I would like an easy way to drop and recreate foreign keys and from what I see, it will appear that there is not a "safe" way to do this yet. > >> >2. a. Drop the Foriegn Key constraint >> > b. Update both the routes and hosts tables >> > c. Re-establish the foriegn key constraint >> >> This is the part that I'm fuzzy on. I've tried this before >> with complete DB corruption resulting. I had to dump each table >> one by one, edit my schema with vi, create new DB, import tables >> one by onevery painful! > >This also sounds like a problem. One should be able to drop a >constraint, the re-create the restraint and check existing records >against it. You can do this in MSSQL and Oracle. > >> PPS. As I replied to Stephan, I'm contracting at a company and I >> don't have access to e-mail. Taking a schema home is NOT OK. >> I already asked the manager if I could GPL my DNS-DB implementation. >> As you might expect, the non-technical manager, didn't know what >> GPL was, and he was NOT going to allow my work to be released to >> publicAnd of course, higher ups in company may decide that >> my solution breaks the "don't build if you can buy" policy, in which >> case all of my work is for naught! ARGH!! > >Well, if they don't use it, you can easily re-create your work at home >and GPL it. It also depends on the contract you signed ... Yes, well, gremlin infested work is not good to distribute onto the net...;-) Time will tell > > -Josh >-- >__AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 436-9166 > for law firms, small businesses fax 436-0137 >and non-profit organizations. pager 338-4078 > San Francisco
[SQL] How do I run a search on array
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 do a search on it using rows and columns (or by any other
way)?
thanks,
Indraneel
/.
# Indraneel Majumdar ¡ E-mail: [EMAIL PROTECTED] #
# Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076 #
`/
Re: [SQL] How do I run a search on array
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 contrib directory.
They include element is member of array and other such functions and
will probably do what you need.
> I would also like to know that if I have an array as a large object, is it
> possible to do a search on it using rows and columns (or by any other
> way)?
You're putting array style data into a large object with the lo_
functions? Probably not in a meaningful way, no (although I'd guess that
toast might work for that kind of application when 7.1 comes out.)
