Group Concat In PostgreSQL

Hints and Tips Posted on 16 Jun 2007, 07:55 AM Posted By Chris Smith  
Group Concat is a useful mysql function, but how do we get the same results in PostgreSQL?
Read More

PostgreSQL Enum Types

Hints and Tips Posted on 26 Mar 2006, 07:22 AM Posted By Chris Smith  
An enum datatype allows only certain values to be entered into a particular field (for example - 'red', 'blue', 'yellow', 'purple' for favourite colours).

Postgresql doesn't have an enum datatype, but we can emulate it quickly and easily.

Instead of an enum type we can set up a CHECK CONSTRAIN...
Read More

Log analyzer

Hints and Tips Posted on 08 Mar 2006, 07:02 AM Posted By Chris Smith  Unrated
I found a useful little tool the other day.

Practical Query Analyzer (written in ruby) will parse a postgresql or mysql log (though at last look mysql support was broken) and tell you what sort of queries are being run. This can help you work out whe...
Read More

Calculating database size

Hints and Tips Posted on 25 Feb 2006, 02:57 AM Posted By Chris Smith  
Postgresql creates directories to keep each database in. These directories aren't names, they are kept as the 'OID's of each database (oid's are "object identifiers"). This saves issues when you rename databases etc.

How then do you find out a databases size?

There is a "contrib" module called...
Read More

Showing Running Queries

Hints and Tips Posted on 15 Feb 2006, 01:11 AM Posted By Chris Smith  
Newer versions of PostgreSQL have a 'pg_stat_activity' view to show you who is currently connected to your database system.

By default, this doesn't show you the queries being run.

How do you show that?

Edit your postgresql.conf file and add (or uncomment):

stats_command_string = true

...
Read More

PostgreSQL Query Timing

Hints and Tips Posted on 15 Feb 2006, 01:06 AM Posted By Chris Smith  
How do you know how long a query took?

PostgreSQL lets you time the queries and print the output in your psql session.

Create a $HOME/.psqlrc file with:

\timing

in it and next time you log in to a psql session, PostgreSQL will show you how long the query took!

Doing it this way means ...
Read More