Kevin Brannen sez:
[...]
} I suppose I could change the question to: Why can't we have it both
} ways? You know, have that ability controlled by a param in the
} postgresql.conf file; it could even be defaulted to "off". I really
} think this could be important, especially if the ultimate goal is world
} domination. ;-) After all, bulk loads and transformations are a fact of
} life that must be dealt with (so "don't do that" is not an option unless
} I don't use Pg, else I'll never migrate away from mysql).
You can have it both ways, at what may or may not be an acceptable
performance penalty. A trigger/rule can be written to update the sequence
upon any insertion. For that matter, since SERIAL is just shorthand for
creating a sequence and setting the column default to a function call, one
can simply write it longhand and replace the function call with a function
you wrote. For example (this requires some hoop-jumping to make the
references come out right):
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();
...and every time you insert a row without specifying the id by hand, the
id field will default to one more than the largest value currently in the
table. If you were feeling really clever and didn't mind the performance
penalty, you could even write a function that would reuse gaps.
Basically, the default behavior is the one with the minimum performance
hit.
[...]
} Thanks!
} Kevin
--Greg
