[GENERAL] I often have to do "update if exist, else insert", is my database design wrong?

Hi. This is just some thoughts about database design.
I often find my self having to do this

update table_XY set x=..., y=... where x=... AND y=....;
if not found then
insert into table_XY (x,y) values (...,...);
end if;

Is this normal or are there something else I could do so I don't have
to check if it exists?

Or is there some more general problem with the table design?
table_XY is in this case (and most cases) a table like this

create table table_XY (
x int references table_X,
y int references table_Y
);

I could of course add a constraint unique(x,y) to avoid duplicates,
but that would not change the the need to check if it exists before
inserting.
I could also do
delete from table_XY where x=... and y=...
insert into table_XY (x,y) values (...,...);
But that would seem to be very slow.

One idea is to put in dummy records for each x,y combination, as a
default value. But if table_XY has very few records, it seems like a
complete vaste of resources.
If I really wanted table_XY to contain a complete set of records of
all possible pairs of x,y values, how would I do that? The only way I
can think of is that when inserting into table_X, I'd do

insert into table_XY VALUES SELECT xvalue,table_Y.id FROM table_Y;

where table_Y contains a primary key called id.

How would you (you who knows this stuff far better than me) do this? :-)

For the developers: a combined insert/update command would be nice :-)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general