GROUP BY consecutive rows where columns are equal or NULL

Question

In Postgres 9.2, I'm trying to group consecutive rows. They must have at least one non-null match, and no non-null mismatch. If all values are null then don't group together. Null can be thought of as a wildcard.

This is the expected result:
2, 4, 5 and 6 get grouped together because 2 and 4 share column1 (3 is all-null and skipped), 4 and 5 share column 3, 4 and 6 share column2 and column1.

Here's the SQL fiddle.

Answer 1

Another idea came to my mind which could be more dynamically concerning the number of columns. It's just an idea and I do not really know if it works. But it's worth a try.

Maybe you could pivot your table so that your colums become your rows:

https://www.postgresql.org/docs/9.1/static/tablefunc.html

http://www.vertabelo.com/blog/technical-articles/creating-pivot-tables-in-postgresql-using-the-crosstab-function

After that it should be easy to do the grouping or you could do a partition over the column content with a window function.

Just a sketch, could try it later.