当同一个表中的其他列DO / DONT为NULL时，查找列的MIN / MAX

Question

I have a few Postgres SELECT queries which is being pieced together according to form inputs via PHP. One of these is a SELECT query which looks to retrieve the min value within each field (I also have one which finds max).

e.g. for the min values:

SELECT
  MIN(col1) "Min_col1"
  MIN(col2) "Min_col2"
  MIN(col3) "Min_col3"
FROM table

Unfortunately, I have quite a lot of NULL values in the table, spread over all fields. To cope with this, I have added check boxes to the form which allows the user to select whether NULL values should be included/excluded as part of this query.

For example, if the user wanted to include NULL values for all columns:

SELECT
  MIN(col1) "Min_col1"
  MIN(col2) "Min_col2"
  MIN(col3) "Min_col3"
FROM table
WHERE
  (col1 IS NULL) AND
  (col2 IS NULL) AND
  (col3 IS NULL)

However, since each of the fields have at least one NULL value, the above query would return all NULLS.

What I would instead like is to be able to retrieve the MIN for each field, while either including/excluding NULL values for the other fields. e.g. MIN(col1) while either including/excluding NULLS for col2, col2 & col3 etc.

Whats the easiest way to go about this? Keep in mind that the query is being put together from form inputs via PHP, so would like to keep it as modular as possible...

Answer 1

for query below if null_only::boolean is false, returns min, else null:

SELECT
    case when not null_only then  MIN(col1) else null end "Min_col1"
  , case when not null_only then  MIN(col2) else null end "Min_col2"
  , case when not null_only then  MIN(col3) else null end "Min_col3"
FROM table
WHERE
  (col1 IS NULL) AND
  (col2 IS NULL) AND
  (col3 IS NULL)

here's working example for prepared statement:

t=# prepare q1 (boolean) as select case when not $1 then  MIN(oid) else null end col1 from pg_class;
PREPARE
Time: 10.905 ms
t=# execute q1(true);
 col1
------

(1 row)

Time: 6.216 ms
t=# execute q1(false);
 col1
------
  112
(1 row)

Time: 7.169 ms