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

Chris Bitmead <chrisb(at)nimrod(dot)itg(dot)telstra(dot)com(dot)au> writes:
> Hmm. Doesn't PostgreSQL have a big list of error codes? I don't think
> it does, I've never seen one. There should be a way to get error
> codes without comparing strings. Should this be on the TODO?

It doesn't, there should, and it already is ;-)

In the meantime, looking at the error message string is Alfred's
only option for distinguishing duplicate-record from other errors,
I'm afraid.

A partial answer to his performance concern is to use a rule
(or possibly a trigger) on the database side to reinterpret
"insert into table X" as "either insert or update in table Y,
depending on whether the key is already there". This wouldn't
buy anything in terms of database cycles, but it would avoid two
rounds of client-to-backend communication and query parsing.

I've never done that myself, but perhaps someone else on the
list has a working example.

regards, tom lane