mkaz.com : Reference : PostgreSQL

My PostgreSQL Notes

Marcus Kazmierczak
April 6, 2003

Introduction

My notes on migrating from MySQL to PostgreSQL. Why migrate? Why not? Postgres offers a few features and functionality that MySQL does not have. However, the most compelling reason for me is that PostgreSQL is released under the more friendly BSD-style license.

If you really want to read more about the debate between the two, try here, or here, and even a response to previous here, and if you still really care here's another.

Now let's do some work and stop wasting time reading about holy wars and pissing matches. Who cares!? Pick one and Just Code Baby.

Datatype Differences

MySQL: id INT UNSIGNED NOT NULL AUTO_INCREMENT
Postgres: id SERIAL

MySQL: column INT UNSIGNED
Postgres: column INT CHECK (column > 0)

MySQL: INDEX idx_id(id)
Postgres: CREATE INDEX idx_id ON TABLENAME(id);

Foreign Keys

Example 1:
Using something like the following allows you to create a foreign key which refers back to another table. If you try to insert a new employee with a deptid which does not exist in the departments table an error will occur.

CREATE TABLE departments (
   deptid SERIAL PRIMARY KEY,
   deptname TEXT,
...
);

CREATE TABLE employees (
   empid SERIAL PRIMARY KEY,
   deptid INT REFERENCES departments,
...
);

Example 2: On Delete Restriction
Foreign keys also allow some features when deleting or updating a referenced item. You can restrict the deletion of an item. For example, if you tried to deleted a department which still had an employee.

Using the same departments table.

CREATE TABLE employees (
empid SERIAL PRIMARY KEY,
deptid INT REFERNCES departments ON DELETE RESTRICT,
...
);

Example 3: On Delete Cascade
Another feature that foreign keys allow on delete is a cascading delete feature. The particularly evil example of laying of a whole department. By deleting the department the cascade would delete all associated employees.

Again, using the same departments table.

CREATE TABLE employees (
   empid SERIAL PRIMARY KEY,
   deptid INT REFERNCES departments ON DELETE CASCADE,
   ...
);

Functional Differences

To page through results, such as a search query. For example, display result set rows 10-15, that is start at row 10 (offset) and give me the next 5 rows (limit). MySQL combines both in othe LIMIT command, Postgres uses two commands.

MySQL: SELECT * FROM tbl LIMIT 10, 5
Postgres: SELECT * FROM tbl LIMIT 5 OFFSET 10