3

I have column with type BOOLEAN

At first (on INSERT), value would be always FALSE, only after may be updated column as TRUE

So question is: make this column NOT NULL DEFAULT FALSE, or make this column DEFAULT NULL and then update as TRUE (and if then also would be reversing update, set column NULL and not FALSE value, so in this case, never would be used FALSE value )

Which choice will be better point of view performance and storage saving ?

Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
  • In answer to the storage question you may find value here: http://stackoverflow.com/questions/4229805/how-much-disk-space-is-needed-to-store-a-null-value-using-postgresql-db and http://www.postgresql.org/docs/9.1/static/datatype-boolean.html –  Dec 21 '15 at 17:09

3 Answers3

5

It makes no meaningful difference to storage. Depending on other nullable columns, we're talking at most a single byte if this column happens to overflow the nullable bitmap to the next byte, but in all likelihood it won't add any storage at all.

For performance (as well as programmer productivity/maintenance), it depends a lot on how you plan to use this. There's nowhere near enough information here, nor would it be possible to get enough info without inviting anyone who actually reads this question to all of your project meetings.

Personally, when I have a boolean column that is false by default (however you choose to represent it) and then will at some point become true and stay true, I like use a nullable DateTime column for this value, where any date at all means the value is true, and NULL means false. My experience is that you'll almost always eventually want to know the date and time on which the value became true.

As an example of this, I work in Higher Ed. One thing that happens in this business is sending financial aid award letters to students. Students then sign and return the letters. Accepted awards may later be declined or superceeded, but that won't change the fact that the student had signed this letter and accepted this award. Regulations require us to keep these letters on file, but from a database standpoint it was good enough just to know the boolean question of whether awards in the letter are accepted. It quickly became apparent that we also need to know when the awards are accepted. Thus, a datetime column is used for that field.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Thank you, also according to other answers here, I choice to use `NOT NULL DEFAULT FALSE`, this really will be more clear. About saving date, I also save every update date but with another table, because of needed report of all event (status change) by date. – Oto Shavadze Dec 21 '15 at 17:39
3

If you want to assume FALSE unless specifically set to TRUE, then use NOT NULL DEFAULT FALSE. It also depends on how you are using this column. If you only test where column = TRUE, then don't think it will matter. The point being that NULL is not equal to FALSE.

DBug
  • 2,502
  • 1
  • 12
  • 25
3

The trouble with pretending that NULL is FALSE is that NULL is neither FALSE nor TRUE.

If you have a column null_or_true in a table some_table, you might write a trio of SELECT statements:

SELECT COUNT(*) FROM some_table;
SELECT COUNT(*) FROM some_table WHERE null_or_true;
SELECT COUNT(*) FROM some_table WHERE NOT null_or_true;

The first gets the total number of rows, say 30. The second gets the number of rows where the value in null_or_true is TRUE; this might return 15. The third query returns the number of rows where null_or_true is FALSE, but if you only store NULL and not FALSE in those columns, it will return 0. To count the other 15 rows, you'd have to write a variant on:

SELECT COUNT(*) FROM some_table WHERE null_or_true IS NULL;

This counter-intuitive behaviour means you will get wrong answers from people who can be forgiven for being confused by the data stored in your table.

Therefore, you should go with:

NOT NULL DEFAULT FALSE

The other path leads to madness, sooner or later.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278