Postgresql insert trigger to set value

by

You are correct that you need a trigger, because setting a default value for the column won’t work for you – default values only work for null values and don’t help you in preventing blank values.

In postgres there are a couple of steps to creating a trigger:

Step 1: Create a function that returns type trigger:

CREATE FUNCTION my_trigger_function()
RETURNS trigger AS $$
BEGIN
  IF NEW.C1 IS NULL OR NEW.C1 = '' THEN
    NEW.C1 := 'X';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql

Step 2: Create a trigger that invokes the above function and fires BEFORE INSERT, which allows you to change the incoming values before they hit the table:

CREATE TRIGGER my_trigger
BEFORE INSERT ON T
FOR EACH ROW
EXECUTE PROCEDURE my_trigger_function()

And you’re done.

See the above code executing on SQLFIddle.

You mention in a comment that the value 'X' is retrieved from a subquery. If so, change the relevant line so something like:

NEW.C1 := (select some_column from some_table where some_condition);

More Related Contents:

  1. How to exit from PostgreSQL command line utility: psql
  2. Generating time series between two dates in PostgreSQL
  3. INSERT with dynamic table name in trigger function
  4. Passing user id to PostgreSQL triggers
  5. I forgot the password I entered during postgres installation
  6. How do I get a column with consecutive, increasing numbers, without having any numbers missing?
  7. Generate series of dates – using date type as input
  8. Refresh a materialized view automatically using a rule or notify
  9. Test for null in function with varying parameters
  10. Postgres query optimization (forcing an index scan)
  11. Dynamically generate columns for crosstab in PostgreSQL
  12. I forgot the password I entered during PostgreSQL installation
  13. PostgreSQL ERROR: canceling statement due to conflict with recovery
  14. Change type of varchar field to integer: “cannot be cast automatically to type integer”
  15. How to add column if not exists on PostgreSQL?
  16. ERROR: unterminated quoted string at or near
  17. Is there a way to disable updates/deletes but still allow triggers to perform them?
  18. PostgreSQL – set a default cell value according to another cell value
  19. now() default values are all showing same timestamp
  20. Convert escaped Unicode character back to actual character in PostgreSQL
  21. How do I temporarily disable triggers in PostgreSQL?
  22. What to do with null values when modeling and normalizing?
  23. Postgres: INSERT if does not exist already
  24. How to close idle connections in PostgreSQL automatically?
  25. postgresql – replace all instances of a string within text field
  26. Is it possible to have autoincrement number without it being an ID?
  27. function returns multiple columns as a single column instead of multiple columns
  28. Dynamic pivot query using PostgreSQL 9.3
  29. In PostgreSQL, how to insert data with COPY command?
  30. Check if NULL exists in Postgres array

Leave a Comment