Обсуждение: Inserting a select statement result into another table

Поиск
Список
Период
Сортировка

Inserting a select statement result into another table

От
Andrew Selle
Дата:
Alright.  My situation is this.  I have a list of things that need to be done
in a table called tasks.  I have a list of users who will complete these tasks.
I want these users to be able to come in and "claim" the top 2 most recent tasks
that have been added.  These tasks then get stored in a table called todolist
which stores who claimed the task, the taskid, and when the task was claimed.
For each time someone wants to claim some number of tasks, I want to do something
like

INSERT INTO todolist SELECT taskid,'1',now() FROM tasks WHERE done='f' ORDER BY submit DESC LIMIT 2;

Unfortunately, when I do this I get 
ERROR:  ORDER BY is not allowed in INSERT/SELECT

The select works fine

aselle=> select taskid,'1',now() FROM tasks WHERE done='f' ORDER BY submit DESC LIMIT 2;taskid | ?column? |
now          --------+----------+------------------------      4 | 1        | 2000-08-17 12:56:00-05      3 | 1
|2000-08-17 12:56:00-05
 
(2 rows)

It seems to me, this is something I should do.  I was wondering if there
is any reason why I can't do this?  I've thought of a couple of workarounds
but they don't seem to be very clean:

1. Read the results of the select at the application level and reinsert into the   todolist table

2. Add two fields to the task table that keep track of userid and claimed.  This unfortunately clutters the main task
table,and it loses the ability   to assign multiple people to the same task. It also requires looping at the
applicationlevel I think
 

3. use a temporary table with a SELECT INTO statement and then copy the contents  of the temporary table into the table
Iwant it in todolist
 

Below are the table creation statements for this sample...

-Andy


CREATE TABLE tasks (taskid    int4,title    varchar(64),descr    text,submit    datetime,done    boolean
);

CREATE TABLE users (userid    int4,name    varchar(32)
);

CREATE TABLE todolist (taskid    int4,userid    int4,claimed    datetime
);





Re: Inserting a select statement result into another table

От
Ben Adida
Дата:
The reason this isn't working is because there is no concept of an inherent order of rows
in SQL. The only time things are ordered are when you explicitly request them to be,
according to a particular field. Thus, inserting a bunch of rows is exactly the same no
matter what order you insert them in, and you shouldn't assume anything about the
underlying mechanism of insertion and oids in your application.

What is the purpose you're trying to accomplish with this order by? No matter what, all the
rows where done='f' will be inserted, and you will not be left with any indication of that
order once the rows are in the todolist table.

-Ben

Andrew Selle wrote:

> Alright.  My situation is this.  I have a list of things that need to be done
> in a table called tasks.  I have a list of users who will complete these tasks.
> I want these users to be able to come in and "claim" the top 2 most recent tasks
> that have been added.  These tasks then get stored in a table called todolist
> which stores who claimed the task, the taskid, and when the task was claimed.
> For each time someone wants to claim some number of tasks, I want to do something
> like
>
> INSERT INTO todolist
>         SELECT taskid,'1',now()
>         FROM tasks
>         WHERE done='f'
>         ORDER BY submit DESC
>         LIMIT 2;
>
> Unfortunately, when I do this I get
> ERROR:  ORDER BY is not allowed in INSERT/SELECT
>
> The select works fine
>
> aselle=> select taskid,'1',now() FROM tasks WHERE done='f' ORDER BY submit DESC LIMIT 2;
>  taskid | ?column? |          now
>  --------+----------+------------------------
>        4 | 1        | 2000-08-17 12:56:00-05
>        3 | 1        | 2000-08-17 12:56:00-05
> (2 rows)
>
> It seems to me, this is something I should do.  I was wondering if there
> is any reason why I can't do this?  I've thought of a couple of workarounds
> but they don't seem to be very clean:
>
> 1. Read the results of the select at the application level and reinsert into the
>     todolist table
>
> 2. Add two fields to the task table that keep track of userid and claimed.
>    This unfortunately clutters the main task table, and it loses the ability
>     to assign multiple people to the same task. It also requires looping at the
>     application level I think
>
> 3. use a temporary table with a SELECT INTO statement and then copy the contents
>    of the temporary table into the table I want it in todolist
>
> Below are the table creation statements for this sample...
>
> -Andy
>
> CREATE TABLE tasks (
>         taskid  int4,
>         title   varchar(64),
>         descr   text,
>         submit  datetime,
>         done    boolean
> );
>
> CREATE TABLE users (
>         userid  int4,
>         name    varchar(32)
> );
>
> CREATE TABLE todolist (
>         taskid  int4,
>         userid  int4,
>         claimed datetime
> );



Re: Inserting a select statement result into another table

От
Chris Bitmead
Дата:
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.

