PostgreSQL & PHP Tutorials - Rss Feeds

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 10:15:52 -0400

Creating a good database design takes some thought. Sometimes, a normalized database won't perform the way you want it. Using some features of postgresql, you could get your performance back.

I decided to use some functions and triggers to keep queries nice and simple, but not lose any functionality and keep data redundancy to a minimum.

Read more..

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.

Forum Progress Part II

chris@designmagick.com (Chris Smith) — Mon, 17 Apr 2006 09:27:34 -0400

The forum is very slowly getting put together. The basic admin areas are done:

- forums have group access
- groups can have users

Since most of the work is done on the frontend, I have been putting the design together and working out how to keep things in check.

Since postgresql has advanced features, I've decided to use some of those.

Instead of having to do a query to get thread counts and post counts per forum, I'll use a trigger to keep this info up to date when a thread or post is added to a particular forum.

I also use a plpgsql trigger to keep the "last post time", "last post author" and "last post id" (which makes navigating a particular forum a little easier).

Lastly, if a forum, group, or user gets deleted, then it will clean up after itself by using referential integrity checks and "on delete cascade" triggers. This will cause postgresql to delete anything that refers to a particular item.

Much easier than having to replicate that functionality in code and missing something!

Site update

chris@designmagick.com (Chris Smith) — Mon, 03 Apr 2006 05:59:20 -0400

I finally got around to adding an area for users to submit articles.

You have to be logged in to submit an article - that saves me having to worry about the form being used for spam, random php scripts trying to be used to hack the site and so on..

So, if you want to submit something, go for it!

Forum Progress

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

I haven't made much progress with the new forum yet, but I've worked out how to manage permissions.

At the bottom level will be users.

Users will be in groups.

Groups will have permissions in particular forums (so a user can be a forum moderator in a particular forum, but a simple user in another).

So permissions are basically worked out (writing the API will be a little bit of work but shouldn't be too difficult now I've worked out the logic behind it all).

Then, forums will (potentially) have subforums.

A forum will have many threads.

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.

New project

chris@designmagick.com (Chris Smith) — Tue, 28 Mar 2006 06:02:52 -0500

I've decided to do something a little more constructive than random tutorials.

I've been looking for a forum to put on the site to make it a bit more interactive, but haven't been able to find anything suitable.

Either they are mysql-only or security-issue prone.

Fud-forum works with postgresql but doesn't take advantage of a lot of postgresql features (like subqueries) because it has to work in mysql and postgresql - so it does a ton of queries with every page.

So, what features am I looking for?

On the tech side:

- postgresql support
- clean, fast
- nice interface
- limited sql queries
- possibly caching.

On the design/interface side:
- clean
- template based
- fast

If you have any suggestions on where to start looking for such a forum, or have any feature suggestions send 'em through!

Might see if I can set up a cvs or subversion server and get others to help me out ;) Let me know if you're interested.

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!