postgresql 如何从基于其他列的值和选择在 postgres 中创建列？

How can I create a column in postgres from values and selections based on other columns?

提问by user1397044

I want to create a new field (or two) in my table that is a concatenation of other fields, which seems relatively straightforward. But what is the casesyntax or if/whensyntax I'd use to help create the following fields (GPA_TXT, and newfield)?

我想在我的表中创建一个新字段（或两个），它是其他字段的串联，这看起来相对简单。但是我用来帮助创建以下字段（, 和）的case语法或if/when语法是什么？GPA_TXTnewfield

The logic is: Each GPA should be #.#, each new field should be:

逻辑是：每个 GPA 应该是#.#，每个新字段应该是：

name & "-" & GPA_TXT & (
    case where GPA_TXT > 3.3
        set newfield = newfield & 'GradeA',
    case where GPA_TXT >2.7 and GPA_TXT < 3.3
        set newfield = newfield & "GradeB",
    etc...
)

For example:

例如：

name         major     GPA(num) GPA_TXT   [newfield]
Bob          sci       2        02.0      Bob-sci-GradeC-02.0
Jane         chem      3.1      03.1      Jane-chem-GradeB-03.1
Charlie      phys      3.7      03.7      Charlie-phys-GradeA-03.7
Garfield     food      0        00.0      Garfield-food-GradeF-00.0

So I guess I have two questions in here:

所以我想我在这里有两个问题：

1. How to create the GPA TXT field.
2. How to write a case statement to calculate a field according to the values in other fields.

1. 如何创建 GPA TXT 字段。
2. 如何编写case语句根据其他字段的值计算一个字段。

If anyone can link me to a resource with examples or explain I would greatly appreciate it! I'm looking through the documentation but not getting anywhere without examples.

如果有人可以将我链接到带有示例的资源或解释，我将不胜感激！我正在查看文档，但没有示例就无处可去。

回答by vyegorov

Important note: I would create a viewbased on your current table and avoided adding new columns, as they will denormalize your schema. Read more here.

重要说明：我会根据您当前的表创建一个视图并避免添加新列，因为它们会使您的架构非规范化。在这里阅读更多。

Also, I will use lowercase names for all the identifiers to avoid qouting.

此外，我将为所有标识符使用小写名称以避免 qouting。

• to form GPA_TXTfield you can use to_char()function: to_char(gpa, 'FM09.0')(the FMwill avoid space in front of the resulting string);

• for the second field, I would use GPAand not GPA_TXTfor numeric comparison. You can check more on CASEconstruct in the docs, but the block might be the following one:

  CASE WHEN gpa >= 3.3 THEN 'A'
       WHEN gpa > 2.7 AND gpa < 3.3 THEN 'B'
       WHEN gpa > 0 THEN 'C'
       ELSE 'F' END
  

• 到表格GPA_TXT字段可以使用to_char()功能：to_char(gpa, 'FM09.0')（在FM将避免在所得串的前部空间）;

• 对于第二个字段，我将使用GPA而 不GPA_TXT用于数字比较。您可以CASE在docs 中检查更多有关构造的信息，但该块可能是以下一个：

  CASE WHEN gpa >= 3.3 THEN 'A'
       WHEN gpa > 2.7 AND gpa < 3.3 THEN 'B'
       WHEN gpa > 0 THEN 'C'
       ELSE 'F' END
  

Sorry, I don't know how grades are assigned per GPA, please, adjust accordingly.

抱歉，我不知道每个 GPA 是如何分配成绩的，请相应调整。

The resulting query for the view might be (also on SQL Fiddle):

视图的结果查询可能是（也在SQL Fiddle 上）：

SELECT name,major,gpa,
       to_char(gpa, 'FM09.0') AS gpa_txt,
       name||'-'||major||'-Grade'||
  CASE WHEN gpa >= 3.3 THEN 'A'
       WHEN gpa > 2.7 AND gpa < 3.3 THEN 'B'
       WHEN gpa > 0 THEN 'C'
       ELSE 'F' END || '-' || to_char(gpa, 'FM09.0') AS adesc
  FROM atab;

