Postgresql – Remove first all null records from first part of time series until one value is not null and return the rest, in PostgreSQL

postgresql

I have a table that has something like this:

dt              col1 col2
2000-01-01      null null
2000-01-02      null null
2000-01-05      null null
2000-01-10      null 100
2000-01-11      1    2
2000-01-12      null 3
2000-01-14      null null
2000-01-20      1    1
2000-01-21      null null

Note it has an unpredictable number of records and in some cases I want to query the table and trim off the records that are earliest in the time series that are all null that occur prior to the first record that has at least one value. In this example I want to remove records 2000-01-01 through 2000-01-05. Note that I want to leave all records whether all values are null or not after the first one is found that has a non-null value starting from oldest date forward.

So my desired result would be:

dt              col1 col2
2000-01-10      null 100
2000-01-11      1    2
2000-01-12      null 3
2000-01-14      null null
2000-01-20      1    1
2000-01-21      null null

This doesn't work because it removes all records that have all null values:

select 
    * 
from 
    tbl
where
    col1 is not null 
    or col2 is not null;

Is there a way to solve this without changing the table at all (doing it all in a query)?

My alternative is to iterate through the records in a program that this query is needed. I'm thinking SQL might be better.

I'm using PostgreSQL 9.6.

Best Answer

select * from tbl
where dt>= (select min(dt) from tbl where col1 is not null or col2 is not null)