Category Archives: Postgres

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

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 | 5 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 | 11 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 | 3 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