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