These are my column names in a table called st:
st4stat
st8stat
st1stat
st11stat
st6stat
st10stat
st2stat
st7stat
st12stat
I need to make a select (pick their values) only when more than ONE of these columns are not null. I can't state any possible combination. Is there any quick way for this?
Cast each test to integer:
where
(col1 is not null)::int +
(col2 is not null)::int +
(col3 is not null)::int +
(col4 is not null)::int
> 1
I don't know any nice way to do it without creating a function (or several), but there are a couple of not-so-pretty ways.
You could do it with an ugly
SELECT * from ROW
WHERE (CASE WHEN col2 is null THEN 0 ELSE 1 END +
CASE WHEN col2 is null THEN 0 ELSE 1 END +
CASE WHEN col3 is null THEN 0 ELSE 1 END +
...) > 1;
We can also do it a bit more nicely with arrays if the columns have the same datatype:
SELECT * from ROW
WHERE array_length(array_remove(ARRAY[col1, col2, col3, col4, ...], NULL), 1) > 1;
This makes an array of the column values, then removes all NULLs and if there are more than one value left in the array then the row is printed.
If the columns have different types then we can use IS NULL to take the booleans and do the same then:
SELECT * from ROW
WHERE array_length(array_remove(ARRAY[col1 IS NULL, col2 IS NULL, col3 IS NULL, col4 IS NULL, ...], true), 1) > 1;