zaterdag 12 september 2015

Why I like PostgreSQL more than MySQL: OFFSET can use any expression

In MySQL, a regular query cannot use anything other than a hardcoded integer value for OFFSET.

This means that you cannot OFFSET by any value that comes from the database itself, without fetching that value separately and injecting it into a new query.

For example; if you want to fetch a quote-of-the-day you would order the records by some value and then just SELECT with an OFFSET that is de modulo of the day-of-year and the number of quotes in the table.

In MySQL that requires two queries.

In PostgreSQL you can just do what you need to do:

-- Create a table to play with
CREATE TEMPORARY TABLE foo (spreuk text);
INSERT INTO foo VALUES ('beter een lege dop dan helemaal geen dop'), ('beter een vogel in de hand, dan de lucht van tien.'),('Wie het laatst lacht heeft het minst gevoel voor humor.');

-- Fetch the quote!
SELECT * FROM foo OFFSET FLOOR( EXTRACT('doy' FROM now())::bigint % (SELECT COUNT(*) FROM foo)) LIMIT 1;