1

Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants. There is a PK-FK relationship between plants.id and samples.tree, and a constraint on table plants that every genus-species combination must be unique. But some, of the entries in table plants are actually just misspelled versions of other records in the same table. So:

Table: plants

id  |  genus   | species
-------------------------
1   |  Acer    | rubrum
2   |  Acer    | rubra
3   |  Quercus | albaa
.....


Table: samples

sampid | tree | size
-----------------------
1      | 1    | 34
2      | 1    | 30
3      | 2    | 16 
4      | 2    | 34
5      | 3    | 20
...

Table: plant_name_mapping

oldgenus | oldspecies | newgenus | newspecies
-----------------------------------------------
Acer     | rubra      | Acer     | rubrum
Quercus  | albaa      | Quercus  | alba
...

I want to update table plants based on table plant_name_mapping. If the update creates a new name that wasn't in plants already, great. However, if the update creates a name that is already in the table plants, I want to delete that duplicate record, and than change the foreign key in the table plants to match the name that was originally there all along. So the output should look like:

Table: plants

id  |  genus   | species
-------------------------
1   |  Acer    | rubrum
3   |  Quercus | alba
.....


Table: samples

sampid | tree | size
-----------------------
1      | 1    | 34
2      | 1    | 30
3      | 1    | 16 
4      | 1    | 34
5      | 3    | 20
...

I'm working with a postgresSQL-9.3 database. Obviously I have many more records than this example. Let me know what kind of update statement or stored procedure could do this. I'm having trouble describing what I want to do, but I think the examples illustrate it pretty clearly. Thanks for the help.

1

This solution requires first that there aren't any conflicting or cascading information in plant_name_mapping; to work, the following query must return no records:

select *
from plant_name_mapping o
join plant_name_mapping n ON ((n.newgenus, n.newspecies) = (o.oldgenus, o.oldspecies));

The queries to update your tables are:

create temp table plantschanged as
with
  renamed as (
    select o.*, n.newgenus, n.newspecies, n.newspecies is not null as namechanged
    from plants o
    left join plant_name_mapping n on ((n.oldgenus, n.oldspecies) = (o.genus, o.species))
      ),
  newplants as (
    select id, coalesce(newgenus, genus) as genus, coalesce(newspecies, species) as species, namechanged
    from renamed
    ),
  plantsnewid as (
    select *, min(id) over(partition by genus, species) as new_id
    from newplants
    )
select *
from plantsnewid
where namechanged or new_id <> id;

with
  plantsrenamed as (
    update plants p set genus = pc.genus, species = pc.species
    from plantschanged pc
    where p.id = pc.id and pc.id = pc.new_id
    returning p.id
    )
update samples s set tree = pc.new_id
from plantschanged pc
where s.tree = pc.id and pc.id not in (select * from plantsrenamed);

delete from plants
where id in (select id from plantschanged where new_id <> id);

The table plants may have more dependencies than just the table samples. You may check through the following query if you have any further tables that would require updating (perform the same query as update samples with each of these, prior to delete from plants):

select conrelid::regclass, conname
from pg_constraint
where confrelid = 'plants'::regclass and contype = 'f';
1
  • Thanks a lot @Ziggy Crueltyfree Zeitgeister. This script doesn't seem to be working though, because it violates the unique genus-species constraint in the table plants. Its a big step in the right direction, though. Jul 8 '16 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.