1. 首页
  2. 其他

PostgreSQL 创建一个新列,其值以其他列为条件

本文介绍了PostgreSQL 创建一个新列,其值以其他列为条件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我使用 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生存(天) 列值.一旦完成,数据库表将如下所示:

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 Survival 条件值的伪代码类似于:

The pseudo code to input the conditioned values of 1-yr Survival would be something like:

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即可实现一次性操作:

The one-time operation can be achieved with a plain 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.

您知道您可以使用 <a href="http://www.postgresql.org/docs/current/interactive/sql-copy 将查询结果导出为 CSV.html” rel=”noreferrer” target=”_blank”>复制?
示例:

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 创建一个新列,其值以其他列为条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,WP2

PostgreSQL 创建一个新列,其值以其他列为条件 为WP2原创文章,链接:https://www.wp2.cn/other/postgresql-%e5%88%9b%e5%bb%ba%e4%b8%80%e4%b8%aa%e6%96%b0%e5%88%97%ef%bc%8c%e5%85%b6%e5%80%bc%e4%bb%a5%e5%85%b6%e4%bb%96%e5%88%97%e4%b8%ba%e6%9d%a1%e4%bb%b6/