This question already has an answer here:

I am using PostgreSQL and trying to write value constraints.

Is it possible to record missing data as distinct from NULL values in a numeric field without using an arbitrary numeric value?

For many fields, I want to ensure that the record is either numeric or recorded as missing, but not NULL.

A common solution is to use -9999 to indicate a missing value. However, this can lead to silent errors (e.g. calculating a mean). Is there a better solution than creating a new type that allows either a value of "Missing" or a number?

marked as duplicate by Max Vernon, Paul White, RolandoMySQLDBA, Kin, Philᵀᴹ May 2 '14 at 8:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 2
    Add a new table, for missing things? But what is the problem you face with using NULL for noting that the value is missing? – ypercubeᵀᴹ Apr 29 '14 at 20:53
  • @ypercube Generally, following Joe Celko's advice to default to using non-null constraints. Specifically, I would like to use nonnull constraints to avoid many known issues with nulls, e.g. in computation and logical queries, and also to be able to distinguish data that is missing from data that does not exist. – David LeBauer Apr 29 '14 at 21:40
  • 1
    Why? You seem to want something that behaves like null, but isn't null. Use null? – Craig Ringer Apr 30 '14 at 0:53
  • You can use a technique where you have a bit flag to indicate whether the value is set or not. Not a 'perfect' solution, but it's also an imperfect situation ;) – Allan S. Hansen Apr 30 '14 at 5:48
up vote 4 down vote accepted

Your alternatives are:

  1. use 6th normal form and don't include a row if something doesn't exist.

  2. use nulls

  3. use some other value that will screw up all of your calculations.

My recommendation would be to use nulls. Why don't you want to use nulls?

Not the answer you're looking for? Browse other questions tagged or ask your own question.