Re: index(fct(primary key)) kills INSERTs

Frank Miles <fpm(at)u(dot)washington(dot)edu> writes:
> If an index is created based on a function of the primary key,
> you cannot insert new entries into the database.

I think the critical point here is that your "function of the primary
key" is actually doing a SELECT from the table:

SELECT INTO prec * FROM test_table WHERE tt_id = dum_int;
IF NOT FOUND THEN
RAISE EXCEPTION ''project % not found'', dum_int;
END IF;

When I try your example, I get

play=> INSERT INTO test_table (tt_descr) VALUES ('third - will fail');
ERROR: project 3 not found

which surprises me not at all, because at the point where this function
is invoked, the new record with tt_id 3 hasn't been entered into the
table yet.

I'm not sure what you are really trying to accomplish here --- as you
say, it's a stripped-down example and not too intelligible. As far
as the example goes, you could skip the SELECT and just use the
passed-in parameter value. What was the original goal that made you
feel you needed to SELECT the about-to-be-inserted row?

regards, tom lane