Categories
Archives
Meta
Category Archives: PlanetPG
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
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
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
15 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
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
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