Boolean type in Postgres does not work as expected

Question

I'm using Ubuntu 16.04 and psql (PostgreSQL) 9.4.14.

I just encountered a really weird case. The sum of three states of boolean is not equal to the whole count.

This is deleted field attribute default as false.

I tried:

The whole count:

SELECT count(*) from table; the count -> 9000

The un-deleted count:

SELECT count(*) from table where deleted; the count -> 400;

Same as:

SELECT count(*) from table where deleted = true; the count -> 400;

The deleted count:

SELECT count(*) from table where not deleted; the count -> 0;

Same as:

SELECT count(*) from table where deleted <> true; the count -> 0;

And the null case is:

SELECT count(*) from table where deleted = null; the count -> 0;

I checked the official doc.

PostgreSQL provides the standard SQL type boolean. The boolean type can have several states: "true", "false", and a third state, "unknown", which is represented by the SQL null value.

But as you can see, that final sum from the three states cannot be 9000.

So surprisingly annoyed.

Please share some advice for this, thank you!

SELECT count(*) from info_security_group where deleted IS null;, not equal — Vao Tsun
– Vao Tsun, Commented Mar 28, 2018 at 8:05
That's one of the reasons I always define boolean columns as NOT NULL with false as the default value — user330315
– user330315, Commented Mar 28, 2018 at 8:35

Tim Biegeleisen · Accepted Answer · 2018-03-28 08:05:57Z

5

You need to use IS NULL to compare a column against NULL:

select count(*) from info_security_group where deleted is null;

My guess is that this will return a count of 8600.

answered Mar 28, 2018 at 8:05

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

Boolean type in Postgres does not work as expected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related