2

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?

David LeBauer
  • 3,162
  • 8
  • 32
  • 34

1 Answers1

4

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?

Neil McGuigan
  • 8,653
  • 5
  • 42
  • 57