PostgreSQL: Select where timestamp is empty

Question

I have a query that looks like the following:

SELECT * FROM table WHERE timestamp = NULL;

The timestamp column is a timestamp with time zone data type (second type in this table). This is in PostgreSQL 8.4.

What I'm trying to accomplish is to only select rows that have not had a timestamp inserted. When I look at the data in pgAdmin the field is empty and shows no value. I've tried where timestamp = NULL, 'EPOCH' (which you would think would be the default value), a valid timestamp of zeros (0000-00-00 00:00:00-00, which results in a out of range error), the lowest date possible according to the docs (January 1, 4713 BC) and a blank string ('', which just gets a data type mismatch error). There also appears to be no is_timestamp() function that I can use to check if the result is not a valid timestamp.

So, the question is, what value is in that empty field that I can check for?

Thanks.

EDIT: The field does not have a default value.

Ermiya Eskandary · Accepted Answer · 2022-05-17 18:12:40Z

117

null in SQL means 'unknown'.

This means that the result of using any comparison operator, like =, with a null is also 'unknown'.

To check if a column is NULL (or not NULL), use the special syntax of IS NULL (or IS NOT NULL) instead of using =.

Applying that to your statement,

SELECT * FROM table WHERE timestamp IS NULL;

should work.

edited May 17, 2022 at 18:12

Ermiya Eskandary

24.4k4 gold badges58 silver badges72 bronze badges

answered Dec 18, 2010 at 1:55

XMen

30.3k41 gold badges103 silver badges152 bronze badges

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

3 Comments

Scott Marlowe Over a year ago

I think he wants where timestamp IS NULL (no not) but yeah, nothing can equal null since null is unknown.

ReK_ Over a year ago

No, that would just select all timestamps. However you did inadvertently give me the answer: you need to use timestamp IS NULL, not timestamp = NULL, so thanks.

Alkanshel Over a year ago

This doesn't retrieve the record for me if the timestamp is blank.

Collectives™ on Stack Overflow

PostgreSQL: Select where timestamp is empty

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related