PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is there a way to keep a column immutable (insert-only) in jOOQ?

The values for some columns are only meant to be inserted and never modified (effectively complex foreign keys but not mapped as such for various reasons). In JPA, this can be modeled via @Column(updatable=false).

Is there a way to systematically (not manually) achieve this in jOOQ? I’m aware of the read-only columns, but immutable (insert-only) columns don’t seem to exist. Is that correct?

I know jOOQ is aware of JPA annotations in some contexts and since I do use the generated DAOs most of the time, I guess I could still use the annotation, but I’d prefer not to as I don’t use JPA anywhere else in the project.

Tags:

2

Answers


  1. 0

    Here’s a sample trigger that could ignore updates to the "x" column on a table named "foo"

    CREATE TRIGGER t_foo_bu
   BEFORE UPDATE ON foo
BEGIN 
   -- null safe equality check  
   IF NVL(new.x, chr(0)) != NVL(old.x, chr(0)) THEN
      DBMS_OUTPUT.PUT_LINE('Ignoring attempted update of foo.x from ' || old.x || ' to ' || new.x || ' for id ' || new.id); 
      new.x = old.x;
   END;
END;
    Login or Signup to reply.
  2. 0

    The trigger by @lance-java is completely invalid for Postgres (it’s Oracle perhaps). Postgres requires a function that returns trigger and a trigger definition invoking that function. However, the approach is appropriate; just replace the new value with the existing one (no reason to even look at the values). See demo.

    create or replace function make_x_immutable() 
   returns trigger 
   language plpgsql
as $$
begin 
   new.x = old.x;
   return new;
end; 
$$;

create trigger  foo_bur
    before update on foo
    for each row 
    execute function make_x_immutable() ;

    Note. Older versions of Postgres may require execute procedure … on the trigger definition.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search