CODEWITHSUNDEEP
postgresqldefault-valuedatabase-trigger
Diego Pomares
Diego Pomares

Reputation: 88

How to force the default value of a column in PostgreSQL?

Is there any practical way other than triggers to ignore query given values in favor of default ones during an INSERT/UPDATE?

This is the behavior I want to archive:

CREATE TABLE foo (id serial, data text);

INSERT INTO foo (data) VALUES('bar1');
INSERT INTO foo (id, data) VALUES(50, 'bar2');
INSERT INTO foo (id, data) VALUES(-34, 'bar3');
INSERT INTO foo (id, data) VALUES(80.34, 'bar4');
INSERT INTO foo (id, data) VALUES('foo5', 'bar5');
INSERT INTO foo (data) VALUES('bar6');

UPDATE foo SET id=200, data='BARn' WHERE íd=6;

SELECT * FROM foo;

+----+------+
| id | data |
+----+------+
|  1 | bar1 |
|  2 | bar2 |
|  3 | bar3 |
|  4 | bar4 |
|  5 | bar5 |
|  6 | BARn |
+----+------+

Thanks!

Upvotes: 3

Views: 2834

Answers (3)

chava
chava

Reputation: 389

There is a way to reject certain columns sent by the user if that helps. As follows,

revoke insert, update on table foo from public;
grant insert (data), update (data) on table foo to public;

As shown, the first revoke must be on the entire table. If you simply try revoke insert (id) ....., it would be superseded by the existing more comprehensive grants on the table. So, it takes those two indicated steps.

Keep in mind that sending a forbidden column will result in an error.

Upvotes: 0

Tometzky
Tometzky

Reputation: 23890

You really should not do this.

You should program in such way that your code will not behave strange. After you insert something to database this data should be inserted or error raised.

You can enforce that serial will always be consistent by:

  • using an after insert trigger that checks if NEW.id=curval('foo_id_seq') and an after update trigger that checks if NEW.id=OLD.id and raising an error if this checks fail, but you excluded triggers for some strange reason;
  • disabling write access to this table for users other than superuser and creating a function for inserting data: 
    create function insert_to_foo(text) returns void as
$$ insert into foo(data) values ($1); $$
language sql volatile security definer
set search_path = public, pg_temp;

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

Not really. A trigger is the way to go here.

Well, you could use a conditional rule, too. But I wouldn't.
With check constraints you can only restrict values or combinations thereof you don't want, but that will raise an exception and skip the operation altogether.

Upvotes: 1

Related Questions