Transcript

"Greatest" and "least" are conditional statements in Postgres that accept a list of arguments, returning the largest or smallest value from the arguments.

Let's take a look at how this works.

For this example, I have a table called users with a few entries.

\d users

select * from users;

From inspecting the table, you'll notice that we have a few date columns on our users, sign up date, last post date and last comment date.

If we wanted to read from our users table and find something like a last activity date, we can use "greatest," passing it each of our date columns.

select name, greatest(sign_up_date, last_post_date, last_comment_date) as last_activity_date from users;

You'll notice that all of our users have a last activity date, even though our last user, named "Dana" didn't have a value for last comment date. This is because the "greatest" and "least" statements ignore null values in their argument lists. The methods will only return null if all of the values in the argument list are null.

We can see this if we create a new user, and then execute our same select statement from earlier.

insert into users (name) values ('Ed');

select name, greatest(sign_up_date, last_post_date, last_comment_date) as last_activity_date from users;

The "least" command works in the exact same way as "greatest", accepting a list of values and returning the smallest of the values. In the case of our data, we can do something silly like select the earliest activity date from our users using "least" and passing it our date columns.

select least(sign_up_date, last_comment_date, last_post_date) as earliest_activity_date from users;

It's important to note that the greatest and least commands are very different from the max and min aggregate commands in Postgres. Greatest and least compute a different value for each row being read out, and accept a list of values. Max and min compute a single result based on all the values from the rows being read out, and only accept a single argument.

So using max with last post date as latest post from our users reads in the last post date from each one of our user rows, and returns the greatest of all the last post dates from all of our users.

select max(last_post_date) as latest_post from users;

This is very different from using greatest with last post date and last comment date to find the latest post from our users. Greatest returns the latest post per user.

select name, greatest(last_post_date, last_comment_date) as latest_post from users;

Thanks for watching!

Setup

create table users (
  id serial primary key,
  name varchar not null,
  last_post_date date,
  last_comment_date date,
  sign_up_date date
);

insert into users (name, last_post_date, last_comment_date, sign_up_date)
values ('Amy', '2019-10-05', '2019-11-04', '2019-09-10'),
('Brad', '2019-10-20', '2019-08-11', '2019-07-23'),
('Chad', '2019-11-03', '2019-11-02', '2019-10-31'),
('Dana', '2019-09-29', null, '2019-08-04');