Author Archives: Andrew

Loading data from JSON files

Posted on 2018-02-11 by Andrew

Another quick one from the IRC channel. The problem: to import a json data file (array of objects) on the client system into PostgreSQL without needing to mess with external scripting for the job (and without relying on Unix tools). … Continue reading →

Posted in PlanetPG, Postgres | 1 Comment

Perfect hash function for weekday names

Posted on 2017-01-25 by Andrew

This is probably only of limited usefulness in practice, but it came as a spinoff from a discussion on #postgresql. The basic problem is, given a string which you already know is an English weekday name, return the day number … Continue reading →

Posted in PlanetPG, Postgres, SQL | Comments Off on Perfect hash function for weekday names

Performance issues with ORed conditions

Posted on 2017-01-22 by Andrew

An issue that comes up from time to time on the IRC channel is performance optimization of queries that contain ORed conditions. Here is a summary of the common problems and some solutions.

Posted in PlanetPG, Postgres, SQL | 2 Comments

The Rule Challenge

Posted on 2010-06-21 by Andrew

I’ve made this challenge on IRC several times, and I think on the mailing lists too, but I’m putting it up here for reference. I’m not a fan of rules. I’ve used them in production code in the past, though … Continue reading →

Posted in Peeves, PlanetPG, Postgres | 15 Comments

Range aggregation with window functions

Posted on 2010-06-21 by Andrew

This article is also available on the PG wiki here This problem is one I’ve been asked about a couple of times on IRC; the solution involves some tricks with window functions that have a fairly wide range of applications. … Continue reading →

Posted in PlanetPG, Postgres, SQL | Comments Off on Range aggregation with window functions

Selecting random rows from a table

Posted on 2009-03-08 by Andrew

The question of how to select some random items from a table is one that comes up fairly often in the IRC channel (and as the subject of blog posts, such as this one from depesz). While there is a … Continue reading →

Posted in PlanetPG, Postgres | 11 Comments

Reading XML files into the database

Posted on 2009-02-05 by Andrew

This came up as a question on IRC: how to read an XML file on the server into a table column?

Posted in PlanetPG, Postgres | 3 Comments

UUID generation for PostgreSQL 8.3 on FreeBSD

Posted on 2008-12-21 by Andrew

A couple of weeks ago I whomped up a quick module in response to someone on IRC who was unable to get contrib/uuid-ossp working on FreeBSD (which turns out to be due to a nasty conflict between misc/ossp-uuid and libc). … Continue reading →

Posted in PlanetPG, Postgres | Comments Off on UUID generation for PostgreSQL 8.3 on FreeBSD

On Normalization

Posted on 2008-12-08 by Andrew

People have funny ideas about database normalization. What normalization is not: creating an (id,value) table for every single piece of data is not normalization using surrogate keys for everything is not normalization a way to improve performance (though it sometimes … Continue reading →

Posted in Peeves, PlanetPG, SQL | Comments Off on On Normalization

Hello world!

Posted on 2008-12-08 by Andrew

I have no idea if I want to keep a blog. So let’s see what happens, if anything.

Posted in Bloggery | 2 Comments