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!
1, the value is unchanged but the column is not automatically updatable any more.
UPDATE privileges on
test_table from your users.
- Make another role holding those privileges own the view.
GRANT INSERT /
test_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
This simple and quick solution works out of the box in Postgres 9.4. Automatically updateable views were introduced with Postgres 9.3, but all columns need to be updateable 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);
INSERT on the view is possible, but not yet
DELETE. Write more rules if you want that.
SQL Fiddle (Postgres 9.6)
Important difference: while the automatically updatable view rejects attempts to
UPDATE values in
id with an exception, the demonstrated
RULE simply ignores values for
id and proceeds without exception.
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 AS
NEW.id := nextval(pg_get_serial_sequence(quote_ident(TG_TABLE_NAME), 'id'));
$func$ LANGUAGE plpgsql;
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:
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.