Google+ Followers

maandag 20 oktober 2014

Why I like PostgreSQL more than MySQL: casting

MySQL supports two types of integers; signed and unsigned. The difference is that a signed integer uses one bit to indicate that a number is negative or positive. This is convenient for applications where you don't want to allow negative values.

Problem:

DROP TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo (a INTEGER UNSIGNED );
INSERT INTO foo (a) values (2);
(SELECT a FROM foo)
UNION ALL
(SELECT -2)

Will yield:
2
0

Notice that the '-2' has been converted to 0, and issues only a warning about having done so. The result is invalid.

The datatype for the column is taken from the first result, and the rest is forced into that type, so reversing the order works:

(SELECT -2)
UNION ALL
(SELECT a FROM foo)
Will yield:
-2
2

But this is not really an option in the realworld, you need something that will always work, and as we've learned over the years: MySQL doesn't have solutions that always work.

The closes we can get is to cast column 'a' to a signed integer (provided that all stored values fit) by adding CAST(a AS SIGNED INTEGER). Yes, that's SIGNED INTEGER, not INTEGER SIGNED, and the keyword INTEGER is optional, so this is also valid: CAST(a AS SIGNED)


(SELECT CAST(a AS SIGNED INTEGER) FROM foo)
UNION ALL
(SELECT -2)
Will yield:
2
-2

But now any value for a that does not fit in an INTEGER UNSIGNED will be truncated...


PostgreSQL does not do UNSIGNED, so there are no issues with casting or truncating. If you need to limit the values of a field to only negative numbers you'd use a CHECK constraint, which will also allow you to set any arbitrary limits on the value, not just "not negative" but also "more than zero", or "between 40 and 120, or "only odd numbers"