Re: AW: [HACKERS] Inserting a select statement result into another table
[ Charset ISO-8859-1 unsupported, converting... ] > > > > > He does ask a legitimate question though. If you are > > going to have a > > > > LIMIT feature (which of course is not pure SQL), there > > seems no reason > > > > you shouldn't be able to insert the result into a table. > > > > > > > > > > This is an interesting idea. We don't allow ORDER BY in > > INSERT INTO ... > > SELECT because it doesn't make any sense, but it does make sense if > > LIMIT is used: > > An "order by" also makes sense if you want to create a presorted table > for faster access. I don't see why we should disallow it. Like CLUSTER. I see. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
AW: [HACKERS] Inserting a select statement result into another table
> > > He does ask a legitimate question though. If you are > going to have a > > > LIMIT feature (which of course is not pure SQL), there > seems no reason > > > you shouldn't be able to insert the result into a table. > > > > > > This is an interesting idea. We don't allow ORDER BY in > INSERT INTO ... > SELECT because it doesn't make any sense, but it does make sense if > LIMIT is used: An "order by" also makes sense if you want to create a presorted table for faster access. I don't see why we should disallow it. Andreas
Re: [HACKERS] Inserting a select statement result into another table
Hello, just my $0.02... If I do insert into x select * from y limit 10; I will get all of rows in x inserted, not just 10... I already wrote about this... But did not get any useful reply. > This is an interesting idea. We don't allow ORDER BY in INSERT INTO ... > SELECT because it doesn't make any sense, but it does make sense if > LIMIT is used: > > ctest=> create table x (Y oid); > CREATE > test=> insert into x > test-> select oid from pg_class order by oid limit 1; > ERROR: LIMIT is not supported in subselects > > Added to TODO: > > Allow ORDER BY...LIMIT in INSERT INTO ... SELECT -- Sincerely Yours, Denis Perchine -- E-Mail: [EMAIL PROTECTED] HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 --
Re: [HACKERS] Inserting a select statement result into another table
> > Well, If I'm reading the spec correctly, > INSERT INTO references a query expression > which doesn't include ORDER BY as an option, so this > is even less SQL since we're actually not just changing > it to allow our non-standard bit, but we're changing > a piece that is explicitly not allowed in the spec. > > That being said, I also think it's probably a useful extension > given the LIMIT clause. > > On Fri, 18 Aug 2000, Chris Bitmead wrote: > > > > > He does ask a legitimate question though. If you are going to have a > > LIMIT feature (which of course is not pure SQL), there seems no reason > > you shouldn't be able to insert the result into a table. > > This is an interesting idea. We don't allow ORDER BY in INSERT INTO ... SELECT because it doesn't make any sense, but it does make sense if LIMIT is used: ctest=> create table x (Y oid); CREATE test=> insert into x test-> select oid from pg_class order by oid limit 1; ERROR: LIMIT is not supported in subselects Added to TODO: Allow ORDER BY...LIMIT in INSERT INTO ... SELECT -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
