Update each row of a table with the corresponding value

Question

I have two Postgres tables:

create table A(
   id_A  serial not null,
   column_A  varchar  null;
   ...);

create table B(
   id_B  serial  not null,
   id_A  int4  not null,
   name  varchar  null,
   keywords  varchar  null,
   ...);

An element of table A is associated to multiple elements of table B and an element of table B is associated to one element of table A.

The column keywords in table B is a concatenation of values of columns B.name and A.column_A:

B.keywords := B.name || A.column_A

How to update with a trigger the column B.keywords of each row in table B if the value of A.column_A is updated?

In other words, I want to do something like this (pseudo-code):

FOR EACH ROW current_row IN TABLE B
   UPDATE B SET keywords = (SELECT B.name || A.column_A
                            FROM B INNER JOIN A ON B.id_A = A.id_A
                            WHERE B.id_B = current_row.id_B)
   WHERE id_B = current_row.id_B;

teppic · Accepted Answer · 2016-12-03T19:05:45.113

Your trigger has to call a function when A is updated:

CREATE OR REPLACE FUNCTION update_b()
  RETURNS TRIGGER
AS $$
BEGIN
  UPDATE B
  SET keywords = name || NEW.column_A
  WHERE id_A = NEW.id_A;
  return NEW;
END
$$ LANGUAGE plpgsql;


CREATE TRIGGER update_b_trigger AFTER UPDATE OF column_A
ON A
FOR EACH ROW
EXECUTE PROCEDURE update_b();

It might also be useful to add a trigger BEFORE INSERT OR UPDATE on table B to set the keywords.

score 0 · Answer 2 · edited May 23 '17 at 12:30

Your approach is broken by design. Do not try to keep derived values current in the table. That's not safe for concurrent access. All kinds of complications can arise. You bloat table B (and backups) and impair write performance.

Instead, use a VIEW (or a MATERIALIZED VIEW):

CREATE VIEW ab AS
SELECT B.*, concat_ws(', ', B.name, A.column_A) AS keywords
FROM   B
LEFT   JOIN A USING (id_A);

With the updated table definition below referential integrity is guaranteed and you can use [INNER] JOIN instead of LEFT [OUTER] JOIN.

Or even a simple query might be enough ...

Either way, you need a PRIMARY KEY constraint in table A and a FOREIGN KEY constraint in table B:

CREATE TABLE A (
   id_A     serial PRIMARY KEY,
   column_A varchar
   ...);


CREATE TABLE B (
   id_B  serial PRIMARY KEY,
   id_A  int4 NOT NULL REFERENCES A(id_A),
   name  varchar
   --  and *no* redundant "keywords" column!
   ...);

About concatenating strings:

How to concatenate columns in a Postgres SELECT?

And I wouldn't use CaMeL-case identifiers:

Are PostgreSQL column names case-sensitive?

Update each row of a table with the corresponding value

2 Answers2