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

Question

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!

Erwin Brandstetter · Accepted Answer · 2011-10-17 19:37:38Z

1

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.

edited Oct 17, 2011 at 19:37

answered Oct 17, 2011 at 19:11

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tometzky · Accepted Answer · 2011-10-18 09:03:59Z

1

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;

answered Oct 18, 2011 at 9:03

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

1 Comment

Andrew Thaddeus Martin Over a year ago

For the benefit of others readers who find this answer helpful, the function is named currval, not curval.

chava · Accepted Answer · 2019-07-07 19:38:10Z

0

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.

answered Jul 7, 2019 at 19:38

chava

4095 silver badges7 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related