Using IF in PostgreSQL

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

Answer

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.