根据 PostgreSQL 中的列将文本附加到列数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15293778/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Append text to column data based on the column in PostgreSQL
提问by James Elder
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?
有没有人对我如何做到这一点有任何建议？
回答by Sebastian vom Meer
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.
在这种情况下，无需转换您的年龄数据类型。
回答by Nripendra Ojha
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;
