From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Deleting Multiple Rows Based on Multiple Columns |
Date: | 2011-08-13 19:07:57 |
Message-ID: | 2DBE8E6F-2974-4DD7-B4FE-DAF7AD6A351A@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> If it is possible to leave one row with specific values in the columns
> (loc_name, sample_date, param) and delete the additional ones, I'd like to
> learn how to do so. I know that I'll have use for these techniques with
> future data.
>
> Else, I'd like to delete all those rows with multiple copies. Then I'll
> manually remove the extra rows in emacs, and insert the remainder in the
> original table.
It will be easiest to delete all and add back the single desired record later.
The general form will be:
DELETE FROM chemistry c WHERE EXISTS (SELECT 1 FROM chem_too ct WHERE c.field =ct.field AND c.field2=ct.field2 etc...)
Not tested so minor syntax tweaks by be needed. Pretty sure docs cover this use case. You can also do:
DELETE FROM chemistry
USING chem_too
WHERE chemistry.fields =AND chem_too.fields;
The other way to group multiple columns in a single "row" column.
E.g., WHERE (field1, field2, field3) =/IN (SELECT field1, field2, field3 FROM ...)
Note the parenthesis grouping the three columns into a single unit for comparison. It is useful shorthand for the ( field1=field1 AND field2=field2 etc... ) construct. You can readily use this form in the FROM/USING form's WHERE clause.
In your chem_too table you can use the ROW_NUMBER window function over the desired key columns to number the duplicate rows (in a sub-query) and then, in the outer query, remove any rows that have a ROW_NUMBER > 1. Try to write the query yourself and post your best effort if you get stumped. Using this query on the chem_too table you can select a single record per key to insert back into the main table. You can also use ORDER BY in the WINDOW definition to sort on secondary (non-partition by) fields if desired.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Joseph Krogh | 2011-08-13 19:30:31 | Re: Using Postgresql as application server |
Previous Message | Adrian Klaver | 2011-08-13 19:07:28 | Re: postgresql server crash on windows 7 when using plpython |