PostgreSQL创建一个新列,其值以其他列为条件 [英] PostgreSQL create a new column with values conditioned on other columns
问题描述
我使用PostgreSQL 9.1.2,我有一个基本表,如下所示,其中我具有布尔值(Survival)
的条目的生存状态,以及数量的天数(生存(天数))
。
I use PostgreSQL 9.1.2 and I have a basic table as below, where I have the Survival status of an entry as a boolean (Survival)
and also in number of days (Survival(Days))
.
我手动添加了一个名为 1-yr Survival
的新列,现在我想填写表格中每个条目的此列,取决于该条目的 Survival
和 Survival(Days)
列值。一次,完成后,数据库表将如下所示:
I have manually added a new column named 1-yr Survival
and now I want to fill in the values of this column for each entry in the table, conditioned on that entry's Survival
and Survival (Days)
column values. Once , completed the database table would look something like this:
Survival Survival(Days) 1-yr Survival
---------- -------------- -------------
Dead 200 NO
Alive - YES
Dead 1200 YES
用于输入 1-yr生存
类似于:
ALTER TABLE mytable ADD COLUMN "1-yr Survival" text
for each row
if ("Survival" = Dead & "Survival(Days)" < 365) then Update "1-yr Survival" = NO
else Update "1-yr Survival" = YES
end
我相信这是一项基本操作,但是我无法找到要执行的postgresql语法。一些搜索结果返回添加触发器,但是我不确定这是我所需要的。我认为我的情况要简单得多。任何帮助/建议将不胜感激。
I believe this is a basic operation however I failed to find the postgresql syntax to execute it. Some search results return "adding a trigger", but I am not sure that is what I neeed. I think my situation here is a lot simpler. Any help/advice would be greatly appreciated.
推荐答案
一次性操作只需使用 UPDATE
:
UPDATE tbl
SET one_year_survival = (survival OR survival_days >= 365);
我建议不要在名称中使用驼峰式,空格和括号。尽管允许在双引号之间,但它经常导致复杂和混乱。考虑有关手册中标识符和关键字的一章。
I would advise not to use camel-case, white-space and parenthesis in your names. While allowed between double-quotes, it often leads to complications and confusion. Consider the chapter about identifiers and key words in the manual.
您知道您可以使用 COPY
?
示例:
Are you aware that you can export the results of a query as CSV with COPY
?
Example:
COPY (SELECT *, (survival OR survival_days >= 365) AS one_year_survival FROM tbl)
TO '/path/to/file.csv';
开始时,您不需要冗余列。
You wouldn't need the redundant column this way to begin with.
为避免出现空更新:
UPDATE tbl
SET "Dead after 1-yr" = (dead AND my_survival_col < 365)
,"Dead after 2-yrs" = (dead AND my_survival_col < 730)
....
WHERE "Dead after 1-yr" IS DISTINCT FROM (dead AND my_survival_col < 365)
OR "Dead after 2-yrs" IS DISTINCT FROM (dead AND my_survival_col < 730)
...
就我个人而言,如果我有令人信服的理由,我只会添加这样的冗余列。通常我不会。如果是关于性能:您是否了解关于表达式和部分索引的索引?
Personally, I would only add such redundant columns if I had a compelling reason. Normally I wouldn't. If it's about performance: are you aware of indexes on expressions and partial indexes?
这篇关于PostgreSQL创建一个新列,其值以其他列为条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!