Erik Jones <erik@myemma.com> writes:
> Forgive me if I'm nagging on this, I just want to understand this
> better. Why does evaluating a CHECK constraint like 'CHECK some_id %
> 100 = 32' against WHERE clause like 'WHERE some_id=1132' need to know
> anything about equality properites of %? Or, rather, why does it
> stop there? Can't it just substitute the given value for some_id in
> to the check expression, execute it and check the result value for
> TRUE/FALSE?
What "given value"?
What you're missing is that the condition "a = b" does not mean that
"f(a) = f(b)" for every function f. It is possible to define
constraints on equality that would make that true, but such constraints
would be far stronger than what is required to make btree (or even hash)
indexes work.
In the example I gave, we are able to conclude that 3 is unequal to 9
not because of any a-priori knowledge, but because we apply the specific
operator to the specific constants and find out that it yields false.
Our knowledge of the consistency requirements that are imposed on btree
equality operators then allows us to determine that the two original
conditions can't be true at the same time.
This does *not* imply assuming that the two constants are really "the
same" in the sense that no other operator in the system could tell them
apart. This isn't mere academic hairsplitting: there actually are
standard equality operators in the system for which such a conclusion
would fail. I already mentioned float comparison, and numeric
comparison has similar behaviors --- for instance,
regression=# select '0.00'::numeric = '0.0'::numeric;
?column?
----------
t
(1 row)
regression=# select text('0.00'::numeric) = text('0.0'::numeric);
?column?
----------
f
(1 row)
regards, tom lane