Re: 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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026