Author Archives: Andrew

Loading data from JSON files

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,203 Comments

Perfect hash function for weekday names

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 | 7 Comments

Performance issues with ORed conditions

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

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 | 16 Comments

Range aggregation with window functions

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 | Leave a comment

Selecting random rows from a table

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 | 14 Comments

Reading XML files into the database

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 | 4 Comments

UUID generation for PostgreSQL 8.3 on FreeBSD

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 | Leave a comment

On Normalization

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 | 7 Comments

Hello world!

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

Posted in Bloggery | 3 Comments