CODEWITHSUNDEEP
sqlpostgresqltimestamp
Maximus Decimus
Maximus Decimus

Reputation: 5291

Is there a way to add an Update Constraint on a Column when creating a Table in Postgresql?

I wanted to do this on a table in Postgresql 9.4 to update the column automatically on an update statement the way it works in MySQL 5.6 without a trigger?

create table test
(
id varchar(10),
updated timestamp not null default now() ON UPDATE now()
)

Postgresql gives an error on the word ON before UPDATE

ERROR: syntax error at or near "ON" SQL state: 42601 Character: 133

Upvotes: 0

Views: 373

Answers (1)

Belayer
Belayer

Reputation: 14936

Of course there is some overhead with triggers, but typically negligible. (The c# option would also have slight code overhead and slight network overhead, so probably a toss up). Also somewhat dependent on your definition of "huge volume of transactions per day". Is that 10s of thousands transactions per day or 10s of thousand transactions per second. Often huge is a relatively small number. The only way to know for sure is test it (make sure you capture total elapsed time, not just time in the database).

Trigger syntax in Postgres differs dramatically from MySql. In Postgres you create a special type function (returns trigger). The trigger itself is a single statement, execute function. With MySql the code is directly in the trigger definition (just like Oracle - in fact until Oracle bought it MySql did not support triggers.) The nice advantage here is that that single function can by used by any table that implements the same column name (at least in this case).

create or replace 
function set_updated_timestamp()
 returns trigger 
 language plpgsql
as $$
begin   
    new.updated = now();
    return new;
end;
$$;

See fiddle for full example. Note: Postgres version 9.4 is no longer supported, I suggest updating as soon as you can. For fiddle I used version 9.5 as it's the oldest version available. Also if fiddle is slow it is due to the "select sleep" statements NOT the trigger. I don't think "db<>fiddle" actually honors those statements.

Upvotes: 1

Related Questions