Google+ Followers

woensdag 13 augustus 2014

Why I like PostgreSQL more than MySQL: UNSIGNED fails.

Take the following piece of SQL:


DROP TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo (a INTEGER, b INTEGER UNSIGNED);
INSERT INTO foo (a,b) VALUES (-20, 3);
SELECT a+b FROM foo;

That just adds -20 and 3 to return -17, right?

Wrong:

SQL Error (1690): BIGINT UNSIGNED value is out of range in '(`wv`.`foo`.`a` + `wv`.`foo`.`b`)'

Something does not fit into an BIGINT UNSIGNED, and I would agree that -20 and -17 don't fit, but there is no BIGINT UNSIGNED in the entire table.Obviously, adding two INTEGER values could possibly give a number the size of a BIGINT, but if one of the source values is SIGNED then the output certainly must be SIGNED aswel.

Solution? Use CAST() to mangle the INTEGER UNSIGNED into an INTEGER SIGNED, then it works. It cannot hold the values you wanted anymore, but MySQL doesn't care.

PostgreSQL doesn't do SIGNED and UNSIGNED, so it doesn't suffer from this casting crap.