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.
Category “Yesql” — 33 articles
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
In a recent article here we’ve been talking about how do do Batch Updates in a very efficient way, using the Writable CTE features available in PostgreSQL 9.1. I sometime read how Common Table Expressions changed the life of fellow DBAs and developers, and would say that Writable CTE are at least the same boost again.
In a recent article Craig Kerstiens from Heroku did demo the really useful crosstab extension. That function allows you to pivot a table so that you can see the data from different categories in separate columns in the same row rather than in separate rows. The article from Craig is Pivoting in Postgres.
Pivoting a matrix, also known as a matrix transposition
Let’s do the same setup as he did, with a table containing some randomly generated data about hypothetical visits to a web page, say, by date then by operating system.
In this article, we want to find the town with the greatest number of inhabitants near a given location.
A very localized example We first need to find and import some data, and I found at the following place a CSV listing of french cities with coordinates and population and some numbers of interest for the exercise here.
To import the data set, we first need a table, then a COPY command: