CODEWITHSUNDEEP
postgresql
user1397044
user1397044

Reputation: 12569

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

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:

  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.

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.

Upvotes: 13

Views: 29842

Answers (3)

kgrittn
kgrittn

Reputation: 19471

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

Upvotes: 5

Bob Folkerts
Bob Folkerts

Reputation: 376

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.

Upvotes: 0

vyegorov
vyegorov

Reputation: 22855

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

Upvotes: 17

Related Questions