Comment faire en sorte qu'une contrainte PostgreSQL ne s'applique qu'à une nouvelle valeur ?

Question

Je suis nouveau sur PostgreSQL et j'aime beaucoup la façon dont les contraintes fonctionnent avec la sécurité au niveau des lignes, mais je ne sais pas comment leur faire faire ce que je veux.

J'ai une colonne et je veux ajouter une contrainte qui crée une longueur minimale pour une colonne de texte, cette vérification fonctionne pour cela :

(length((column_name):: text) > 6)

MAIS, cela empêche également les utilisateurs de mettre à jour toutes les lignes où le nom de la colonne est déjà inférieur à 6 caractères.

Je veux faire en sorte qu'ils ne puissent pas modifier cette valeur pour cela, mais qu'ils puissent quand même mettre à jour une ligne où c'est déjà le cas, afin qu'ils puissent la modifier si nécessaire en fonction de ma nouvelle politique.

Est-ce possible ?

Answer 1

MAIS, cela empêche également les utilisateurs de mettre à jour toutes les lignes où le nom de la colonne est déjà inférieur à 6 caractères.

Eh bien, non. Quand vous essayez d'ajouter ce CHECK toutes les lignes existantes sont vérifiées, et une exception est levée si une violation est trouvée.
Vous devriez le faire NOT VALID . Alors oui.

Vous avez vraiment besoin d'un déclencher sur INSERT o UPDATE qui vérifie les nouvelles valeurs. Il n'est pas aussi bon marché et pas aussi efficace, mais il est tout de même solide. Comme :

CREATE OR REPLACE FUNCTION trg_col_min_len6()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF TG_OP = 'UPDATE'
   AND OLD.column_name IS NOT DISTINCT FROM NEW.column_name THEN
      -- do nothing
   ELSE
      RAISE EXCEPTION 'New value for column "note" must have at least 6 characters.';
   END IF;

   RETURN NEW;
END
$func$;

-- trigger
CREATE TRIGGER tbl1_column_name_min_len6
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW
WHEN (length(NEW.column_name) < 7)
EXECUTE FUNCTION trg_col_min_len6();

_db<>fidèle aquí_

Il serait plus efficace de vérifier dans un WHEN au déclencheur directement. La fonction de déclenchement n'est alors appelée que pour des valeurs courtes et peut être très simple.
Voir :

• Déclenchement avec plusieurs conditions WHEN
• Déclenchement de la mise à jour de la colonne A ou de la colonne B ou de la colonne C.

Answer 2

Vous pouvez créer des déclencheurs distincts pour l'insertion et la mise à jour, en laissant chacun d'eux définir complètement le moment où il doit être déclenché. Si une logique complètement différente est nécessaire pour l'action DML, cette technique permet d'écrire des fonctions de déclenchement dédiées. Dans ce cas, la fonction de déclenchement se résume à raise exception ... . Voir Démo

-- Single trigger function for both Insert and Delete
create or replace function trg_col_min_len6()
  returns trigger
  language plpgsql 
as $$
begin
   raise exception 'Cannot % val = ''%''. Must have at least 6 characters.'
                 , tg_op, new.val;
   return null;
end;
$$;

-- trigger before insert 
create trigger tbl_val_min_len6_bir
    before insert 
        on tbl
       for each row
      when (length(new.val) < 6)
      execute function trg_col_min_len6();

-- trugger before update 
create trigger tbl_val_min_len6_bur
    before update
        on tbl
       for each row    
      when (    length(new.val) < 6
            and new.val is distinct from old.val 
           ) 
     execute function trg_col_min_len6();