Using IF in PostgreSQL

Question

I need to check (here for an example) if the average earnings of DE workers are higher than IT workers. I tried using

SELECT IF(
(SELECT AVG(earnings) FROM workers WHERE country LIKE 'DE') > 
(SELECT AVG(earnings) FROM workers WHERE country LIKE 'IT'),
'True', 'False');

but it doesn't seem to work. Is there any way to do this in PostgresSQL (I am using version 14)

first_name	last_name	country	earnings
Andrea	Pfeiffer	DE	800
Eufrosina	Marchesi	IT	2975
Elisa	Sabbatini	IT	2450
Marco	Grunewald	DE	3000
Doreen	Kalb	DE	5000
Isidoro	Bruno	IT	1100
Lucas	Mueller	DE	3000
Ausonio	Loggia	IT	1300

sticky bit · Accepted Answer · 2022-01-05 16:27:39Z

1

Postgres knows a Boolean type and Boolean expressions will evaluate to a value of that type. So you could simply SELECT the expression.

SELECT (SELECT avg(earnings)
               FROM workers
               WHERE country = 'DE')
       > 
       (SELECT avg(earnings)
               FROM workers
               WHERE country = 'IT');

Or, if you explicitly need strings, use a CASE expression.

SELECT CASE
         WHEN (SELECT avg(earnings)
                      FROM workers
                      WHERE country = 'DE')
              > 
              (SELECT avg(earnings)
                      FROM workers
                      WHERE country = 'IT') THEN
           'True'
         ELSE
           'False'
       END;

And you could even use Postgres' FILTER to get rid of the sub selects. (That might be a bit faster.)

For a Boolean:

SELECT avg(earnings) FILTER (WHERE country = 'DE')
       >
       avg(earnings) FILTER (WHERE country = 'IT')
       FROM workers;

For a string:

SELECT CASE
         WHEN avg(earnings) FILTER (WHERE country = 'DE')
              >
              avg(earnings) FILTER (WHERE country = 'IT') THEN
           'True'
         ELSE
           'False'
       END
       FROM workers;

Instead of FILTER you could also use a CASE expression as argument to avg() with the same effect.

avg(earnings) FILTER (WHERE country = 'DE')

would become

avg(CASE
      WHEN country = 'DE' THEN
        earnings
    END)

(and analog for 'IT'). That would also work in many other DBMS, not just Postgres.

edited Jan 5, 2022 at 16:27

answered Jan 5, 2022 at 16:04

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Dominik Czekański Over a year ago

Thank you. I didn't think it could be written down so simply. The second expression is exactly what I needed. As I understand it FILTER is used only in PostgreSQL so I can't use it in MySQL for example?

sticky bit Over a year ago

@DominikCzekański: Yes, FILTER is a Postgres only thing. At least I didn't encounter it anywhere else yet.

sticky bit Over a year ago

@DominikCzekański: But in many other DBMS you could use (yet another) CASE expression to get something with the same effect of FILTER. I edited the answer to include that as well.

wildplasser Over a year ago

@stickybit looking at the documentation, FILTER doesn't seem to be a Postgres extension (it is a keyword in ANSI-SQL, and there is no NOTE in the aggregate documentation) Maybe Postgres is the only DBMS that actually implemented it?

Frank Heikens Over a year ago

Filter is standard SQL, but not implemented by all types of databases. modern-sql.com/feature/filter

|

Collectives™ on Stack Overflow

Using IF in PostgreSQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related