
> > > 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

Fri, 13 Oct 2000 09:53:20 +0200
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: Inserting a select statement result into another ta
	ble
[ 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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 13 Oct 2000 05:07:22 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: AW: Inserting a select statement result into another ta
 ble

With how we do things right now, does it actually gain us anything
to have a presorted table?  Do we know not to do a seek on an index scan
if we're already at the right location in the heap file?  We can't assume
the table is sorted (unless it hasn't been modified), so it's not like we
can sequence scan and stop when the bounds are met.  If we don't do the
seek though, this could definately be good for mostly static data since
that might allow us to mostly not do seeks on normal conditions.

On Fri, 13 Oct 2000, Zeugswetter Andreas SB 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:
> 
> 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
> 


Fri, 13 Oct 2000 09:43:42 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: AW: Inserting a select statement result into another
 ta ble
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
>> 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.

In current sources:

regression=# insert into int4_tbl select * from int4_tbl order by f1;
INSERT 0 5
regression=# select * from int4_tbl;
     f1
-------------
           0
      123456
     -123456
  2147483647
 -2147483647
 -2147483647         <<= insertion starts here
     -123456
           0
      123456
  2147483647
(10 rows)

LIMIT won't work without some further code-rejiggering, but I think
it should be made to work eventually.

			regards, tom lane

Sat, 14 Oct 2000 00:22:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: Inserting a select statement result into another ta ble 