Re: [HACKERS] how to deal with sparse/to-be populated tables

The thing is, in the relational model there isn't a standard
defininition of "already exists". For example, when you say
"already exists", I presume you mean that a record with the
same primary key already exists. But not all tables have
primary keys.

There are two things you can do...

1) remember if a record came out of the database in the first place
with a flag. This is what an object database would do.

2) If there is a unique index, instead of checking
whether the record exists with exists_in_table,
attempt to update the record. If you get a database error, THEN
do an insert. This is a common programming technique, often used
with unix system calls. Try one option and if error try the other.
Don't try to predict yourself whether an error will occur. This
will save 1 or two database calls depending on whether it exists
or not.

Alfred Perlstein wrote:
>
> We were having some trouble doing updates to our database,
> a lot of our database sort of works like this:
>
> dbfunc(data)
> somedatatype *data;
> {
> somedatatype *existing_row;
>
> existing_row = exists_in_table(data);
>
> if (existing_row != NULL) {
> update_table(existing_row, count = count + data->count)
> } else
> insert_into_table(data);
>
> }
>
> Is there anything built into postgresql to accomplish this without
> the "double" work that goes on here?
>
> something like:
> update_row_but_insert_if_it_doesn't_exist(data,
> update = 'count = count + data->count');
>
> Meaning, if a row matching the 'new' data exists, update it, otherwise
> store our new data as a new record?
>
> It seems like the database has to do an awful amount of extra work
> for our application because we haven't figured out how to do this
> effeciently.
>
> Any pointers?
>
> thanks,
> --
> -Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
>
> ************