Michael Chaney <mdchaney(at)michaelchaney(dot)com> writes:
> begin;
> lock table test in exclusive mode;
> insert into test values (1,(select max(id2) from test where id1=1)+1);
> commit;
> It's not pretty, and it'll probably slow down as the table grows.
As-is, that will definitely get pretty slow on large tables. You could
avoid the slowdown with the standard hack for replacing max() with an
index probe:
insert into test values (1,
(select id2+1 from test where id1=1 order by id1 desc, id2 desc limit 1)
);
This will be fast if there is a double-column index on (id1, id2).
regards, tom lane