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
