When reading the article Crunching 30 Years of NBA Data with MongoDB Aggregation I coulnd’t help but think that we’ve been enjoying aggregates in SQL for 3 or 4 decades already. When using PostgreSQL it’s even easy to actually add your own aggregates given the SQL command create aggregate.
Category “Yesql” — 36 articles
In our Tour of Extensions today’s article is about advanced tag indexing. We have a great data collection to play with and our goal today is to be able to quickly find data matching a complex set of tags. So, let’s find out those lastfm tracks that are tagged as blues and rhythm and blues, for instance.
We’re going to use the Last.fm dataset from the Million Song Dataset project here.
PostgreSQL is an all round impressive Relational DataBase Management System which implements the SQL standard (see the very useful reference page Comparison of different SQL implementations for details). PostgreSQL also provides with unique solutions in the database market and has been leading innovation for some years now. Still, there’s no support for Autonomous Transactions within the server itself. Let’s have a look at how to easily implement them with PL/Proxy.
Let’s get back to our Tour of Extensions that had to be kept aside for awhile with other concerns such as last chance PostgreSQL data recovery. Now that we have a data loading tool up to the task (read about it in the Loading Geolocation Data article) we’re going to be able to play with the awesome ip4r extension from RhodiumToad.
In our ongoing
Tour of Extensions we played with
earth distance in
How far is the nearest pub? then with
hstore in a series about trigger,
first to generalize
Trigger Parameters then to enable us to
Auditing Changes with Hstore. Today we are going to work with
trigrams PostgreSQL extension: its usage got seriously enhanced in
recent PostgreSQL releases and it’s now a poor’s man
Full Text Search
In a previous article about
Trigger Parameters we have been using the
hstore in order to compute some extra field in our records, where
the fields used both for the computation and for storing the results were
passed in as
dynamic parameters. Today we’re going to see another
use case for
hstore: we are going to record changes made to our tuples.
Sometimes you want to compute values automatically at
INSERT time, like for
duration column out of a
start and an
end column, both
timestamptz. It’s easy enough to do with a
BEFORE TRIGGER on your table.
What’s more complex is to come up with a parametrized spelling of the
trigger, where you can attach the same
stored procedure to any table even
when the column names are different from one another.
There was SQL before window functions and SQL after window functions: that’s how powerful this tool is. Being that of a deal breaker unfortunately means that it can be quite hard to grasp the feature. This article aims at making it crystal clear so that you can begin using it today and are able to reason about it and recognize cases where you want to be using window functions.
In our recent article
about The Most Popular Pub Names we did
have a look at how to find the pubs nearby, but didn’t compute the
distance in between that pub and us. That’s because how to compute a
distance given a position on the earth expressed as longitude and
latitude is not that easy. Today, we are going to solve that problem