CODEWITHSUNDEEP
postgresql
MSA
MSA

Reputation: 1

Postgresql : Select only if more than one column is populated

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?

Upvotes: 0

Views: 158

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

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

Upvotes: 1

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31143

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;

Upvotes: 0

Related Questions