CODEWITHSUNDEEP
sqlpostgresql
user2889419
user2889419

Reputation:

Query for retrieving columns with null value

Considering the following table

+-----+-----+-----+-----+-----+-----+
|Col01|Col02|Col03|Col04|Col05|Col06|   <===header
+-----+-----+-----+-----+-----+-----+
|data1|data2|NULL |hi   |Hello|Folks|   <===a row
+-----+-----+-----+-----+-----+-----+

Now I would like to have a query which returns the name of column ( in this case COL03) or the column number as result which has a null value.

How can I achieve this in PostgreSQL?

Upvotes: 0

Views: 72

Answers (3)

user275683
user275683

Reputation:

This will return row for each column in your table that has null value. 

SELECT 'Col1' FROM MyTable WHERE col1 IS NULL
UNION ALL 
SELECT 'Col2' FROM MyTable WHERE col2 IS NULL
UNION ALL 
SELECT 'Col3' FROM MyTable WHERE col3 IS NULL

Upvotes: 1

roman
roman

Reputation: 117347

Something like this:

select 'col1' from Table1 where col1 is null
union all
select 'col2' from Table1 where col2 is null
....
union all
select 'colN' from Table1 where colN is null

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156928

Would something like this do?

select case
       when col01 is null
       then 'col1'
       when col02 is null
       then 'col2'
       when col03 is null
       then 'col3'
       when col04 is null
       then 'col4'
       when col05 is null
       then 'col5'
       else null
       end
       which_col
from   tableX

If you want to construct this dynamically, I think you have to go procedural. You can get the name of the columns by querying information_schema.columns. Then create a query like this using that information.

Upvotes: 3

Related Questions