PostgreSQL – MAX of Two or More Fields

Let’s say that we have a table of grades that students received during a school year. The columns are as follows:

  • student_id
  • quarter1
  • quarter2
  • quarter3
  • quarter4

If you had to display the highest grade that a student received for a quarter, how would you go about selecting the data? Your first thought may be to use a CASE statement. As you may know from experience, using this means that you have to write a longer query. Alternatively, as Thom Brown brought to my attention, you can use PostgreSQL’s GREATEST function. Assuming that we have a student table with the specified fields, we could use the following to retrieve the name of the student and that student’s best quarter grade:

SELECT students.first_name,
  students.last_name,
  GREATEST(
    grades.quarter1,
    grades.quarter2,
    grades.quarter3,
    grades.quarter4
  ) AS best_quarter_grade

As you may have guessed, there is also a LEAST function in PostgreSQL which will give you the ability to get the smallest value in a list of expressions.

Previous Solution

My solution to this prior to knowing about the GREATEST and LEAST functions dealt with the MAX, and UNNEST functions. The equivalent query for grabbing the best quarter grade is as follows:

SELECT students.first_name,
  students.last_name,
  (
    SELECT MAX(quarters)
    FROM unnest(ARRAY[
        grades.quarter1,
        grades.quarter2,
        grades.quarter3,
        grades.quarter4
      ]) quarters
  ) AS best_quarter_grade
FROM students
JOIN grades
ON students.id = grades.student_id

This works by converting the array of the four quarter grades into a set of rows. Next I select the maximum of those quarter grades and alias it as best_quarter_grade. Even though this approach works, I recommend using the GREATEST function because it requires less code and most-likely works more efficiently. 8)

PostgreSQL – Converting Rows Into A String

One thing that I often have to do is convert a subquery into a string that is delimited by a comma. For example, I may need to write a quick report that shows the users’ names, their login ID, and all of the roles that they fulfill. In this case, there may be three tables to reference: users, roles, and user_role_xref. In order to display the desired data for all enabled users, I can use the following query in PostgreSQL:

SELECT users.user_id,
  users.full_name,
  array_to_string(array(
    SELECT roles.title
    FROM user_role_xref, roles
    WHERE user_role_xref.user_id = users.id
    AND user_role_xref.role_id = roles.id
  ), ', ') AS roles
FROM users
WHERE users.enabled = TRUE;

It is really as simple as that. All you have to do is convert your subquery into an array and then use the array_to_string function to join each element element together with the second parameter used as the delimiter.

PostgreSQL – String To Rows

Working Solution – Custom Function

I have to say that using PostgreSQL has been great compared to MySQL. Recently, though, I ran into an issue while working with iReports designer. I needed to be able to accept a string of comma-delimited integers and then JOIN on those ID’s. Therefore, I started looking for a good string splitting function. At first I was unable to find a native one so I altered this plpgsql function made by pilcrow and added some documentation:

CREATE OR REPLACE FUNCTION split_to_rows(
	IN in_text TEXT,
	IN in_delimiter VARCHAR(20)
)
RETURNS SETOF TEXT AS $$
---------------------------------------------------------------------------
-- Function Name: split_to_rows
-- In-coming Params:
--   in_text [TEXT]
--   in_delimiter [VARCHAR(20)]
-- Out going Param:
--   TEXT
-- Description:
--   Splits the specified text with the specified delimiter and returns a
--   set of rows.
-- Created On: 2011-05-24
-- Author: pilcrow (http://stackoverflow.com/questions/1986491#1987113)
-- Modified By: Chris West
---------------------------------------------------------------------------
  DECLARE
    elems text[];      
  BEGIN
    elems := string_to_array(in_text, in_delimiter);
    FOR i IN array_lower(elems, 1) .. array_upper(elems, 1) LOOP
      RETURN NEXT elems[i];
    END LOOP;
    RETURN;
  END
$$ LANGUAGE 'plpgsql';

The following is an example of using this new function to convert a string of numbers into rows of numbers as integers.

SELECT CAST(split_to_rows('1,2,3,4,5,6', ',') AS INTEGER) AS id;

The Better Solution – Native PostgreSQL Function

After looking into other native solutions, I finally found the regexp_split_to_table function. This is basically called in the same way as pilcrow’s, but with one major difference: you can use regular expressions to split the string into rows. Here is an example of using this new function in the same was as I did the split_to_rows function:

SELECT CAST(regexp_split_to_table('1,2,3,4,5,6', ',') AS INTEGER) AS id;