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

Question

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 case syntax or if/when syntax I'd use to help create the following fields (GPA_TXT, and newfield)?

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

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:

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

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.

The conditions `GPA_TXT > 3.3` and `GPA_TXT > 2.7 and GPA_TXT < 3.3` exclude the exact value 3.3. Is this intended? — Erwin Brandstetter, Jul 08 '12 at 23:33

score 17 · Accepted Answer · edited May 23 '17 at 11:54

Important note: I would create a view based 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.

to form GPA_TXT field you can use to_char() function: to_char(gpa, 'FM09.0') (the FM will avoid space in front of the resulting string);
for the second field, I would use GPA and not GPA_TXT for numeric comparison. You can check more on CASE construct 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
```

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

The resulting query for the view might be (also on 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 AS before this query.

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');

Thanks! A quick follow up question -- I need to create table based on a second step -- a left join on that newly concatenated column to a different table. Will a view still work for that? — user1397044, Jul 06 '12 at 19:59
Sure. As soon as view is there, it is treated as a table for all further queries. It is just the way how such _table_ is internally scanned that differs: for ordinary table it's contents will be scanned and rows returned, for view it's SQL will be executed and rows will also be returned. In both cases PostgreSQL is smart enough to choose the best plan that suits you predicates, ordering and grouping conditions. — vyegorov, Jul 06 '12 at 20:04
+1 A lot of good advice. One minor gripe about the CASE statement: `WHEN gpa > 2.7 AND gpa < 3.3 THEN 'B'` is logically redundant and can be simplified to `WHEN gpa > 2.7 THEN 'B'`. — Erwin Brandstetter, Jul 08 '12 at 23:30
@ErwinBrandstetter, good notice, haven't thought of it. Thanks. I will keep statement as is though, for better visibility. — vyegorov, Jul 09 '12 at 05:07

kgrittn · Answer 2 · 2012-07-13T15:00:19.393

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:

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

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).*.

@user1397044: That's good advice about the generated column. You can find more explanation why this works in [this related answer](http://stackoverflow.com/a/11166268/939860). — Erwin Brandstetter, Jul 08 '12 at 23:31

score 0 · Answer 3 · answered Jul 06 '12 at 15:27

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

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"

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.

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

3 Answers3

Linked