Re: Inserting a select statement result into another table
От | Chris Bitmead |
---|---|
Тема | Re: Inserting a select statement result into another table |
Дата | |
Msg-id | 399C7689.2DDDAD1D@nimrod.itg.telecom.com.au обсуждение исходный текст |
Ответ на | Inserting a select statement result into another table (Andrew Selle <aselle@upl.cs.wisc.edu>) |
Ответы |
Re: Inserting a select statement result into another table
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Re: Inserting a select statement result into another table (Philip Warner <pjw@rhyme.com.au>) |
Список | pgsql-hackers |
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 > > );
В списке pgsql-hackers по дате отправления: