Google+ Followers

maandag 27 oktober 2014

Why I like PostgreSQL more than MySQL: Zalando!

Whenever I tell a manager about PostgreSQL the first thing they ask is "who uses it?", asif tools are only good if someone they know uses them.

Well how about, oh, one of the largest mailorder companies in the world: Zalando? Will that do?

http://gotocon.com/berlin-2013/presentation/Why%20Zalando%20trusts%20in%20PostgreSQL

And guess what, they do exactly what I'm always on about: Let the database control the data; define a stored-procedure based API in the database and let applications talk to that.

Permission to look smug sir? Granted!

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"

donderdag 2 oktober 2014

Why I like PostgreSQL more than MySQL: stubbornly refusing to admit errors.

MySQL has a long history of pretending that there are no such things as errors. Everywhere you turn there are features that allow you to make mistakes and never be told about them, or even worse: pretend that there actually was no error at all. If you've ever restored a database with views you'll know how MySQL treats the view as a table which is later converted into a view, just in case the target database does not support views.

The command line options are much the same; if you are adding a command line option to MySQL in a shell script you can actually tell MySQL not to throw an error if it does not support that option.

From the manual:

If an option is prefixed by --loose, a program does not exit with an error if it does not recognize the option, but instead issues only a warning:
shell> mysql --loose-no-such-optionmysql: WARNING: unknown option '--no-such-option'

So if your script needs to do something that the target database might not be able to do, then just pretend that it can, and it'll be fine. Well, what's the worst that could happen... your database gets destroyed, big deal, you've got backups... right?