How to write pgsql update query with string aggregate?

Question

I have update query that will manually change the field value as a unique string, the table already have a lost of data and the id as unique Pkey.

So I need the names should look like

mayname-id-1, 
mayname-id-2, 
mayname-id-3,     etc

I tried to update with string_agg, but that doesn't work in update queries

UPDATE mytable 
SET name = string_agg('mayname-id-', id);

How to construct string dynamically in an update query?

Answer 1

How about the following:

UPDATE mytable 
SET name = 'mayname-id-' || CAST(id AS text)

Answer 2

Typically, you should not add such a completely redundant column at all. It's cleaner and cheaper to generate it as functionally dependent value on the fly. You can use a view or a "generated column" for that. Details:

• Store common query as column?

You can even have a unique index on such a functional value if needed.

Answer 3

Use string concatenation

UPDATE mytable SET name = 'nayname-id-' || (id :: text);