Prevent explicit insert into serial columns
In Postgres 10 or later, consider an IDENTITY
column instead. Like:
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
See:
- What are Identity Columns?
- Auto increment table column
Original answer for older versions:
Updateable View
In Postgres 9.4, views are automatically updatable per column. I.e., as long as basic conditions are met, columns are automatically updatable for plain references to underlying columns. Expressions are not. We can exploit this feature:
CREATE TABLE test_table(
id serial PRIMARY KEY
, foobar text
);
CREATE VIEW test_view AS
SELECT id * 1 AS id -- id unchanged but not updatable!
, foobar
FROM test_table;
By multiplying id
with 1
, the value is unchanged but the column is not automatically updatable any more.
REVOKE
INSERT
/UPDATE
privileges ontest_table
from your users.- Make another role holding those privileges own the view.
GRANT INSERT
/UPDATE
ontest_view
to your users.
Now they can do everything, but they cannot manually set or alter the value in id
. It's your choice how to handle DELETE
.
This simple and quick solution works out of the box in Postgres 9.4. Automatically updatable views were introduced with Postgres 9.3, but all columns need to be updatable in that version for the feature to work.
In Postgres 9.3 or older, provide an INSTEAD OF INSERT
trigger or an unconditional ON INSERT DO INSTEAD
rule.
While writing rules / triggers manually, you don't need the id * 1
trick. You might want to use this even in Postgres 9.4+ to fine-tune functionality. Example:
CREATE VIEW test_view1 AS TABLE test_table;
CREATE OR REPLACE RULE ins_up AS
ON INSERT TO test_view1 DO INSTEAD
INSERT INTO test_table (foobar) VALUES (NEW.foobar);
Now, INSERT
on the view is possible, but not yet UPDATE
or DELETE
. Write more rules if you want that.
SQL Fiddle (Postgres 9.6)
Important difference: while the automatically updatable view rejects attempts to INSERT
/ UPDATE
values in id
with an exception, the demonstrated RULE
simply ignores values for id
and proceeds without exception.
Simple trigger
Alternatively, you could simply overwrite the serial column with the next value from its sequence unconditionally:
CREATE OR REPLACE FUNCTION force_serial_id()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
NEW.id := nextval(pg_get_serial_sequence(quote_ident(TG_TABLE_NAME), 'id'));
RETURN NEW;
END
$func$;
That's simpler and cheaper and less error-prone than trying to be smart about it. quote_ident()
to safely escape otherwise illegal names (also defends against SQL injection).
Like @dezso commented, this burns two numbers per row in normal operation with a serial
column because a default is fetched before the trigger function kicks in. Typically, gaps in a sequence should not be a problem (to be expected anyway), but you can avoid the side effect by removing the DEFAULT
from the column. Then you rely on the trigger exclusively.
You could fine-tune the UPDATE
case with a separate trigger and a condition on the trigger itself WHEN (OLD.id <> NEW.id)
. Syntax example:
- How to prevent a PostgreSQL trigger from being fired by another trigger?
Note the use of pg_get_serial_sequence()
, which won't break like your original for non-basic identifiers. Think of "MyTable"
or a non-default sequence name. Still assuming the column name id
which I personally never use since it's not descriptive.
Identity Columns
This isn't exactly an answer yet, but what you want is GENERATED ALWAYS
. And, it's on its way, maybe as soon as the next release of PostgreSQL, PostgreSQL 10
CREATE TABLE itest4 (
a int GENERATED ALWAYS AS IDENTITY,
b text
);
Syntax stolen verbatum from the emails about the patch.
Track the issue in the commitfest here.
From the SQL Spec,
If <identity column specification> is specified, then:
i) An indication that the column is an identity column.
ii) If ALWAYS is specified, then an indication that values are always generated.
iii) If BY DEFAULT is specified, then an indication that values are generated by default.
iv) The General Rules of Subclause 9.26, “Creation of a sequence generator”, are applied with
SGO as OPTIONS and ICT as DATA TYPE; let the descriptor of the sequence generator SG be
the SEQGENDESC returned from the application of those General Rules.