postgresql 如何在 sql 查询中添加具有默认值的自定义列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26538485/
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
How to add a custom column with a default value in an sql query?
提问by Nona
So I'm doing a basic join query in SQL (postgres)...
所以我在 SQL (postgres) 中做一个基本的连接查询......
SELECT first_name, last_name
FROM table1, table2
WHERE table1.id = table2.customer_id
In the returned result query is it possible to generate an extra column called "default_value" with a string value of "test" in each row returned, and if so how? I'm not trying to permanently alter the tables with new data, just add an extra column in the result set. The use case is to store the sql query in a Heroku dataclip so I can generate a csv report.
在返回的结果查询中，是否可以在返回的每一行中生成一个名为“default_value”的额外列，字符串值为“test”，如果是这样，如何？我不想用新数据永久更改表，只需在结果集中添加一个额外的列。用例是将 sql 查询存储在 Heroku 数据剪辑中，以便我可以生成 csv 报告。
回答by a_horse_with_no_name
Yes, that's quite easy:
是的，这很容易：
select first_name,
last_name,
'test' as default_value, --<< a "virtual" column containing a character value
42 as the_answer --<< another column containing a numeric value
from table1
join table2 on table1.id = table2.customer_id;
You should also stop using those outdated implicit joins in the WHEREclause. Use an explicit JOINoperator instead. It makes the queries more robust against accidentally forgotten join conditions.
您还应该停止在WHERE子句中使用那些过时的隐式连接。请改用显式JOIN运算符。它使查询对于意外忘记的连接条件更加健壮。
回答by Varje
"Is there any option to put a condition on the virtual column's value?"
“是否有任何选项可以对虚拟列的值设置条件？”
select first_name,
last_name,
CASE WHEN first_name = 'Mary' THEN 'test' WHEN first_name = 'John' THEN 'test2'
ELSE 'Not known' END as default_value,
42 as the_answer
from table1
join table2 on table1.id = table2.customer_id;
