"Number" types

[mathemancer]

PostgreSQL offers three main categories of number types (unfortunately, the docs refer to these as "numeric" types, one of which is the NUMERIC type).

1. Floats
2. Integers
3. Numeric (Decimal)

We need to figure out what number type(s) to use for the MVP. If we want a "catch-all", we should probably use either the FLOAT or NUMERIC type.

Floats

Pros

• speed/efficiency/performance
• consistency: Python types are also floats, and they act similarly.

Cons

• We'll need to simulate integers for the UI (e.g., by truncating floats with only 0 after the decimal point to look nice.
• Floating point errors (may affect even "integers")

Numerics (Decimal type)

Pros

• control over precision / scale
  • This means we can fix (for example) two decimal points if we want for money (or let the user choose a precision).
• Automatically reflects int in python when no decimal point, decimal.Decimal otherwise
• space efficient on DB (this only happens for very large numbers, since there's some overhead).

Cons

• performance is quite bad (need to test to see practical effects)
• complexity: When reflected, can be more than one type depending on context, which we'll need to handle.

[mathemancer]

I did some basic performance testing by taking the AVG of a numeric column and a float column which had the same data. The numeric took about 1.3x the execution time of the floats.

[mathemancer]

One of the problems (for a select few users) w.r.t. using these types as a "catch-all" is that neither can be cast to boolean without first being cast to integer.

[mathemancer]

@kgodey , @ghislaineguerin , @pavish I think I'm convinced that if we want a catch-all number type, numeric is more appropriate than float at this point. What do you think?

[mathemancer]

It's resoundingly not recommended to use NUMERIC for most use-cases, by the way, but I think we're in an odd situation: We don't really want to bother a novice user with "sorry, you can't insert that number, since this is an integer column and it's a float" or "sorry that the sum of these integers has a decimal point now"

[kgodey]

@mathemancer let's go with NUMERIC, we can change it later if needed.

[pavish]

I think NUMERIC makes more sense. Since we would like to have a single Number type for the MVP, NUMERIC seems like the best option for us.

[ghislaineguerin]

I agree with using NUMERIC as well so that we have a single number type. We can always add settings to the type.