Problem merging two rows into same primary key

[Patrik Kudo <kudo@xxxxxxxxxxxx>]

Hi!

I've got a problem I can't seem to find an answer to. The problem is
simplified by this example:

1. We have two tables:

create table asdf (id serial primary key,
data text);
create table qwert (id serial,
data integer references asdf
on delete cascade on update cascade);

2. We populate both tables with the following result:

keytest=# select * from asdf;
id | data
----+------
1 | asdf
2 | asd2
3 | asd3
4 | asd4
(4 rows)

keytest=# select * from qwert;
id | data
----+------
1 | 2
2 | 4
(2 rows)


Now to the problem. We want to merge rows with id = 2 and id = 4 into id
= 1 in the asdf table with the qwert table beeing updated to reflect the
change. The desired result would yeild:

keytest=# select * from asdf;
id | data
----+------
1 | asdf
3 | asd3
(2 rows)

keytest=# select * from qwert;
id | data
----+------
1 | 1
2 | 1
(2 rows)


I find no way to do this because the primary/foreign keys that would
make this easy actually makes it impossible. Are there any smart way to
do this or do I need to drop the primary key (hence also drop the
foreign keys since the drop will cascade), update the data manually and
then recreate the constraints? I hope there's an easier way beacuase in
the real scenario we're dealing with nearly 100 tables depending on that
single one with the primary key...

Thanks in advance,
Patrik Kudo

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend