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?

  • 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? Apr 29, 2014 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. Apr 29, 2014 at 21:40
  • 1
    Why? You seem to want something that behaves like null, but isn't null. Use null? Apr 30, 2014 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 ;) Apr 30, 2014 at 5:48

1 Answer 1


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.