PostgreSQL & PHP Tutorials - Rss Articles Feed

Group Concat In PostgreSQL

chris@designmagick.com (Chris Smith) — Sat, 16 Jun 2007 07:55:49 -0400

Group Concat is a useful mysql function, but how do we get the same results in PostgreSQL?

Database Design Issues

chris@designmagick.com (Chris Smith) — Thu, 20 Apr 2006 08:54:51 -0400

Database normalization and design issues is a difficult topic to write about. A lot of it depends on the application you are writing and how you want to access the data.

Whilst writing my forum, I decided to do things slightly differently to a normalized setup to speed things up.

Here's a look at how to achieve some nice improvements by adjusting a database design and using some postgresql features like triggers and functions.

Introduction to Database Joins

chris@designmagick.com (Chris Smith) — Sun, 02 Apr 2006 08:34:24 -0400

Database joins are an essential skill to learn, whether it's using PostgreSQL or any other database management system.

Creating a Cache

chris@designmagick.com (Chris Smith) — Sun, 02 Apr 2006 07:08:32 -0400

I decided to create a simple cache system for this site, to cut down on the number of database queries being run.

Here's a look at how I did it, what it caches and how simple it is to get up and running.

PostgreSQL Enum Types

chris@designmagick.com (Chris Smith) — Sun, 26 Mar 2006 07:22:55 -0500

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 CONSTRAINT - this tells postgresql to make sure that the value we are entering is valid.

CREATE TABLE person (
personid int not null primary key,
favourite_colour varchar(255) NOT NULL,
CHECK (favourite_colour IN ('red', 'blue', 'yellow', 'purple'))
);

Now that's done, let's check it works:

test=# insert into person(personid, favourite_colour) values (1, 'red');
INSERT 0 1

Now for something not in the list:

test=# insert into person(personid, favourite_colour) values (2, 'green');
ERROR: new row for relation "person" violates check constraint "person_favourite_colour_check"

Done! Nice and easy!

Introduction to Full Text Indexing

chris@designmagick.com (Chris Smith) — Sun, 26 Mar 2006 07:00:14 -0500

Full text indexing in postgresql is a little more complicated to set up compared to other databases.

This is a quick introduction on how to install it, how to set it up and how to keep it up to date.

Using Explain

chris@designmagick.com (Chris Smith) — Sun, 05 Mar 2006 07:31:47 -0500

Now that you've set up a database, you need to check it's being utilized properly.

Using 'Explain' to check your queries are using an index is a good way to do it. Here's a quick introduction in to reading the output.

PostgreSQL and FreeBSD

chris@designmagick.com (Chris Smith) — Sun, 26 Feb 2006 00:41:05 -0500

PostgreSQL can be installed using on FreeBSD using the ports that all *BSD systems use.

Check out this install guide.

Calculating database size

chris@designmagick.com (Chris Smith) — Sat, 25 Feb 2006 02:57:40 -0500

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 'dbsize' which can do it for you. These modules don't get installed by default but allow extra functionality quite easily.

See the documentation for details on how to use it.

Introduction to Object Oriented Programming

chris@designmagick.com (Chris Smith) — Sun, 19 Feb 2006 07:13:06 -0500

Object oriented programming is a little different to procedural programming.

Here's an introduction to "OOP" and a proper example of where it can come in handy.