Thursday, March 02, 2006

5 postgres tips

By Justin Silverton

Here are 5 postgres tips to follow my "5 mysql tips" post. These are known issues with versions 7.3 and below.

1) Object names that aren't in quotes are assumed to be lower-case

example: Select column1 FROM table1 and SELECT COLUMN1 FROM table1 both will return results from column1. SELECT "COLUMN1" FROM table1 will be viewed as a diffrent column by postgres.

2) count(*) is very slow with large tables

This is because rather than an indexed scan, postgres scans the entire table to determine the count.

3) Min and Max are both slow with large tables

This is fixed in version 8.0, but there is a work-around that I have used in previous versions:

min equivalent: SELECT column1 FROM table1 ORDER BY column1 DESC LIMIT 1
max equivalent: SELECT column1 FROM table1 ORDER BY column1 ASC LIMIT 1

4) unicode values

unicode defaults to UTF-8

5) integers used in calculations that result in a value that is greater that 32-bits will end up as 0.

to fix the issue, either get postgres version 8.0 or cast one of your values as a 64 bit value (select integer::INT8)


  • If I remember properly, version 8.1 fixes count(*) to be a fast operation, and min() and max() will now use an index if present. So, there's three gotchas gone now.

    By Blogger Randal L. Schwartz, at 11:23 PM  

Post a Comment

Links to this post:

Create a Link

<< Home