Re: [SQL] How to creat tables using record ID in for loop
Oh, I checked the function. There are some syntax errors. Right code listed below: CREATE OR REPLACE FUNCTION cr_tbls_by_staid() RETURNS INTEGER AS $$ DECLARE stid INTEGER; q TEXT; BEGIN FOR stid IN SELECT staid FROM mytest LOOP q:= 'CREATE TABLE "s' || staid || '" (staid varchar(50), val real, dt date);'; RAISE NOTICE 'query is: %', q; --for debug EXECUTE q; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; Also I forget to change first column definition in last CREATE TABLE query. Edited variant is, for sure: CREATE TABLE sta_descs (staid INTEGER, val real, dt date) WITHOUT OIDS; -- Best regards. Yuri. mailto: [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] DELETE with JOIN
I want to delete with a join condition. Google shows this is a common problem, but the only solutions are either for MySQL or they don't work in my situation because there are too many rows selected. I also have to make this work on several databases, includeing, grrr, Oracle, so non-standard MySQL "solutions" are doubly aggravating. DELETE FROM a WHERE a.b_id = b.id AND b.second_id = ? I have tried to do this before and always found a way, usually DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?) but I have too many rows, millions, in the IN crowd, ha ha, and it barfs. EXISTS is no better. At least Oracle barfs, and I haven't got to the others yet. I figured I would go with the worst offender first, and let me tell you, it is offensive. Dang I wish it were postgresql only! I could write a Dumb Little Test Program (tm) to read in all those IN ids and execute a zillion individual DELETE statements, but it would be slow as puke and this little delete is going to come up quite often now that I have a test program which needs to generate the junky data and play with it for several days before deleting it and starting over again. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On fim, 2008-08-07 at 09:14 -0700, [EMAIL PROTECTED] wrote: > I want to delete with a join condition. Google shows this is a common > problem, but the only solutions are either for MySQL or they don't > work in my situation because there are too many rows selected. I also > have to make this work on several databases, includeing, grrr, Oracle, > so non-standard MySQL "solutions" are doubly aggravating. > > DELETE FROM a WHERE a.b_id = b.id AND b.second_id = ? > > I have tried to do this before and always found a way, usually > > DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?) did you look at DELETE FROM table1 USING table2 WHERE ... ? gnari -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
At 10:05 AM 8/7/2008, [EMAIL PROTECTED] wrote: Date: Thu, 7 Aug 2008 09:14:49 -0700 From: [EMAIL PROTECTED] To: [email protected] Subject: DELETE with JOIN Message-ID: <[EMAIL PROTECTED]> I want to delete with a join condition. Google shows this is a common problem, but the only solutions are either for MySQL or they don't work in my situation because there are too many rows selected. I also have to make this work on several databases, includeing, grrr, Oracle, so non-standard MySQL "solutions" are doubly aggravating. DELETE FROM a WHERE a.b_id = b.id AND b.second_id = ? I have tried to do this before and always found a way, usually DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?) but I have too many rows, millions, in the IN crowd, ha ha, and it barfs. EXISTS is no better. At least Oracle barfs, and I haven't got to the others yet. I figured I would go with the worst offender first, and let me tell you, it is offensive. Dang I wish it were postgresql only! I could write a Dumb Little Test Program (tm) to read in all those IN ids and execute a zillion individual DELETE statements, but it would be slow as puke and this little delete is going to come up quite often now that I have a test program which needs to generate the junky data and play with it for several days before deleting it and starting over again. Hi, Have you tried something where you read in all those "IN id's" and then group them into blocks (of say 1,000 or 10,000 or whatever number works best)? Then execute: DELETE FROM a WHERE a.b_id in ([static_list_of_ids]) Replacing in a loop "[static_list_of_ids]" with each block of 1000 id's in a comma delimited string? I use this technique sometimes in middleware and it works pretty well. There's probably a pure-sql solution in Pg as well but this method should work across any SQL platform, which seems like one of your requirements. Steve -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] enumerate groups given a certain value
Hello,
Here is a sql problem, which I thought simple at first, but for which I
ended up with a solution I find surprisingly complicated.
I really think I could have achieved a much easier way of handling this,
but I do not manage to find the trick allowing a very simple and
efficient query to solve the problem.
Let's present it with a sample case. The initial table is the following
one :
drop table if exists test_gen ;
create table
test_gen as
select *
from (
select
chr((round(random()* 25) +65)::int) as id
, random()* 100 as val
from
generate_series(1,200) as g
order
by id
) as foo
select * from test_gen;
---
What I want to do is to enumerate lines for each group of id, following
the order of val.
For example :
id val gen
A 2.651051385328171
A 38.92893604934222
A 74.60891641676433
B 2.015121886506681
B 11.46420473232872
B 31.26432197168473
B 65.84279797971254
C 0.759994331747293 1
C 11.89057962037622
C 13.73886489309373
C 49.19343511573974
C 83.18619034253065
D 45.82689679227771
D 57.11615891195832
E 9.721256978809831
E 61.324825277552 2
E 70.33489583991473
F 0.498912342342371
Here is the solution I ended up with :
---
-- first count number of ids per group
drop table test_gen2 ;
create table test_gen2 as
select t1.*, t2.nb
from
test_gen as t1,
(
SELECT
id, count(*) as nb
FROM
test_gen
GROUP BY id
) as t2
WHERE
t1.id =t2.id
ORDER BY
t1.id;
create sequence seq_test_gen start with 1;
create sequence seq_test_gen2 start with 1;
-- get the table with the order set (gen is our order)
select
*
from
(
select
foo1.*,
nextval('seq_test_gen') as serial
from (
select
*
from
test_gen2
order by
id, val
) as foo1
) as t1,
(
select
foo.*,
nextval('seq_test_gen2') as serial
from (
select
gb1.*,
generate_series(1, gb1.nb) as gen
from (
select
id, nb
from
test_gen2
group by
id, nb
) as gb1
order by
gb1.id, gen
) as foo
) as t2
where
t1.serial = t2.serial
;
---
The problem seems to be as easy as : . But I could not find a better way to do that than putting a
serial on left and right side and do a join on this serial.
I also tried to find a solution using a modulo but could not manage to
get it work.
Anybody for a ray of light on a different approach ? This look like a
recurrent problem, isn't there an experienced sql programmer here who
tackled this issued a couple of time ?
Thanks for any help,
Vincent
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Thu, Aug 07, 2008 at 05:05:38PM +, Ragnar wrote: > did you look at DELETE FROM table1 USING table2 WHERE ... ? No, I hadn't known about that. It looks handy to know about, at least, but I don't see it for Oracle. I am going to play with that, but I don't think it will help here. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Thu, Aug 07, 2008 at 10:40:22AM -0700, Steve Midgley wrote: > Have you tried something where you read in all those "IN id's" and then > group them into blocks (of say 1,000 or 10,000 or whatever number works > best)? Then execute: > > DELETE FROM a WHERE a.b_id in ([static_list_of_ids]) It may come to something like that, but I figure handing over hubdreds of static IDs is probably worse for the planner than an expression, and it's ugly as sin :-) I tried using "%" for a mod function, but that seems to not be a universally recognized operator. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
[EMAIL PROTECTED] wrote: On Thu, Aug 07, 2008 at 10:40:22AM -0700, Steve Midgley wrote: Have you tried something where you read in all those "IN id's" and then group them into blocks (of say 1,000 or 10,000 or whatever number works best)? Then execute: DELETE FROM a WHERE a.b_id in ([static_list_of_ids]) It may come to something like that, but I figure handing over hubdreds of static IDs is probably worse for the planner than an expression, and it's ugly as sin :-) I tried using "%" for a mod function, but that seems to not be a universally recognized operator. Could you not achieve the same result with a LIMIT on subSELECT and reissue the command until there is nothing to delete? Is b_id already unique; or should you be using DISTINCT or GROUP BY on the subSELECT? DELETE FROM a WHERE a.b_id IN (SELECT DISTINCT id FROM b WHERE second_id = ? LIMIT 1000) DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ? GROUP BY id LIMIT 1000) If you're really desperate; is it possible to alter table 'a' to add column b_id; populate it; delete your rows without a join; then drop the column? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Thu, Aug 07, 2008 at 03:00:35PM -0400, Frank Bax wrote: > Could you not achieve the same result with a LIMIT on subSELECT and reissue > the command until there is nothing to delete? Oracle has some barbarous alternative to LIMIT. I find myself retching over Oracle almost as much as MySQL. > If you're really desperate; is it possible to alter table 'a' to add column > b_id; populate it; delete your rows without a join; then drop the column? I thought of something similar, but UPDATE has the same limitation as DELETE. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
[EMAIL PROTECTED] writes: > I have tried to do this before and always found a way, usually > DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?) > but I have too many rows, millions, in the IN crowd, ha ha, and it > barfs. Define "barfs". That seems like the standard way to do it, and it should work. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
[EMAIL PROTECTED] wrote: On Thu, Aug 07, 2008 at 03:00:35PM -0400, Frank Bax wrote: If you're really desperate; is it possible to alter table 'a' to add column b_id; populate it; delete your rows without a join; then drop the column? I thought of something similar, but UPDATE has the same limitation as DELETE. Instead of DELETE; what if you CREATE a new table of the rows you wish to keep (using JOIN instead of IN). Then either drop original table and rename new table OR delete all rows and re-populate from new table. You mentioned that the process of insert/delete is to be repeated. Are all the rows that were inserted; the same ones that will be deleted when the cycle is complete? If yes; then after you delete this batch of rows; add a 'junky' column and populate with any value different from the default. Your mass-insert should populate with the default value; and you can delete based on this value. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Thu, 2008-08-07 at 09:14 -0700, [EMAIL PROTECTED] wrote: >DELETE FROM a WHERE a.b_id = b.id AND b.second_id = ? > This should work for your needs: delete from a using b where a.id = b.id -- join criteria and b.second_id = ? > I have tried to do this before and always found a way, usually > DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id > = ?) This is akin to: delete from a where (a.key1, a.key2, a.key3) in (select key1, key2, key3 from b) I use this every day for millions of rows per delete and it works just fine and in a very reasonable time period. -Mark -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Thu, Aug 07, 2008 at 04:01:29PM -0400, Frank Bax wrote: > You mentioned that the process of insert/delete is to be repeated. Are all > the rows that were inserted; the same ones that will be deleted when the > cycle is complete? If yes; then after you delete this batch of rows; add a > 'junky' column and populate with any value different from the default. > Your mass-insert should populate with the default value; and you can delete > based on this value. That would work, and might even be possible, but adding a column just to mark test data doesn' quite sit right. This data is, yucch, tax data, and I have been generating test cases with bogus country names, like Fredonia -- the taxes themselves are in a dozen tables with foreign key references holding them together, one of them being the country name. I was sort of doing what you suggest by using bogus country names, but that's only indirect. To add a column to every concerned table would be a pain for other reasons. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] enumerate groups given a certain value
Hello, Picavet.
> Anybody for a ray of light on a different approach ? This look like a
> recurrent problem, isn't there an experienced sql programmer here who
> tackled this issued a couple of time ?
Actually, I'm not very experienced in SQL. But from my point of view
this problem could be solved much more easily using plpgsql rather
than plain SQL.
Your initial query have been a little bit modified for convenience:
CREATE TABLE test_gen AS
SELECT *
FROM(
SELECT
chr((round(random()* 25) +65)::int) AS id,
random()* 100 AS val,
0::INTEGER AS gen
FROM
generate_series(1,200) as g
ORDER BY id
) foo;
CREATE OR REPLACE FUNCTION enum_groups (varchar) RETURNS SETOF test_gen AS
$body$
DECLARE
r chip.test_gen%ROWTYPE;
_id VARCHAR;
i INTEGER := 0;
q TEXT;
BEGIN
q := 'SELECT * FROM ' || $1 || ' ORDER BY id ASC, val ASC;';
FOR r IN EXECUTE q LOOP
IF ((_id IS NULL) OR (_id = r.id)) THEN
i := i + 1;
ELSE
i := 1;
END IF;
_id := r.id;
r.gen := i;
RETURN NEXT r;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql';
Now you can SELECT * FROM enum_groups('test_gen') and you'll get following:
id val gen
B 2,353265881538391
B 11,42691676504912
B 11,93143948912623
B 27,90169059298934
B 28,548994101584 5
B 48,81512420251976
B 50,215089507401 7
B 59,613792411983 8
B 61,22819306328899
B 80,49540463835 10
C 5,866358801722531
C 11,59744574688382
C 15,81365317106253
C 29,84652016311884
C 52,98715918324895
C 57,34610003419226
C 63,33442740142357
...
HTH
PS. Sorry, I forget to reply all first time.
--
Best regards. Yuri.
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Thu, Aug 07, 2008 at 03:58:51PM -0400, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > I have tried to do this before and always found a way, usually > > > DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?) > > > but I have too many rows, millions, in the IN crowd, ha ha, and it > > barfs. > > Define "barfs". That seems like the standard way to do it, and it > should work. In this case, the first database I tried was Oracle, and it complained of too much transactional data; I forget the exact wording now. It worked on some cases, but others with "too much" data died with the complaint after thinking about it for a minute or so. Since the test data will only grow in size, I was hoping for some other way. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Aug 7, 2008, at 2:39 PM, [EMAIL PROTECTED] wrote: In this case, the first database I tried was Oracle, and it complained of too much transactional data; I forget the exact wording now. You might try it on PostgreSQL. While it might have to spill the result of the subquery to disk, it shouldn't actually fail unless disk is very constrained. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
[EMAIL PROTECTED] wrote: > On Thu, Aug 07, 2008 at 03:58:51PM -0400, Tom Lane wrote: > > [EMAIL PROTECTED] writes: > > > I have tried to do this before and always found a way, usually > > > > > DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?) > > > > > but I have too many rows, millions, in the IN crowd, ha ha, and it > > > barfs. > > > > Define "barfs". That seems like the standard way to do it, and it > > should work. > > In this case, the first database I tried was Oracle, and it complained > of too much transactional data; I forget the exact wording now. I suggest you do not assume that Oracle implementation details apply to Postgres, because they do not, most of the time. They certainly don't in this case. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Thu, Aug 07, 2008 at 06:12:32PM -0400, Alvaro Herrera wrote: > I suggest you do not assume that Oracle implementation details apply to > Postgres, because they do not, most of the time. They certainly don't > in this case. And I suggest you go back and read where I said I had to do this on several databases and am trying to avoid custom SQL for each one. I would much rather this were postgresql only, but it's not. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Aug 7, 2008, at 4:37 PM, [EMAIL PROTECTED] wrote: And I suggest you go back and read where I said I had to do this on several databases and am trying to avoid custom SQL for each one. I would much rather this were postgresql only, but it's not. Then it does appear you have an Oracle debugging problem, more than a Postgres SQL formulation problem. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Thu, Aug 7, 2008 at 5:37 PM, <[EMAIL PROTECTED]> wrote: > On Thu, Aug 07, 2008 at 06:12:32PM -0400, Alvaro Herrera wrote: > >> I suggest you do not assume that Oracle implementation details apply to >> Postgres, because they do not, most of the time. They certainly don't >> in this case. > > And I suggest you go back and read where I said I had to do this on > several databases and am trying to avoid custom SQL for each one. I > would much rather this were postgresql only, but it's not. My experience with Oracle and PostgreSQL together tell me that you're better off researching a proper method for each db independent of the other. While it may be possible to come with a generic method that works well in both dbs for a lot of things, this doesn't look like one of them. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DELETE with JOIN
On Thu, Aug 07, 2008 at 10:55:17PM -0400, Joe wrote: > I recall a similar problem ages ago and IIRC it was due to Oracle's locking > configuration, i.e., some parameter had to be increased and the instance > restarted so it could handle the transaction (or it had to be done in > chunks). I gather that the general consensus is that Oracle and Postgresql are different enough that I will have to figure them out independently. I have found that Postgresql is more tolerant of operator ignorance, so I had hoped that any Oracle solution would work elsewhere, but maybe not. Thanks for the helpful responses. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
