Обсуждение: Inserting a select statement result into another table
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 );
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 > );
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 > > );
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.
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 |/
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
> > 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
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 ----------------------------------
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 |/
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
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
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 |/
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
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