PostgreSQL

Look, I know this isn't our normal type of post.  No code snippets, no real world examples, etc.  Barely even a trace of wit and/or style.

Ok, maybe the lack of style thing really is in keeping with our normal posts.  Whatever.

Point is that this is Important Stuff and, therefore, you need to know:

The deadline for submitting proposals for Open Source Bridge is fast approaching, as in you-need-to-get-yours-in-in-under-20-days-as-of-this-writing fast.  Or, more specifically, by March 31.

So what, exactly, is Open Source Bridge?  Well, my under-rock dwelling friend, it is:

Open Source Bridge is a new conference for developers working with open source technologies. It will take place June 17-19 in Portland, OR, with five tracks connecting people across projects, languages, and backgrounds to explore how we do our work, and why we participate in open source. The conference structure is designed to provide developers with an opportunity to learn from people they might not connect with at other events.

Them's their words, of course.  Me?  I like to think of it as The OSCON Replacement That Will No Doubt Be Better Than Its Predecessor, So San Jose Can Just Keep That Bloody Thing Because We Don't Need Them In Portland, Anyway, Conference. 

Granted, "Open Source Bridge" rolls off the tongue a little easier.

So go to the Open Source Bridge site.  Submit a proposal!  Volunteer!  Sponsor!  

And if you can't do any of those things, just make sure to register and get your bums to Portland in JUNE. 

Couple of events going down in the Portland area over the next week or two that all the cool kids are going to attend (i.e. me and any other nerds who can sneak past their mom/wife/partner/WoW).

PostgreSQL Conference Fall 2007

Ah, PostgreSQL...  The Other White Meat.  Or, at least, the Other Open Source DB.  Those cooky kids are throwing their little conference at Portland State University in the heart of increasingly-less-sunny-as-fall-hits-its-stride Stumptown this coming Saturday, October 20th.  You need to preregister and scrounge up sixty tax-deductible bucks to get in ($10 if you're a student/faculty), but the topics and speakers look worth every penny.  The Rails applications talk sounds interesting, and I have a soft spot for the GIS stuff (PostGIS talk at 2:15 PST).  Oh, and don't forget the morning meet-n-greet and the after-dinner/party.

Read more and register at http://www.postgresqlconference.org, and if you happen to see a goofy blonde guy who bears a striking resemblance to the picture on your right, say "hi."

Ignite Portland

Ignite Portland's goin' down on Thursday, October 25 at Wieden+Kennedy in uber-trendy Northwest Portland.  Free to anyone who decides to browse over to http://upcoming.yahoo.com/event/265888/ and get their RSVP on.  Yes, I said "free."  As in, no cost.  Looks like there are currently 181 PDXers on the list right now, should be fun.

But what the hell is "Ignite Portland."  Hell if I know.  The jist of it is that speakers have 5 minutes to talk about just about anything.  They get 20 slides that rotate after 15 seconds, pretty much guaranteeing a fast clip.  Take a look at the clip on the Ignite Portland page for an example (http://www.igniteportland.com/). 

They haven't posted the list of presenters and topics yet, but if the roll calls from previous Ignites is any indication, it should be a hoot.  And given the fact that Portlanders are genetically predisposed to attempt to out-wacky people in other metro areas, well, I'm expecting great things.  Worst case scenario?  Well, each presentation is only 5 minutes long.  I can put up with even the worst speaker for that long, knowing the hook's coming out soon.

By the way, the Ignite people need more sponsors, so if this kind of crowd's the type o' people your business wants to reach or if you have a wealthy eccentric uncle looking to splurge, head to http://www.igniteportland.com/sponsorships/. 

Woke up this morning to find a message in my inbox from the PostgreSQL pgsql-announce listserv with the subject "PostgreSQL 8.0, 8.1 on Windows End-of-Life."  Pretty self-explanatory.

According to the announcement, no more updates for those versions on the Windows platform will be released once version 8.3 hits the shelves later this year.  They recommend that anybody using one of those versions start thinking about upgrading as soon as possible.

Normally, we don't like to just parrot news readily available from other sources, but I didn't see this particular blurb on the PostgreSQL website (http://www.postgres.org) as of this morning.  And considering that I doubt most of you subscribe to that same listserv, well, I thought I'd raise up and get my Walter Cronkite on.

Of course, I doubt many Drupalers (Drupalites?  Drupalians?) will be affected by this.  After all, seems that the people who run it on Windows are in the minority anyway...  And how many of those do you think are using PostgreSQL on Windows, too?  I'm guessing this affects about 3 people... Total.

Anyway, here's the message.  Enjoy:

Following the release of PostgreSQL 8.3 later this year, PostgreSQL
8.1.x and 8.0.x will cease to be officially supported on Windows. This
means that no further binary releases will be produced, and no Windows
specific bug fixes will be applied. Users of PostgreSQL 8.0 and 8.1 on
Windows should begin planning an upgrade to version 8.2 or 8.3 soon.

The PostgreSQL Global Development Group regrets the necessity of
dropping support for these releases. PostgreSQL 8.0 was the first
release to natively support Windows, and since then a number of
potentially serious issues have been fixed in later releases that are
simply not technically practical to back port. PostgreSQL 8.2 and above
are considered mature and will enjoy the same lifetime on Windows as on
all other platforms.

--
Dave Page
PostgreSQL Core Team

In our last SQL post, we talked about the three basic types of JOINs enterprising young SQL junkies might use to gather data from different tables in their databases.

"Enterprising..." Oh, how I crack myself up some times. Seriously. Don't make me explain why that's funny.

Anyway, in between Star Trek-related examples, we blabbed on and on about INNER JOINs, three kinds of OUTER JOINs, and the almost completely useless CROSS JOIN. You might remember, though, that every one of those brilliant examples involved querying existing data with SELECT statements. That's fine and dandy. After all, that's also how you'll use those JOINs about 98% of the time. However, JOINs aren't just confined to SELECT statements...

No, sir. You can use JOINs in your INSERT, UPDATE, and DELETE statements, too. Sit back and watch.
First, let's resurrect our sample tables from the last post. If you'll recall, we had a STARSHIP table that contained the following data:

And an OFFICER table that looked a little something like this:

So let's have some fun. First, we're going to add another record to our OFFICER table.

Let's say that we have a dashing new OFFICER who we want to assign to the USS Justinian. Star Fleet HR gave us the info we need for the firstname, lastname, and rank fields (Justin, Stanley, and Grand Poobah, respectively), but we still need to know the right value to insert into the currentshipid field.

One way to go about that would be to simply look it up first by throwing a SELECT statement at our STARSHIP table, writing down the shipid value that corresponds to the USS Justinian, then manually adding that to our normal INSERT statement:

INSERT INTO officer 

    (firstname, lastname, rank, currentshipid)

VALUES

    ('Justin', 'Stanley', 'Grand Poobah', 4)

Obviously, that works just fine and you'll probably end up doing that most of the time. But damn it, Jim! This post is about using JOINs (or will be eventually...), so that's what we're bloody hell gonna do. Before we get to that part, though, we need to get familiar with the INSERT...SELECT syntax.

So let's write that query. The first part looks like your regular ol' INSERT statement. You'll notice, though, that the syntax changes a bit right after we specify the column names of the destination table:

INSERT INTO officer

    (firstname, lastname, rank, currentshipid)

SELECT 'Justin', 'Stanley', 'Grand Poobah', s.shipid

    FROM starship s

    WHERE s.name = 'Justinian'

(Note: I didn't need to specify a value for the officerid column because we set it up as an IDENTITY field that'll autoincrement when new records are added when the table was initially created.)

Almost seems to overcomplicate things, doesn't it? Maybe you thought that you could get away with something like this instead:

INSERT INTO officer

    (firstname, lastname, rank, currentshipid)

VALUES

    ('Justin', 'Stanley', 'Grand Poobah', 

        (SELECT shipid FROM starship WHERE name = 'Justinian')

    )

A lot of people probably try that one first then get pissed when it doesn't work. I'm one of them. Thing you have to remember is that the SELECT statement doesn't necessarily return a scalar value, but that's what the INSERT statement (written in this format) requires. Even if you write the SELECT part so that it only returns one record (using TOP, for instance), this query will still poo itself.

And that brings us to a big warning: when you're using the INSERT...SELECT statement, be 100% certain that the SELECT part only returns a single row. Otherwise, there's a pretty good chance that you'll end up INSERTing more rows than you intended.

For example, what if I was trying to assign this new officer to Picard's Enterprise instead? If I just changed the WHERE statement in my query to 'WHERE s.name = 'Enterprise',' I'd be in for a rude awakening. That query would return results for both ships named Enterprise in my table and, therefore, would insert two rows into the OFFICER table with my new guy's info, one with shipid = 1 and another with shipid = 2 (the shipid's for the two ships named 'Enterprise' in my STARSHIP table).

So let's get to the JOIN example. Let's say that we have another new OFFICER to add to the table. Again, Star Fleet HR gives us his firstname, lastname, and rank ('Luke', 'Skoubo', 'Junior Lackey'), but this time they also tell us that he's to serve under Grand Poobah Stanley.

Well, again, we could just use a SELECT statement to find the currentshipid for Stanley's record and use that to create your INSERT. Boring. We're going to use a JOIN so we only have to write one query and don't have to bother fishing through our desk for a pen and Post-It Note:

INSERT INTO officer

    (firstname, lastname, rank, currentshipid)

SELECT 'Luke', 'Skoubo', 'Junior Lackey', s.shipid

    FROM starship s

    INNER JOIN officer o

    ON s.shipid = o.currentshipid

    WHERE o.lastname = 'Stanley'

Simple, eh? Again, though, just have to make sure that the SELECT part only pulls a single record so you don't end up INSERTing more than you expected.

UPDATEs work pretty much the same way and, frankly, I can think of many more situations in which I've used a JOIN in UPDATEs than INSERTs.

For example, what if Star Fleet decided to implement some cost-cutting measures by decommissioning the old Constitution class Enterprise and transferring all it's personnel over to... oh, what's it called? That one ship that Grand Poobah Stanley runs?

As the Star Fleet DBA, we need to ensure that the data in the OFFICER table reflects that change and adjust our currentshipid fields accordingly:

UPDATE o

SET o.currentshipid = 

    (SELECT currentshipid 

    FROM officer 

    WHERE lastname = 'Stanley')

FROM starship s  

INNER JOIN officer o

    ON s.shipid = o.currentshipid

WHERE s.registry = 'NCC-1701'

Now Kirk and Spock are Stanley's minions. Like the INSERT example, I still had to be careful that the subquery I used to find Stanley's currentshipid only returned on row. However, unlike that previous query, wacky things won't happen if my subquery returns multiple results. Instead, it'll just fail altogether with a warning that it can only return a single record if I expect things to actually work.

Finally, a DELETE example. It's very similar to the others, but I find it a little counter-intuitive in its redudancy. With the DELETE...JOIN statement, you have to list the table twice using the FROM statement to begin the JOIN. Check out the example.

Let's say that the Reliant is destroyed while Chekov and Khaaaaaan are both still on board. Star Fleet, anxious to sweep that dirty little hijacking under the rug, asks you to delete all records of OFFICERs assigned to that boat at the time:

DELETE officer

FROM officer o

INNER JOIN starship s

    ON s.shipid = o.currentshipid

WHERE s.name = 'Reliant'

You might try something like "DELETE FROM officer INNER JOIN...", but you'll faily miserably. Sorry, but I didn't write the language or standards.

Well, I don't know about you, but that's about all the JOINing I can take for awhile. As always, happy to hear any questions, comments, or suggestions.

When you've been playing around with SQL for awhile, you start to take your knowledge of JOINs for granted. When someone asks you about JOINing data from two or more tables together, you get this stunned look on your face... You know, the same one you get when you actually hear someone mutter the words "I've never seen 'Star Wars'." You forget that you, too, once didn't know a JOIN from a Jedi, and that everyone has to start somewhere.

So let's talk about JOINs. Like the name implies, a JOIN is simply a method for connecting two tables in a database, usually through some piece of data common to records in both tables.

And they are pretty simple once you figure them out. Before you bother to start writing queries, you need to ask yourself two questions: what kind of data do you want from each table, and how are those two tables related?

The first question will help you figure out which type of JOIN you're going to use. Do you only care about records in each table when they have a matching result in the other table or do you want all records from one or both tables, regardless of matches found?

And once you've answered that one, you can start thinking about that second question. SQL doesn't know exactly how those two tables are related on its own, so your well-designed database will likely take advantage of some foreign keys or other data that somehow links the tables together.

How 'bout an example? Let's say you're a DBA working in Star Fleet Command (hey, the site's called "Nerdliness," isn't it?). You store information about specific vessels in the STARSHIP table and info about different people manning those ships is in the OFFICER table. Occasionally, some admiral or another asks you to pull information about the ships and their crews. Should be pretty clear by now what SQL operation we'll be performing, eh?

We're going to make the tables pretty simple for this example. Our STARSHIP table looks something like this:

And our OFFICER table looks like this:

First, no. I didn't know all the information off the top of my head. Only about 95% of it...

And second, for all the Trekkers and Trekkies out there, in this example, Ryker's had a spat with Picard and is no longer assigned to his (or any other) ship. And, because Star Fleet's a government organization and, therefore, probably dirty as all Hell, they've put a positive spin on the whole oh-snap-this -300-year-old-super-dude-just-hijacked-one-of-our-ships thing by issuing a press release stating that Khan was actually given command of the Reliant and all records were forged to jive with the party line.

Oh, and the USS Justinian was just finished, but Star Fleet has yet to find a captain worthy of sitting at the helm of such a fantastic machine.

Anyway...

So there are three basic types of JOINs:

1. INNER JOIN: An INNER JOIN will return all rows from both tables involved so long as there's a matching row in each table. For example, if I did a straight INNER JOIN of my two tables, the query would probably look something like this:

SELECT * FROM officer o

INNER JOIN starship s

ON o.currentshipid = s.shipid

And my output:

That might be useful if I wanted to find the names of all the crew members for all this ships. Or, if I just wanted to know about a specific ship, I could use the WHERE statement to slim down the result set (WHERE s.registry = 'NCC-1707', for example).

Notice a couple of things... First, I didn't specify columns, so my result set contains every piece of data from both tables. In all likelihood, I'd probably only need some of that info and would adjust my query accordingly in real Star Fleet life.

Second, the INNER keyword is actually optional. I like to use it for clarity's sake, but you'll end up with the same results if you leave it out. In other words, the default JOIN with MS SQL is the INNER JOIN.

Finally, see what's missing? Because I used an INNER JOIN, Ryker and the USS Justinian are missing from the result set. I only asked SQL to give me the records that met the "o.currentshipid = s.shipid" criteria, and they didn't fit the bill. And if I actually did want to see all of the records from a table, even if there wasn't a match? In that case, I'd need to use an...

2. OUTER JOIN: Unlike an INNER JOIN, the OUTER JOIN will return all the records from one (or more) of the tables involved. Let's say I wanted to know the names of all current officers and their currently assigned ships. However, unlike with the INNER JOIN, I also want them listed even if they aren't on a ship. That might look like this:

SELECT o.firstname, o.lastname, s.registry, s.name FROM officer o

LEFT OUTER JOIN starship s

ON o.currentshipid = s.shipid

The result?

Suddenly, Ryker matters. We pulled his info from the OFFICE table like we wanted, giving us the complete list of personnel. And for those people assigned a ship, we have all the data we asked for from the STARSHIP table.

Notice that the two columns from the STARSHIP table are both NULL for Ryker. That'll happen for every record that doesn't have a matching result from your OUTER JOINed table.

Also note that the USS Justinian still isn't listed anywhere. The way we structured our query, we only asked SQL for all the records in one of the two tables involved (the one on the LEFT of the equals sign). Guess what word we'd replace LEFT with if we wanted all the records in the table on the RIGHT side? Yep. RIGHT. So if we instead wanted our list to include all the STARSHIP records and, when possible, the assigned officers, we could use the exact same query, only changing that one word:

SELECT o.firstname, o.lastname, s.registry, s.name FROM officer o

RIGHT OUTER JOIN starship s

ON o.currentshipid = s.shipid

Gives us this:

No more Ryker, but now the Justinian shows up. Imagine that. SQL did what we told it to do. And, again, we see NULL values when there aren't any matching records from the JOINed table.

I know your next question: "What if I want everything from both columns, regardless of matches?" Easy. You raise up and get your FULL OUTER JOIN on:

SELECT o.firstname, o.lastname, s.registry, s.name FROM officer o

FULL OUTER JOIN starship s

ON o.currentshipid = s.shipid

And...

Piece of cake, eh? All records from both tables, matching when you can, NULL values where you can't.

Like the "INNER" in "INNER JOIN," the word "OUTER" is actually optional in each of the above queries. You'll see the exact same results whether you type "LEFT JOIN" or "LEFT OUTER JOIN." Again, I think using the full syntax makes your queries more readable and that you're better off with it than without (especially while you're still learning).

Ok, so you've got your INNER JOIN and your OUTER JOIN, and they pretty much cover 99.999999% of the JOINs you'll ever write. However, I promised you a third JOIN when we started this adventure, and I'm a nerd who delivers. I bring you...

3. CROSS JOIN: The CROSS JOIN gives you every possible combination of results from the two tables involved, and in all honesty, I've never ever had to use it in real life. I only mention it thoroughness's sake and I can't even think of a situation in which it might be useful. If you've got one, please please let me know about it. Dying to hear.

Syntax for the CROSS JOIN is a little different. Because you don't really care if any records from table A match anything in table B, you omit the ON keyword and everything after it:

SELECT o.firstname, o.lastname, s.registry, s.name FROM officer o

CROSS JOIN starship s

I'm not going to bother with the result set. With CROSS JOINs, they get really big, really fast, returning the number of records equal to the total in table A multiplied by the total in table B. For instance, our example with six guys in our OFFICER table and four clunkers in the STARSHIP table, the result set of our CROSS JOIN yields 24 records. Now imagine a CROSS JOIN between a table with 100,000 customers and 200,000 orders. For you folks playing along at home, that little query would give you 20,000,000,001 problems (the 20 billion records returned, plus one pissed off DBA).

And that's where our story ends, at least for today. Next time, in a much shorter post, maybe we'll talk about how you might use JOINs in UPDATE and INSERT statements. Or maybe I'll just bust our "Wrath of Khan" and ignore this place for awhile. Who knows.