home / blog

Postgresql hello world – part 2

So we’ve got a table of horses. Let’s make another table, and get get busy with an association table and some JOINs. First we have to add a primary key to our horse table so we can reference it elsewhere.

The easy way:

ALTER TABLE horses ADD COLUMN id SERIAL;

The hard way: This explains how auto-increment really works in postgres – it actually uses a sequence owned by the table column that uses it.

# Steps to make a sequence.
ALTER TABLE horses ADD COLUMN id INT UNIQUE;
ALTER TABLE horses ALTER COLUMN id NOT NULL;
ALTER TABLE horses ADD PRIMARY KEY (id);
CREATE SEQUENCE horses_id_seq;
ALTER TABLE horses ALTER COLUMN id SET DEFAULT NEXTVAL('horses_id_seq');
ALTER SEQUENCE horses_id_seq OWNED BY horses.id;

Now let’s create table of riders.

CREATE TABLE riders (name VARCHAR(255), id SERIAL PRIMARY KEY);
INSERT INTO riders (name) VALUES ('adam');
INSERT INTO riders (name) VALUES ('bob');
INSERT INTO riders (name) VALUES ('charlie');

Now we create an association table linked horses with riders.

CREATE TABLE horses_riders (hid INT REFERENCES horses(id),
                            rid INT REFERENCES riders(id));

INSERT INTO horses_riders (hid, rid) VALUES (1,1);
INSERT INTO horses_riders (hid, rid) VALUES (2,3);

Using the association table. First the naive, inefficient way. This is bad because it makes all possible comparisons.

SELECT r.name,h.name FROM horses h, riders r,horses_riders ass 
                     WHERE ass.hid = h.id AND ass.rid = r.id;
  name   |     name     
---------+--------------
 adam    | Mr Ed
 charlie | glue factory

Now joins, there are various varieties, but the simplest to understand is simple “table1 JOIN table2 ON condition”.

SELECT * from (horses h JOIN horses_riders ass ON ass.hid = h.id);
     name     | size | id | hid | rid 
--------------+------+----+-----+-----
 Mr Ed        |  456 |  1 |   1 |   1
 glue factory |  123 |  2 |   2 |   3

SELECT * from (riders r JOIN horses_riders ass ON ass.rid = r.id);
  name   | id | hid | rid 
---------+----+-----+-----
 adam    |  1 |   1 |   1
 charlie |  3 |   2 |   3

We can combine two joins to see who owns which horses.

SELECT r.name, h.name from 
      (horses_riders ass JOIN horses h ON ass.hid = h.id)
      JOIN riders r ON ass.rid = r.id;

  name   |     name     
---------+--------------
 adam    | Mr Ed
 charlie | glue factory
This entry was posted in geek. Bookmark the permalink.

Leave a Reply

Your email address will not be published.