How do I add a concatenated column to existing table?

Question

I am trying this expression, but I do not find where is wrong my syntax:

alter table "TableName"
add column "NameColumn" as concat(ColumnA,ColumnB,ColumnC)

All three fields are character varying.

Answer 1

What is wrong is that you want an update after adding the column:

alter table "TableName" add column "NameColumn" varchar(255);  -- or whatever

update "TableName
    set "NameColumn" = concat(ColumnA, ColumnB, ColumnC);

Postgres doesn't directly support computed columns. There are various more cumbersome workarounds, some suggested here by Erwin Brandstetter.

Answer 2

This doesn't precisely answer your question, but I think it would accomplish the same thing. There is, of course, overhead with triggers, but short of creating a view instead I'm not sure how else to accomplish this when DML occurs.

CREATE OR REPLACE FUNCTION tablename_insert_update_trigger()
  RETURNS trigger AS
$BODY$
BEGIN
  new."NameColumn" = concat(ColumnA, ColumnB, ColumnC);
  return new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

And then set that trigger any time your table is modified:

CREATE TRIGGER insert_tablename_trigger
  BEFORE INSERT
  ON "TableName"
  FOR EACH ROW
  EXECUTE PROCEDURE tablename_insert_update_trigger();

CREATE TRIGGER update_tablename_trigger
  BEFORE UPDATE
  ON "TableName"
  FOR EACH ROW
  EXECUTE PROCEDURE tablename_insert_update_trigger();

That said, I think in most cases @Nicarus's suggestion of a view is the way to go. The one alternative where the trigger would be preferable is if the function is actually quite complex (computationally expensive) and the DML occurs either infrequently or during non-peak hours, whereas the reads occur frequently and/or during peak hours.

Your example was a simple concat, but it's possible that was a notional example for the purpose of illustrating the concept, so take this for what it's worth.