how to do this -- subqueries? - Mailing list pgsql-general

-- main data table
create table person (
    id serial,
    lname varchar,
    primary key ( lname ) -- to keep example simple
);

-- validation table
create table relation_v (
    id serial,
    name varchar
);
insert into relation_v(name)values('Friend');
insert into relation_v(name)values('Colleague');
insert into relation_v(name)values('Family');

-- linking table (person-to-person, many-to-many)
create table relation (
    person     int4 references person ( id ),
    relative   int4 references person ( id ),
    relation_v int4 references relation_v ( id ),
    descr varchar,
    primary key (person,relative,relation_v)
);
...

okay, got that? relation is a linking table for a many-to-many
relationship between PERSON and PERSON. relation_v is the
validation table having valid relation types. joe can be family
and colleague to bob, but since there's no reason to have
joe-bob-family twice, person-relative-relation_v is the primary
key.

select descr
    from
        relation,
        person     p,
        person     r,
        relation_v t
    where
        relation.person     = p.id and p.lname = <$P_NAME> and
        relation.relative   = r.id and r.lname = <$R_NAME> and
        relation.relation_v = t.id and t.name  = <$R_TYPE>

that select properly displays one record, as it should -- we
specify each of the three elements of the primary key, in the
WHERE clause.

so here's the tricky part: i want to fix a relation record --
it's "family" but should be "colleague" instead:

update relation set
    relation_v = (
        -- get the new value we're looking for
        select z.id from relation_v z where name='Colleague'
    )
    from
        person     p,
        person     r,
        relation_v t
    where
        -- make sure we get the one record to update
        relation.person     = p.id and p.lname = <$P_NAME> and
        relation.relative   = r.id and r.lname = <$R_NAME> and
        relation.relation_v = t.id and t.name  = <$R_TYPE>

prodcing ERROR "Cannot insert a duplicate key into unique index"

the WHERE clause is identical, but given the "Cannot insert a
duplicate key into unique index" error, it's apparently finding
more than one record.

(in this test database i've got only three people, and one
relation between each, for each "direction" a-rel-b and b-rel-a
for a total of six relation records.
    person a   relative b    relation_v family
    person b   relative a    relation_v family
    person a   relative c    relation_v boss
    person c   relative a    relation_v employee
    person b   relative c    relation_v colleague
    person c   relative b    relation_v family -- should be colleague
if i were to set ALL "relation_v" values to "Family" (for
example) it should be legal. why "duplicate key" error?)

the trouble is, i need to get the old relation_v.id so i can be
sure i have the one record i'm looking for, and then the new
relation_v.id to set it properly.

which cluestick do i need? (or is there a better paradigm for
this kind of thing?)

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !