postgresql: exclude data based on other incomplete data

Question

In this data - there are multiple DATA_ID values associated with time-series data. I am trying to exclude all data from any DATA_ID values that return a NULL value for USE for any timestamp value.

In other words, I only want to return DATA_ID values (and their data) if they have complete (not any NULL) values for all timestamp values.

Sample query given below:

SELECT   
My.Table.DATA_ID,   
MY.Table.timestamp,   
My.Table.USE

FROM   
My.TABLE

WHERE timestamp BETWEEN '2012-06-01 00:00:00' AND '2012-06-02 23:59:59'

-- Something here that says exclude all data from DATA_ID(s)
-- with any missing USE data,   i.e. USE=NULL

ORDER BY DATA_ID, timestamp

You really should include your schema and some sample data, so people aren't left guessing about the data. I am guessing that DATA_ID is a number of some kind, timestamp is a timestamp value, and USE is a ... what? Is that right? Or is DATA_ID actually an array or something? Also, your column names are very very poorly chosen; one is a SQL keyword USE, one is a SQL data type name timestamp. Please show the real table. — Craig Ringer
– Craig Ringer, Commented Aug 25, 2012 at 1:46
It'll really help if you edit your question to add some sample data and an example of your expected results. — Craig Ringer
– Craig Ringer, Commented Aug 25, 2012 at 1:48
Your question also contradicts its self. In the text you say you want to skip data_id values if they have non-null values for all timestamps. Then in the example you say you want to exclude all data from data_ids with missing USE values. These appear to be different things. — Craig Ringer
– Craig Ringer, Commented Aug 25, 2012 at 1:53

eevar · Accepted Answer · 2012-08-25 12:25:13Z

1

Assuming I understand your question correctly and you want to exclude whole batches of samples (determined by equal data_id and timestamp) that contain a null value.

SELECT   
My.Table.DATA_ID,
MY.Table.timestamp,
My.Table.USE

FROM
My.TABLE o

WHERE timestamp BETWEEN '2012-06-01 00:00:00' AND '2012-06-02 23:59:59'

and not exists (select 1 from my_table i
  where i.use is null
  and i.data_id = o.data_id
  and i.timestamp BETWEEN '2012-06-01 00:00:00' AND '2012-06-02 23:59:59')

ORDER BY DATA_ID, timestamp

answered Aug 25, 2012 at 12:25

eevar

3,6891 gold badge21 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Travers · Accepted Answer · 2012-08-25 10:11:34Z

The simple thing to do is something like this:

CREATE FUNCTION missing_info(MY.TABLE) 
RETURNS BOOL 
LANGUAGE SQL AS
$$ select $1.use is null -- chain conditions together with or. 
   -- no from clause needed.  no where clause needed.
$$;

Then you can just add:

where (My.Table).missing_info is not true;

And as you need to change the logic as to what sorts of info is missing you can just change it in the function and everything still works.

This is the sort of encapsulation of derived information where ORDBMS's like PostgreSQL really shine.

Edit: Re-reading your example, it looks like what you are looking for is the IS NULL operator. However if you need to re-use some sort of logic, see the above example. NULL never "equals" NULL (because we can't say whether two unknown values are the same). But IS NULL tells you whether it is NULL or not.

Collectives™ on Stack Overflow

postgresql: exclude data based on other incomplete data

Sample query given below:

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Sample query given below:

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related