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('email@example.com'); INSERT INTO shoppinglists (owner_id, name) VALUES ( (SELECT id FROM users WHERE firstname.lastname@example.org'), 'Grocery' ); INSERT INTO shoppinglist_items (shoppinglist_id, name) VALUES( (SELECT id FROM shoppinglists WHERE name='Grocery'), 'Peanut Butter' );
- Postgresql Home Page
- GBorg Open Source Postgres related repository
- PgFoundry Postgres releated tools produced by the core postgres developer group.
- 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.