On Fri, 28 Jun 2002 18:16:30 -0400, Gregory Seidman
<gss+pg@cs.brown.edu> wrote:
>CREATE TABLE Foo (
> id int UNIQUE NOT NULL, -- no default yet
> -- ... whatever else
> primary key (id)
>);
>
>CREATE FUNCTION next_foo() RETURNS int
>AS 'SELECT COALESCE(max(id), 0)+1 FROM Foo'
>LANGUAGE SQL;
>
>ALTER TABLE Foo ALTER COLUMN id SET DEFAULT next_foo();
Greg, yes this works, but ...
it turns your application into a single user system, because
concurrent transactions could see the same max(id) and therefore try
to insert duplicate keys,
and the performance hit soon gets unacceptable, because select max()
always does a table scan.
Kevin, stick to serials. They offer the best performance and you will
get used to them. And you do the migration only once, don't you?
Servus
Manfred