Ben Adida wrote:
> 
> The reason this isn't working is because there is no concept of an inherent order of rows
> in SQL. The only time things are ordered are when you explicitly request them to be,
> according to a particular field. Thus, inserting a bunch of rows is exactly the same no
> matter what order you insert them in, and you shouldn't assume anything about the
> underlying mechanism of insertion and oids in your application.
> 
> What is the purpose you're trying to accomplish with this order by? No matter what, all the
> rows where done='f' will be inserted, and you will not be left with any indication of that
> order once the rows are in the todolist table.
> 
> -Ben
> 
> Andrew Selle wrote:
> 
> > Alright.  My situation is this.  I have a list of things that need to be done
> > in a table called tasks.  I have a list of users who will complete these tasks.
> > I want these users to be able to come in and "claim" the top 2 most recent tasks
> > that have been added.  These tasks then get stored in a table called todolist
> > which stores who claimed the task, the taskid, and when the task was claimed.
> > For each time someone wants to claim some number of tasks, I want to do something
> > like
> >
> > INSERT INTO todolist
> >         SELECT taskid,'1',now()
> >         FROM tasks
> >         WHERE done='f'
> >         ORDER BY submit DESC
> >         LIMIT 2;
> >
> > Unfortunately, when I do this I get
> > ERROR:  ORDER BY is not allowed in INSERT/SELECT
> >
> > The select works fine
> >
> > aselle=> select taskid,'1',now() FROM tasks WHERE done='f' ORDER BY submit DESC LIMIT 2;
> >  taskid | ?column? |          now
> >  --------+----------+------------------------
> >        4 | 1        | 2000-08-17 12:56:00-05
> >        3 | 1        | 2000-08-17 12:56:00-05
> > (2 rows)
> >
> > It seems to me, this is something I should do.  I was wondering if there
> > is any reason why I can't do this?  I've thought of a couple of workarounds
> > but they don't seem to be very clean:
> >
> > 1. Read the results of the select at the application level and reinsert into the
> >     todolist table
> >
> > 2. Add two fields to the task table that keep track of userid and claimed.
> >    This unfortunately clutters the main task table, and it loses the ability
> >     to assign multiple people to the same task. It also requires looping at the
> >     application level I think
> >
> > 3. use a temporary table with a SELECT INTO statement and then copy the contents
> >    of the temporary table into the table I want it in todolist
> >
> > Below are the table creation statements for this sample...
> >
> > -Andy
> >
> > CREATE TABLE tasks (
> >         taskid  int4,
> >         title   varchar(64),
> >         descr   text,
> >         submit  datetime,
> >         done    boolean
> > );
> >
> > CREATE TABLE users (
> >         userid  int4,
> >         name    varchar(32)
> > );
> >
> > CREATE TABLE todolist (
> >         taskid  int4,
> >         userid  int4,
> >         claimed datetime
> > );


Re: Inserting a select statement result into another table

От
Stephan Szabo
Дата:
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.



Re: Inserting a select statement result into another table

От
Philip Warner
Дата:
At 09:34 18/08/00 +1000, 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 feature is supported by two commercial DBs: Dec/RDB and SQL/Server. I
have no idea if Oracle supports it, but it is such a *useful* feature that
I would be very surprised if it didn't.


>Ben Adida wrote:
>> 
>> What is the purpose you're trying to accomplish with this order by? No
matter what, all the
>> rows where done='f' will be inserted, and you will not be left with any
indication of that
>> order once the rows are in the todolist table.

I don't know what his *purpose* was, but the query should only insert the
first two rows from the select bacause of the limit).

>> Andrew Selle wrote:
>> 
>> > Alright.  My situation is this.  I have a list of things that need to
be done
>> > in a table called tasks.  I have a list of users who will complete
these tasks.
>> > I want these users to be able to come in and "claim" the top 2 most
recent tasks
>> > that have been added.  These tasks then get stored in a table called
todolist
>> > which stores who claimed the task, the taskid, and when the task was
claimed.
>> > For each time someone wants to claim some number of tasks, I want to
do something
>> > like
>> >
>> > INSERT INTO todolist
>> >         SELECT taskid,'1',now()
>> >         FROM tasks
>> >         WHERE done='f'
>> >         ORDER BY submit DESC
>> >         LIMIT 2;

----------------------------------------------------------------
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: Inserting a select statement result into another table

От
Ben Adida
Дата:
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.

Yes, that's true, I had missed that the first time around.

-Ben



Re: Inserting a select statement result into another table

От
Bruce Momjian
Дата:
> 
> 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);CREATEtest=> insert into x test-> select oid from pg_class order by oid limit 1;ERROR:
LIMITis not supported in subselects
 

Added to TODO:
Allow ORDER BY...LIMIT in INSERT INTO ... SELECT

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


Re: Inserting a select statement result into another table

От
Denis Perchine
Дата:
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: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------


Re: pg_dump writes SEQUENCEs twice with -a

От
Philip Warner
Дата:
At 16:07 6/03/01 +0100, kovacsz wrote:
>The problem hasn't
>disappeared yet. In 7.1beta4...

As per an earlier message today, the problem is fixed in CVS


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


pg_dump writes SEQUENCEs twice with -a

От
kovacsz
Дата:
I reported this problem about 3 weeks ago or even more. The problem hasn't
disappeared yet. In 7.1beta4 if I use pg_dump with -a switch together, I
get each CREATE SEQUENCE twice. I suspected if this is an installation
problem at my place but now I think it maybe isn't.

