CODEWITHSUNDEEP
databasepostgresqltriggerslocking
v18o
v18o

Reputation: 1297

How to dual write to two columns in one table using Postgres trigger?

I want to change a column type in PostgreSQL. Let's say from INT to VARCHAR. The safe way to do it is the following:

  1. Add a new column new_<column>.
  2. Dual write to both columns (e.g., with a BEFORE INSERT/UPDATE trigger).
  3. Backfill the new column with a copy of the old column's values.
  4. Rename <column> to old_<column> and new_<column> inside a single transaction and explicit LOCK <table> statement.
  5. Drop the old column.

The question is how to create this BEFORE INSERT/UPDATE trigger? (2)

Upvotes: 1

Views: 422

Answers (1)

Rodolphe
Rodolphe

Reputation: 858

Use a simple table foo for this example

CREATE TABLE foo (id int, idstr varchar);

Create a simple function to assign a valur to your new column idstr

CREATE OR REPLACE FUNCTION bluegreen ()
        RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.idstr IS NULL THEN
    NEW.idstr = NEW.id::varchar;
  END IF;
  IF NEW.id IS NULL THEN
    NEW.id = NEW.idstr::int;
  END IF;
  RETURN NEW;
END;
$$;

And create a trigger on your table with the function previously created.

CREATE TRIGGER foo_trg
    BEFORE INSERT OR UPDATE ON foo
    FOR EACH ROW
    EXECUTE PROCEDURE bluegreen();

https://www.postgresql.org/docs/current/sql-createtrigger.html for details on triggers

Upvotes: 2

Related Questions