I suggest a function taking a regclass
parameter that runs ALTER SEQUENCE
with a new randomly generated increment before it returns the next value from a given sequence.
Can be used as drop-in replacement for nextval()
.
Per documentation on ALTER SEQUENCE
:
increment
The clause INCREMENT BY
increment is optional. A positive value will
make an ascending sequence, a negative one a descending sequence. If
unspecified, the old increment value will be maintained.
However:
You must own the sequence to use ALTER SEQUENCE
.
So we need to take care of privileges. You could make the function SECURITY DEFINER
and owned by a superuser. If you don't REVOKE
privileges from public
it works for anyone on any sequence. There are two basic strategies to restrict usage:
To allow for selected sequences only, change the owner of those sequences to some dedicated role, say randseq
and make randseq
own the function (still with SECURITY DEFINER
).
To allow for selected roles only, REVOKE
all privileges on the function from public
and GRANT EXECUTE
to said roles. You might use a group role to simplify privilege management.
Or combine both:
CREATE OR REPLACE FUNCTION nextval_rand(regclass)
RETURNS int AS
$func$
BEGIN
EXECUTE format('ALTER SEQUENCE %s INCREMENT %s'
, $1 -- regclass automatically sanitized
, (random() * 100)::int + 1); -- values between 1 and 100
RETURN nextval($1)::int;
END
$func$ LANGUAGE plpgsql SECURITY DEFINER;
-- to restrict usage:
ALTER FUNCTION nextval_rand(regclass) OWNER TO randseq;
REVOKE ALL ON FUNCTION nextval_rand(regclass) FROM public;
GRANT EXECUTE ON FUNCTION nextval_rand(regclass) TO randseq;
GRANT randseq TO ???;
Call:
SELECT nextval_rand('tbl_tbl_id_seq'::regclass);
All you have to do now is replace nextval()
with nextval_rand()
in the column default of any serial column. And possibly change the owner of the sequence.
ALTER SEQUENCE tbl_tbl_id_seq OWNER TO randseq;
ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval_rand('tbl_tbl_id_seq'::regclass);
Notes
ALTER SEQUENCE
is designed not to block concurrent transactions. It takes effect immediately and cannot be rolled back. It should work reliably in a multi-user environment. Read the Notes section of the manual page for the fine print of ALTER SEQUENCE
behavior.
There is a very slim chance for a race condition, where two concurrent operations each run ALTER SEQUENCE
before calling nextval()
. Since we are operating with random numbers anyway, this really doesn't matter.
Since we are running dynamic SQL I would normally SET search_path = public, pg_temp
for the function. But since the parameter is regclass
, only valid sequence names can be passed and are automatically schema-qualified and escaped unambiguously.
PostgreSQL does not have "auto-increment" fields in the sense of MySQL's AUTO_INCREMENT
, but I'm guessing you mean SERIAL
.
If so, yes, what you describe is possible, but please, please don't do this.
A SERIAL
is just shorthand for a CREATE SEQUENCE
and a default value. e.g.
CREATE TABLE blah(
id serial primary key
);
is actually shorthand for:
CREATE SEQUENCE blah_id_seq;
CREATE TABLE blah (
id integer primary key default nextval('blah_id_seq'::regclass)
);
ALTER SEQUENCE blah_id_seq OWNED BY blah.id;
You can use this knowledge to cast the nextval
return to a character type, e.g.
CREATE TABLE thisiswrong (
id text primary key default CAST(nextval('thisiswrong_id_seq'::regclass) AS text)
);
but again, I beg of you, do not do this. It's horrible. It's wrong. You will regret it. Whatever you are trying to do, there is a better way to do it than this.
I have intentionally not shown how to generate a formatted field like PAT000001
, but you can use any expression in a DEFAULT
, not just a CAST
. So look at the to_char
function or the format
function for how to do this if you insist.
The correct way to do what you want to do is not to do it. Get the application to display codes like PAT000001
when it sees a primary key value for patient_id
like 1
. The user never needs to know that you're just storing integers.
If you want other prefixes like PAT
, DOC
, etc, use a composite primary key, e.g.
CREATE TABLE saner_ish (
categorycode varchar(3) CHECK (length(categorycode) = 3),
patient_id integer default nextval('saner_ish_patient_id'::regclass),
PRIMARY KEY (categorycode, patient_id)
);
(possibly with an enum
type, an IN
list, or whatever for validation).
Then in the application turn PAT00001
into ('PAT', 1)
for queries, and reverse it for display to the user.
Best Answer
Postgres has the
serial
datatype which matches SQL Server'sIDENTITY
or MySQL'sAUTO_INCREMENT
.Internally it is shorthand for a
SEQUENCE
but does that matter? It acts likeIDENTITY
/AUTO_INCREMENT
:Edit,
I think what OP means is "is there SCOPE_IDENTITY or such" in PostgreSQL. Yes. You'd need currval or another one