Google+ Followers

vrijdag 31 januari 2014

Why I like PostgreSQL more than MySQL: Substring() can use regular expressions

The SUBSTRING() function accepts regular expressions and can return a matching block:

SELECT
SUBSTRING('<a href="http://www.thedolmen.com/">The Dolmen</a>' , 'href=\"([^\"]+)\"')

Returns:
 substring
------------
 The Dolmen
(1 row)


Notice that the expression includes elements that are not returned in the result.
And be aware that there can be only one block in the regular expression, SUBSTRING() can after all only return one value.

donderdag 30 januari 2014

Why I like PostgreSQL more than MySQL: Windowing

When you deal with customers in a shop you will want to log changes to your stock and your orders, both to generate statistics and to have a way of tracing any problems. The simplest way to do this is through a trigger that simply creates a copy of the entire record in a separate auditing table.

MySQL can do this just as well as PostgreSQL, but analysing the data is a different story.

Whenever you look a a history of events one of the first things you will want to know is: how much time elapsed between each row? Traditionally you would solve that by adding  a self-join to fetch the previous record, and subtracting the two timestamps.

Windowing allows you to do it much faster, in a much more flexible way that is infinately more readable:

SELECT
timestampfield - LAG(timestampfield) OVER (ORDER BY timestampfield)
FROM tale;

Yes, it really is that simple. LAG() fetches the previous record and the OVER(ORDER BY ...) indicates that "previous" should be taken from "the record with the highest timestamp that is still lower than the current record's timestamp".

Windowing also allows you to fetch the "second to previous" record, or "the ten previous records", and obviously you can do the same with records following the current record, using LEAD().

There are many other things you can do with windowing, like subdivide the data into groups of equal values in a particular field and ranking.

See also: http://www.postgresql.org/docs/9.1/static/tutorial-window.html

dinsdag 28 januari 2014

Is oracle finally getting a grip on MySQL?

Today the MySQL blogs are talking about a change that Oracle is thinking of making in MySQL 5.7. The change: to enable ONLY_FULL_GROUP_BY by default.

Apparently this is a very big deal, even though it is just a default setting which can simply be disabled (like most things in MySQL that provide reliability).

I guess I'm pleasantly surprised that so many MySQL actually know about this feature...

See also: http://www.tocker.ca/2014/01/24/proposal-to-enable-sql-mode-only-full-group-by-by-default.html

dinsdag 14 januari 2014

Why I like PostgreSQL more than MySQL: RETURNING

Every once in a while you get a request like "When I run this delete query I want to see which records have been deleted.".

The quick and dirty solution would be to run a SELECT  FOR UPDATE query first, to get the data, and follow that with a DELETE query to remove the data. But that requires two roundtrips to the database and if the query parameters are complex then you basically make the whilething twice as slow as it needs to be.

In PostgreSQL you just add "RETURNING *" to the DELETE query.

DROP TABLE IF EXISTS demo;
CREATE TABLE demo (id INTEGER, firstname TEXT, score INTEGER);
INSERT INTO demo VALUES (1,'Carl',24), (2,'Pete',10);

DELETE FROM demo WHERE firstname='Pete' RETURNING *;

That removes the record for 'Pete', and returns it, in one trip to the database.

Similarly, you can fetch the new record after an update:

UPDATE demo SET score=score+6 WHERE firstname='Carl' RETURNING score;

which returns just one record with score='30', the new score for Carl after adding 6 to it.


zondag 12 januari 2014

Why I like PostgreSQL more than MySQL: Combining Queries

So you have a shop that sells powerwashers and your flagship product is the PW-100, and a about a week ago you received a shipment of nozzles that fix a known problem in the PW-100. But, this only affects the PW-100 when used with the universal extender EX-900. Your customers are not ordering them as often as you think they should, so you want to send them an email telling them about this new nozzle. Of course you are very much against useless information so you only want to send the email to people who have actually bought the PW-100 and the EX-900, and only if the have not yet ordered the new nozzle.

This sounds like a bunch of INNER and LEFT joins, but PostgreSQL has proper support for EXCEPT and INTERSECT so you can write the query like this:


SELECT customer_id FROM orders INNER JOIN orderlines ON orderlines.order_id = orders.order_id WHERE product_id='PW-100'

INTERSECT

SELECT customer_id FROM orders INNER JOIN orderlines ON orderlines.order_id = orders.order_id WHERE product_id='PW-100'

EXCEPT

SELECT customer_id FROM orders INNER JOIN orderlines ON orderlines.order_id = orders.order_id WHERE product_id='New_nozzle_for_PW-100'

The power of INTERSECT and EXCEPT become more apparent as the queries get more complex. This example selects only on field but of course it works just as well when selecting a list of fields, which would be a pain for a JOIN syntax.