[SQL] Delete with foreign keys
I have three tables -- CREATE TABLE name (id INT PRIMARY KEY, str VARCHAR(20)); CREATE TABLE place (id INT PRIMARY KEY, name_id INT REFERENCES name(id)); CREATE TABLE data (id INT PRIMARY KEY, place_id INT REFERENCES place(id)); I want to delete all place and data rows which reference specific names, but not the names themselves. I can do it like this: DELETE FROM data d WHERE exists (SELECT id from place p WHERE d.place_id = p.id AND p.name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%')); DELETE FROM place WHERE name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%'); but it seems rather roundabout, and I wonder whether the EXISTS and IN business is slow. Is there some way to do it using JOINs? I think of something like this: DELETE FROM place p, name n WHERE p.name_id = n.id AND n.str LIKE 'Fredonia%'; but I don't want to delete the name rows. Then I think of this: DELETE FROM place p WHERE p.name_id = name.id AND name.str LIKE 'Fredonia%'; but I feel uneasy about the two separate name references when the table is not named in the FROM clause. Maybe that's just my novicity. I also wonder about getting fancy and ending up with SQL specific to a database; I don't have any plans to migrate, but I try to avoid branding my SQL. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Delete with foreign keys
On Sat, Feb 23, 2008 at 12:42:13PM +0530, dipesh wrote: >alter table data add constraint data_place_id_fkey FOREIGN KEY(place_id) > REFERENCES place(id) ON DELETE CASCADE ON UPDATE CASCADE; Interestingly different way of doing it. Thanks. It makes me think too :-) -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[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 (pgsql-sql@postgresql.org) 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 (pgsql-sql@postgresql.org) 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 (pgsql-sql@postgresql.org) 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 (pgsql-sql@postgresql.org) 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 (pgsql-sql@postgresql.org) 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 (pgsql-sql@postgresql.org)
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 (pgsql-sql@postgresql.org) 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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to split a table?
Hi, I want to split a table to 2 small tables. The 1st one contains 60% records which are randomly selected from the source table. How to do it? Regards, Felix
[SQL] Any documatation about porting from Oracle to PostgreSQL
Hi all, I'm a newbie of PostgreSQL. I'm searching materials about porting from Oracle to PostgreSQL. Anyone can share with me some good documatations? Thanks and regards, Felix
Re: [SQL] [NOVICE] [GENERAL] How to split a table?
to do some statistics analysis. 2006/10/17, Andreas Kretschmer <[EMAIL PROTECTED]>: Felix Zhang <[EMAIL PROTECTED]> schrieb: > Hi,>> I want to split a table to 2 small tables. The 1st one contains 60% records> which are randomly selected from the source table.> How to do it?Why do you want to do this? Andreas--Really, I'm not out to destroy Microsoft. That will just be a completelyunintentional side effect. (Linus Torvalds)"If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Turning column into *sorted* array?
Suppose I have a table containing a column with integer values:
CREATE TABLE some_table (x int NOT NULL);
INSERT INTO some_table values(1);
INSERT INTO some_table values(5);
INSERT INTO some_table values(3);
Also, there is a view to that table (probably superfluous here, but it's
in the actual more complicated database design):
CREATE VIEW some_view AS SELECT x FROM some_table;
Now, the goal is to turn the column "x" of the view "some_view" into a
*sorted* array. I tried the following code. It works, but is it
reliable? IOW: is it guaranteed that this gives me indeed a sorted
array? If not, what's a good alternative?
CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
COMMENT ON AGGREGATE array_accum(anyelement) IS
'Found in section "33.9. User-Defined Aggregates" of the PostgreSQL 7.4.2
Documentation.';
SELECT array_accum(x) FROM (SELECT * FROM some_view ORDER BY x) AS tmp;
BTW, the best alternative (in terms of execution performance) that comes
into my mind is to create an aggregate that does the sorting right away
while the values "come in" from the rows. But that'd probably take me
some time to get right.
--
Felix E. Klee
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Turning column into *sorted* array?
At Wed, 18 May 2005 19:54:08 +0200, PFC wrote: > > SELECT array_accum(x) FROM (SELECT * FROM some_view ORDER BY x) AS tmp; > > If you're using integers, you could use the int_array_accum or > something from the intarray module which is a lot faster. I believe > intarray also has a function for sorting integer arrays... No, unfortunately I'm using strings in "real-life" (my example is perhaps a bit over-simplified). Let me make my original question a bit more precise: How do I best transform a column of varchars into a *sorted* array? -- Felix E. Klee ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
