Inserting a select statement result into another table

Started by Andrew Selleover 25 years ago13 messages

aselle@upl.cs.wisc.edu

over 25 years ago

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

Chris Bitmead

chrisb@nimrod.itg.telstra.com.au

over 25 years ago

In reply to: Andrew Selle (#1)

Re: Inserting a select statement result into another table

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:

Show quoted text

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

Stephan Szabo

sszabo@megazone23.bigpanda.com

over 25 years ago

In reply to: Chris Bitmead (#2)

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:

Show quoted text

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

ben@openforce.net

over 25 years ago

In reply to: Andrew Selle (#1)

Re: Inserting a select statement result into another table

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

Philip Warner

pjw@rhyme.com.au

over 25 years ago

In reply to: Chris Bitmead (#2)

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

Bruce Momjian

pgman@candle.pha.pa.us

over 25 years ago

In reply to: Stephan Szabo (#3)

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

Denis Perchine

dyp@perchine.com

over 25 years ago

In reply to: Bruce Momjian (#6)

Re: Inserting a select statement result into another table

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

kovacsz

zoli@pc10.radnoti-szeged.sulinet.hu

almost 25 years ago

In reply to: Philip Warner (#5)

pg_dump writes SEQUENCEs twice with -a

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

Philip Warner

pjw@rhyme.com.au

almost 25 years ago

In reply to: kovacsz (#8)

Re: pg_dump writes SEQUENCEs twice with -a

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

#10

Oliver Elphick

olly@lfix.co.uk

almost 25 years ago

In reply to: Philip Warner (#9)

Re: pg_dump writes SEQUENCEs twice with -a

kovacsz wrote:

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.

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 the Father, and of the Son, and of the
Holy Ghost; Teaching them to observe all things
whatsoever I have commanded you; and, lo, I am with
you alway, even unto the end of the world. Amen."
Matthew 28:19,20

Import Notes

Reply to msg id not found: Messagefromkovacszzoli@pc10.radnoti-szeged.sulinet.huofTue06Mar2001160736+0100.Pine.LNX.4.10.10103061542060.26655-100000@tir.tir | Resolved by subject fallback

#11

Philip Warner

pjw@rhyme.com.au

almost 25 years ago

In reply to: Oliver Elphick (#10)

Re: pg_dump writes SEQUENCEs twice with -a

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.

#12

Oliver Elphick

olly@lfix.co.uk

almost 25 years ago

In reply to: Philip Warner (#11)

Re: pg_dump writes SEQUENCEs twice with -a

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.

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

Import Notes

Reply to msg id not found: MessagefromPhilipWarnerpjw@rhyme.com.auofThu08Mar2001101004+1100.3.0.5.32.20010308101004.0319dbc0@mail.rhyme.com.au | Resolved by subject fallback

#13

Kovacs Zoltan

kovzol@server.math.u-szeged.hu

almost 25 years ago

In reply to: Philip Warner (#11)

Re: pg_dump writes SEQUENCEs twice with -a

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