Postgresql

Postgresql is a powerful, mature, open source RDMS. While MySQL has a reputation for being faster, postgres has been ACID compliant for years and is far more featureful. This page contains postgresql snippets and tips. As well as external links.

Tips and Snippets

Subqueries in INSERT

Consider the following shopping list schema:

CREATE TABLE USERS (
	id serial PRIMARY KEY,
	email VARCHAR(255)
);

CREATE TABLE shoppinglists (
	id serial PRIMARY KEY,
	owner_id integer REFERENCES users(id) NOT NULL,
	name varchar(1024),
	is_shared BOOLEAN DEFAULT false
);

CREATE TABLE shoppinglist_items (
	id serial PRIMARY KEY,
	shoppinglist_id integer REFERENCES shoppinglists(id) NOT NULL,
	name varchar(1024)
);

CREATE OR REPLACE VIEW shoppinglists_with_contents AS
	SELECT 
		shoppinglists.name as shoppinglist_name,
		shoppinglist_owners.email AS shoppinglist_owner,
		shoppinglists.is_shared,
		shoppinglist_items.name
	FROM 
		shoppinglists LEFT JOIN shoppinglist_items 
			ON shoppinglists.id=shoppinglist_items.shoppinglist_id
		INNER JOIN users AS shoppinglist_owners	
			ON shoppinglists.owner_id=shoppinglist_owners.id;

You have a table of users, a table of shopping lists, and a table of shopping list items. Adding passwords and a web frontend is an exercise left for the reader. This is about INSERT statements. Anway, lets say you wanted to add a new user, a new shopping list and a new item. Normally you would create a user, get his user id, create a shopping list, gets its id and then create a shopping list item. However, postgresql supports SELECT inside of INSERT statements so your application never needs to retrieve the id of the user or shopping list. The syntax is like so.

INSERT INTO users (email) VALUES('zippy1981@gmail.com');
INSERT INTO shoppinglists (owner_id, name) VALUES (
	(SELECT id FROM users WHERE email='zippy1981@gmail.com'),
	'Grocery'
);

INSERT INTO shoppinglist_items (shoppinglist_id, name) 
VALUES(
	(SELECT id FROM shoppinglists WHERE name='Grocery'),
	'Peanut Butter'
);

External links

• Postgresql Home Page
• GBorg Open Source Postgres related repository
• PgFoundry Postgres releated tools produced by the core postgres developer group.
• Tutorials
  • Trigger Tutorial The example code looks like COBOL due to its lack of indenting and blank lines. Your better off reading the relevant chapters in te reference manual. I will probably touch on the subject in this wiki.