I would like to append some text to every cell in each column of my table to act as a symbol for that particular column. For example say my table is as follows (all fields are type character varying):
name age location james 45 france simon 33 usa ben 76 china
I would like to modify it to be:
name age location ajames b45 cfrance asimon b33 cusa aben b76 cchina
Does anyone have any suggestions as to how I can do this?
First you have to transform your age to be some kind of string. After that you can transform the values like this (of course you have to do this for each field):
update mytable set name = 'a' || name, age = 'b' || age;
This updates the data inside your table. If you only want the output to be prefixed you can use the following approach:
select 'a' || name as name, 'b' || age as age from mytable;
In this case there is no need to convert your age data type.
where column is not null), transform null into an empty string before concating, or whatever is best in your special case. There is no general solution.
The accepted answer is not handle null value and blank space. So I gave this answer. If it help someone, it will be my pleasure.
update "public"."mytable" set
"name"= case when "name" is null or trim("name")='' then null else 'a' || "name" end,
"age"= case when "age" is null or trim("age")='' then null else 'b' || "age" end,
"location"= case when "location" is null or trim("location")='' then null else 'c' || "location" end;
As of version 9.1, PostgreSQL provides a built-in concatenation function called CONCAT(). The Concat function in Postgres appends text to the end of the value and can handle blank values as well.
UPDATE mytable
SET name=Concat(name, 'a');
Hope this helps ;)