You answered that noone experienced anything like this. Here I get this
behaviour with the most simple table as well.

Could you please help? TIA, Zoltan

------------------------------------------------------------------------
Zoltan Kovacs
system designing leader at Trend Ltd, J\'aszber\'eny
assistant teacher in mathematics at Bolyai Institute, Szeged

http://www.trendkft.hu
http://www.math.u-szeged.hu/~kovzol




Re: pg_dump writes SEQUENCEs twice with -a

От
"Oliver Elphick"
Дата:
kovacsz wrote: >I reported this problem about 3 weeks ago or even more. The problem hasn't >disappeared yet. In
7.1beta4if I use pg_dump with -a switch together, I >get each CREATE SEQUENCE twice. I suspected if this is an
installation>problem at my place but now I think it maybe isn't. > >You answered that noone experienced anything like
this.Here I get this >behaviour with the most simple table as well.
 

I get the same error using 7.1beta4.  See this example for a 1 table database:

olly@linda$ pg_dump -a junk
--
-- Selected TOC Entries:
--
\connect - olly
--
-- TOC Entry ID 1 (OID 2091620)
--
-- Name: "basket_id_seq" Type: SEQUENCE Owner: olly
--

CREATE SEQUENCE "basket_id_seq" start 1 increment 1 maxvalue 2147483647 
minvalue 1  cache 1 ;

--
-- TOC Entry ID 3 (OID 2091620)
--
-- Name: "basket_id_seq" Type: SEQUENCE Owner: olly
--

CREATE SEQUENCE "basket_id_seq" start 1 increment 1 maxvalue 2147483647 
minvalue 1  cache 1 ;

--
-- Data for TOC Entry ID 5 (OID 2091639) TABLE DATA basket
--

-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'basket';
COPY "basket"  FROM stdin;
1    2001-03-04 19:59:58+00
\.
-- Enable triggers
BEGIN TRANSACTION;
CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, 
"pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* 'basket'  GROUP 
BY 1;
UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP 
WHERE "pg_class"."relname" = TMP."tmp_relname";
DROP TABLE "tr";
COMMIT TRANSACTION;

--
-- TOC Entry ID 2 (OID 2091620)
--
-- Name: "basket_id_seq" Type: SEQUENCE SET Owner: 
--

SELECT setval ('"basket_id_seq"', 1, 't');

--
-- TOC Entry ID 4 (OID 2091620)
--
-- Name: "basket_id_seq" Type: SEQUENCE SET Owner: 
--

SELECT setval ('"basket_id_seq"', 1, 't');

olly@linda$ 

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Go ye therefore, and teach all nations, baptizing them     in the name of
theFather, and of the Son, and of the      Holy Ghost; Teaching them to observe all things       whatsoever I have
commandedyou; and, lo, I am with      you alway, even unto the end of the world. Amen."                         Matthew
28:19,20
 




Re: pg_dump writes SEQUENCEs twice with -a

От
Philip Warner
Дата:
At 20:48 7/03/01 +0000, Oliver Elphick wrote:
>kovacsz wrote:
>  >
>  >You answered that noone experienced anything like this. Here I get this
>  >behaviour with the most simple table as well.
>

Is there a problem with the lists? I reveived Zoltan's message twice, and
now this one that seems to indicate my earlier reply has not been seen.

FWIW, this is fixed in CVS.


----------------------------------------------------------------
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: pg_dump writes SEQUENCEs twice with -a

От
"Oliver Elphick"
Дата:
Philip Warner wrote: >At 20:48 7/03/01 +0000, Oliver Elphick wrote: >>kovacsz wrote: >>  > >>  >You answered that noone
experiencedanything like this. Here I get this >>  >behaviour with the most simple table as well. >> > >Is there a
problemwith the lists? I reveived Zoltan's message twice, and >now this one that seems to indicate my earlier reply has
notbeen seen.
 

No I hadn't (and still haven't) seen your earlier reply.


-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Go ye therefore, and teach all nations, baptizing them     in the name of
theFather, and of the Son, and of the      Holy Ghost; Teaching them to observe all things       whatsoever I have
commandedyou; and, lo, I am with      you alway, even unto the end of the world. Amen."                         Matthew
28:19,20
 




Re: pg_dump writes SEQUENCEs twice with -a

От
Kovacs Zoltan
Дата:
On Thu, 8 Mar 2001, Philip Warner wrote:

> At 20:48 7/03/01 +0000, Oliver Elphick wrote:
> >kovacsz wrote:
> >  >
> >  >You answered that noone experienced anything like this. Here I get this
> >  >behaviour with the most simple table as well.
> >
> 
> Is there a problem with the lists? I reveived Zoltan's message twice, and
> now this one that seems to indicate my earlier reply has not been seen.
> 
> FWIW, this is fixed in CVS.
Thank you, I checked the CVS (and I downloaded the new sources and tried 
to compile -- without success, I should download the whole stuff IMHO,
e.g. postgres_fe.h is quite new to 7.1beta4 and the old sources may be
incompatible with the new ones).

Zoltan