0

There is a solution for Postgresql in this post. However, I need it for Redshift. The solution proposed for postgresql does not work for redshift as it says: column does not exist in . Is there any work around?

Given:

SELECT * FROM t;
┌────────┬────────┐
│   f1   │   f2   │
├────────┼────────┤
│ (null) │      1 │
│      2 │ (null) │
│ (null) │ (null) │
│      3 │      4 │
└────────┴────────┘
(4 rows)

Expected result:

┌────────┬────────┐
│   f1   │   f2   │
├────────┼────────┤
│ (null) │      1 │
│      2 │ (null) │
│ (null) │ (null) │
└────────┴────────┘
(3 rows)
kkk
  • 183
  • 1
  • 9
  • Hi and welcome to Stack Overflow, please read how to create a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) and also check [How to Ask Good Questions](https://stackoverflow.com/help/how-to-ask) so you increase your chances to get feedback and useful answers. – DarkCygnus Jul 13 '17 at 17:15

1 Answers1

1

You can convert all columns to varchar, concatenate and check if the resulting value is null. If any of the values in the row is null the whole concatenated result will be null. Just like this:

select *
from your_tablename
where col_1::varchar||col_2::varchar||col_3::varchar is null
AlexYes
  • 4,088
  • 2
  • 15
  • 23
  • It is an automated procedure, meaning that I do not know what columns are there in the table. – kkk Jul 13 '17 at 23:34
  • As you told there is a solution for Postgres and it is not working in Redshift, there is a whole lot more of incompatibilities between these two platforms. I don't think there is such function in Redshift, so I have offered a workaround. – AlexYes Jul 14 '17 at 11:05
  • p.s. I think it doesn't work because Postgres is a row based storage and Redshift is column based, that might be the reason why Postgres supports tuples like `select (col1,col2,col3)` and Redshift doesn't. And the Postgres solution is based on tuples - if at least one value in the tuple is null then the whole tuple is evaluated as null. – AlexYes Jul 14 '17 at 15:03