Re: index(fct(primary key)) kills INSERTs - Mailing list pgsql-bugs

Frank Miles <fpm@u.washington.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