To build a view just prepend CREATE VIEW aview ASbefore this query.

要构建视图，只需CREATE VIEW aview AS在此查询之前添加即可。

---

---

EDIT

编辑

If you still go for adding columns, the following should do the trick:

如果您仍然要添加列，以下应该可以解决问题：

ALTER TABLE atab ADD gpa_txt text, ADD adesc text;
UPDATE atab SET
    gpa_txt = to_char(gpa, 'FM09.0'),
    adesc = name||'-'||major||'-Grade'||
      CASE WHEN gpa >= 3.3 THEN 'A'
           WHEN gpa > 2.7 AND gpa < 3.3 THEN 'B'
           WHEN gpa > 0 THEN 'C'
           ELSE 'F' END || '-' || to_char(gpa, 'FM09.0');

回答by kgrittn

I recommend a "generated" column rather than storing the data redundantly. It will take less space on disk, which is likely to actually make it faster than storing the generated value, and will certainly be less prone to accidentally falling out of sync with the base data. Assuming you like the format provided by @vyegorov, You could create a function like this, using the record type of your table (which matches the table name) as input:

我建议使用“生成”列，而不是冗余存储数据。它将占用更少的磁盘空间，这实际上可能比存储生成的值更快，并且肯定不会意外地与基础数据不同步。假设您喜欢@vyegorov 提供的格式，您可以创建一个这样的函数，使用表的记录类型（与表名匹配）作为输入：

CREATE FUNCTION adesc(rec atab)
  RETURNS text
  IMMUTABLE
  LANGUAGE SQL
AS $$
SELECT to_char(.gpa, 'FM09.0') AS gpa_txt,
       .name||'-'||.major||'-Grade'||
  CASE WHEN .gpa >= 3.3 THEN 'A'
       WHEN .gpa > 2.7 AND .gpa < 3.3 THEN 'B'
       WHEN .gpa > 0 THEN 'C'
       ELSE 'F' END || '-' || to_char(.gpa, 'FM09.0') AS adesc;
$$;

You would need to reference this using a relation qualifier (the table name or an alias). When such a reference is not resolved by finding an actual column, PostgreSQL will look for a function taking the table's record type as its only parameter. So you would be able to do something like this:

您需要使用关系限定符（表名或别名）来引用它。当这样的引用没有通过查找实际列来解析时，PostgreSQL 将寻找一个以表的记录类型作为其唯一参数的函数。所以你可以做这样的事情：

SELECT name, major, gpa, atab.adesc
  FROM atab;

Such a "generated column" can be used in indexes for fast searching, if that's what you're after, with something like adesc(atab).*.

这样的“生成列”可用于索引中以进行快速搜索，如果这就是您所追求的，可以使用类似adesc(atab).*.

回答by Bob Folkerts

here is query that returns your values from a table with 3 columns:

这是从包含 3 列的表中返回您的值的查询：

select *
, to_char(gpa, '09.9') as gpa_text
, name || '-' || major || '-Grade' ||
case    when gpa between 3.5 and 4.0 then 'A'
    when gpa between 2.5 and 3.4 then 'B'
    when gpa between 1.5 and 2.4 then 'C'
    when gpa between 0.5 and 1.4 then 'D'
    else 'F' end
|| '-' || ltrim(to_char(gpa, '09.9')) as newfield
from students

This is working code, here is the newfield for Bob, "Bob-sci-GradeC-02.0"

这是工作代码，这是 Bob 的新字段“Bob-sci-GradeC-02.0”

I would strongly suggest that you do not have a text column in a database to hold a duplicate of a numeric value. I'm not quite sure why I need the ltrim, it seems odd that the formatted string would have a leading blank.

我强烈建议您在数据库中没有文本列来保存数值的重复项。我不太确定为什么我需要 ltrim，格式化的字符串有一个前导空白似乎很奇怪。