Dondi: /* Running PostgreSQL on the Lab Machines */ It actually is 9.2 :-\

2015-10-01T21:38:29Z

‎Running PostgreSQL on the Lab Machines: It actually is 9.2 :-\

Dondi: Small title correction.

2015-10-01T21:19:35Z

Small title correction.

Dondi: /* Creating a Database */ Version update.

2015-08-30T06:43:42Z

‎Creating a Database: Version update.

Dondi: /* Running PostgreSQL on the Keck Lab Windows Machines */ Slight version update.

2015-08-30T05:42:32Z

‎Running PostgreSQL on the Keck Lab Windows Machines: Slight version update.

Dondi: Initial transcription.

2015-08-30T05:42:12Z

Initial transcription.

New page

This page gives you a tutorial-style walkthrough for using PostgreSQL. The walkthrough assumes that you’ve been set up for PostgreSQL use within the Keck lab infrastructure.

But first, a little leadoff cartoon: http://xkcd.com/327

== Running PostgreSQL on the Keck Lab Windows Machines ==

# Login to the computer as usual
# From the ''Start''/Windows icon menu, launch '''pgAdmin III'''
# The '''pgAdmin III''' window starts with a hierarchical view on the left that starts with three layers:
#* Server Groups
#** Servers (1)
#*** PostgreSQL 9.2 (localhost:5432)
# Double-click on ''PostgreSQL 9.2 (localhost:5432)'' to connect to the database server
# The password to start the server is simply ''keck''

== Creating a Database ==

Once the server is running, the red '''x''' disappears from the ''PostgreSQL 9.2 (localhost:5432)'' icon, and additional icons appear beneath it. If you click on the '''+''' button to the left of the ''Databases'' icon, you will see the databases that are currently available. Initially, you will see a single database called '''postgres'''.

To do your work and practice some SQL, it is recommended that you work on a database of your own. To create a database, right-click on the ''Databases'' icon and choose ''New Database...'' from the menu that appears. In the '''New Database''' dialog, the only information you need to supply is your new database's name. To avoid confusion in case multiple students use the same computer, use your Keck lab username as the name of your database.

When you click '''OK''', you will return to the main '''pgAdmin III''' window and you should see your new database underneath the ''Databases'' icon.

Note that you only need to go through this creation process once; that database will remain available until it is explicitly deleted.

== Connecting to a Database ==

To start using a database, click on its icon. The red '''x''' disappears from the database icon and you should now be able to work.

== Walkthrough: Loading the Sample Movie Table Into Your Database ==

Before we can dive into SQL, we need to set up some information that we can access. In doing this, you will see how data in a plain text file can find its way into a full-fledged relational table.

We will load up the ''movie_titles.txt'' file in ''~dondi/xmlpipedb/data'' into your own database. If you '''cat''' that file, you will see that it looks like this:

17761,2003,Levity
17762,1997,Gattaca
17763,1978,Interiors
17764,1998,Shakespeare in Love
17765,1969,Godzilla's Revenge
17766,2002,Where the Wild Things Are and Other Maurice Sendak Stories
17767,2004,Fidel Castro: American Experience
17768,2000,Epoch
17769,2003,The Company
17770,2003,Alien Hunter

(this is from the end of the file)

Looking at the information, we recognize that this file consists of a movie ID, a year, and a title. Thus, we need to prepare a table with these columns in our database.

=== Create the Movie Table ===

Switching back to '''pgAdmin III''', click the SQL button in the toolbar. A new window with an '''SQL Editor''' tab appears. The following command will create your ''movie'' table; type this into that tab:

create table movie (id int primary key, year int, title varchar)

As always, watch out for typos! When ready, click on the '''Execute query''' button in the toolbar. (its button looks like a green play button)

Upon executing the query, the following should appear in the '''Messages''' tab of the ''Output Pane'' in the bottom half of the window:

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "movie_pkey" for table "movie"
Query returned successfully with no result in 101 ms.

To assure yourself that the ''movie'' table is indeed there, type and execute this query:

select * from movie

The '''Data Output''' tab of the ''Output Pane'' should now show an empty tabular display with headings for '''id integer''', '''year integer''', and '''title character varying'''.

=== Prepare Data for Insertion into the Movie Table ===

At this point, you have a ''movie'' table, but no data: that information currently resides in ''movie_titles.txt'' over in the Keck lab's ''my.cs.lmu.edu'' server. How do we get that data into our ''movie'' table?

Recall that the SQL command for adding data has this format:

insert into '''''table''''' ('''''columns''''') values ('''''values''''')

In other words, this text data:

17761,2003,Levity
17762,1997,Gattaca
17763,1978,Interiors
17764,1998,Shakespeare in Love
17765,1969,Godzilla's Revenge
17766,2002,Where the Wild Things Are and Other Maurice Sendak Stories
17767,2004,Fidel Castro: American Experience
17768,2000,Epoch
17769,2003,The Company
17770,2003,Alien Hunter

...must be made to look like this:

insert into movie(id, year, title) values (17761,2003,'Levity');
insert into movie(id, year, title) values (17762,1997,'Gattaca');
insert into movie(id, year, title) values (17763,1978,'Interiors');
insert into movie(id, year, title) values (17764,1998,'Shakespeare in Love');
insert into movie(id, year, title) values (17765,1969,'Godzilla''s Revenge');
insert into movie(id, year, title) values (17766,2002,'Where the Wild Things Are and Other Maurice Sendak Stories');
insert into movie(id, year, title) values (17767,2004,'Fidel Castro: American Experience');
insert into movie(id, year, title) values (17768,2000,'Epoch');
insert into movie(id, year, title) values (17769,2003,'The Company');
insert into movie(id, year, title) values (17770,2003,'Alien Hunter');

Note a few rules here that might not be very obvious:
* Text values need to be enclosed between single quotes; numbers don't.
* Note, in the case of movie 17765, ''Godzilla's Revenge'', that the ''movie title itself'' contains a single quote (apostrophe). To distinguish an “in-text” apostrophe from a “wrapper” apostrophe, we double it up; that's why the SQL above shows ''Godzilla's Revenge'' with the apostrophe converted into two.
* When performing multiple SQL queries, the semicolon (;) is used to distinguish one query from another. Think of the semicolon as playing the same role that periods (.) do in plain English sentences.

Hmmmmm...do we have a program that can do this? We need to take the lines in the ''movie_titles.txt'' file and convert them into valid SQL ''insert'' commands. Why yes we do, and you have already used it: '''sed'''.

Since this is a tutorial, we won't spend time to explain exactly how we come up with the '''sed''' command below. But you should be able to see the pieces:
* We need to turn all single apostrophes into doubles.
* We then need to “wrap” the titles at the end around single quotes.
* Finally, we append the ''insert'' command before each line, and
* end each line with a parenthesis and semicolon.

Finally, we somehow need to get the data into the PostgreSQL server running on your workstation. For this, we will use the built-in web server of the ''my.cs.lmu.edu'' host: we will deposit the results of '''sed''' into a file that a ''web browser'' can then display. From the web browser, we can copy and paste the ''insert'' statements into the '''pgAdmin III''' SQL window.

All that said (no pun intended), this is the command that you want (invoke it from ''~dondi/xmlpipedb/data''):

cat movie_titles.txt | sed "s/'/''/g" | sed "s/,/,'/2" | sed "s/^/insert into movie(id,year,title) values(/g" | sed "s/$/');/g" > ~/public_html/movie.sql.txt

At this point, you can probably work out the '''sed''' commands. The portion we will explain just a little bit more is the last section, <code>> ~/public_html/movie.sql.txt</code>. We have not needed to use the '''>''' symbol before, but now it is just what we need: it “sends” the result of the prior '''sed''' commands into a file. That file is placed in your ''public_html'' folder, which, if you recall, is visible on the web as http://my.cs.lmu.edu/~your_username/.

=== Insert! ===

Finally, we can feed these 17,770 ''insert'' statements (quick, how did we know this?) into PostgreSQL. Open a new browser tab or window and go to http://my.cs.lmu.edu/~username/movie.sql.txt (remember to substitute ''username'' with your Keck lab ''ssh''/''PuTTY'' login. You should see your fresh '''sed''' product in the browser. From here, '''Select All''' and '''Copy''' the commands.

Switch to the '''pgAdmin III''' SQL window, empty out the '''SQL Editor''' tab, then '''Paste''' your ''insert'' statements into the tab. Finally, execute the query (the green play button, remember?) and let it work. With 17,770 records, this takes a ''little'' bit longer than prior commands that you have run.

In the end, the '''Messages''' tab in the ''Output Pane'' should say something like:

Query returned successfully: one row affected, 2325 ms execution time.

(exact execution times will vary)

Once more, check your work; re-execute this:

select * from movie

This time, you should see a fully-populated '''Data Output''' tab, with...17,770 rows.

== Walkthrough: Adding a Few More Tables ==

To make our database a little more interesting, will create a couple more tables and load them with some data. The rest of the tutorial will be based on these tables and their content.

At this stage, you’ve already seen these commands in some form, so we’ll skip the explanation and go right to the commands. Copy, paste, and run this (you successfully copied and pasted 17,770 ''insert'' commands previously, so this should be no sweat!):

create table member (id int primary key, name varchar);
create table rating (movie int references movie(id), member int references member(id), rating int);
insert into member(id, name) values (6, 'Natalie');
insert into member(id, name) values (8, 'Boomer');
insert into member(id, name) values (42, 'Doug');
insert into rating(movie, member, rating) values(209, 6, 5);
insert into rating(movie, member, rating) values(2040, 6, 5);
insert into rating(movie, member, rating) values(6908, 6, 2);
insert into rating(movie, member, rating) values(2610, 6, 1);
insert into rating(movie, member, rating) values(8809, 6, 2);
insert into rating(movie, member, rating) values(37, 6, 4);
insert into rating(movie, member, rating) values(113, 6, 2);
insert into rating(movie, member, rating) values(8687, 8, 1);
insert into rating(movie, member, rating) values(9628, 8, 5);
insert into rating(movie, member, rating) values(10877, 8, 2);
insert into rating(movie, member, rating) values(12513, 8, 1);
insert into rating(movie, member, rating) values(15923, 8, 2);
insert into rating(movie, member, rating) values(15532, 8, 3);
insert into rating(movie, member, rating) values(4006, 8, 3);
insert into rating(movie, member, rating) values (30, 42, 4);
insert into rating(movie, member, rating) values (113, 42, 5);
insert into rating(movie, member, rating) values (37, 42, 5);
insert into rating(movie, member, rating) values (4765, 42, 5);
insert into rating(movie, member, rating) values (6762, 42, 3);
insert into rating(movie, member, rating) values (6853, 42, 4);
insert into rating(movie, member, rating) values (10176, 42, 4);
insert into rating(movie, member, rating) values (13847, 42, 5);
insert into rating(movie, member, rating) values (15127, 42, 3);
insert into rating(movie, member, rating) values (15532, 42, 1);

You should see the customary success messages once the commands are done. Time to play!

== An Introduction to SQL Select Queries ==

Database activities are triggered via ''SQL'' commands. The previous ''SQL'' PDF handout gives you more of a reference/nutshell view of SQL; this page walks you through some commands step-by-step, using the sample movie database that you set up earlier in this page.

The ''select'' command is the SQL “kitchen sink” for retrieving information from a database. Its general, basic form is:

select '''''columns''''' from '''''tables''''' where '''''conditions'''''

As you will see, ''select'' can do even more, but let’s start simple.

=== Basics ===

The simplest type of ''select'' queries involve getting records from an individual table based on relatively simple conditions. For example:

select year from movie where title = 'Metropolis';

...will retrieve the release years of movies whose titles are ''exactly'' “Metropolis.” If you try this query, you should see two rows: one for 2001 and another for 1927.

In addition to equality, ''like'' lends further flexibility. The ''like'' comparison allows for pattern matching, similar but not identical to '''grep'''. In SQL, the percent sign (“'''%'''”) is a “wildcard” that can represent any number of letters and symbols. ''like'' and '''%''' can be combined for broader queries, such as this one, which retrieves all movie titles that have the word “Vampire” in them:

select title from movie where title like '%Vampire%';

If you want ''select'' to display ''all'' columns of a database record, use the asterisk ('''*'''):

select * from movie where title like '19%';

This will display ''every'' column/field of movie records whose title starts with “19”—these mostly appear to be films pertaining to a specific year in the 20th century.

Conditions can be combined via ''and'' and ''or''; for example, retrieving movies whose title contains ''either'' “DNA” or “Bio” can be done with:

select * from movie where title like '%DNA%' or title like '%Bio%';

When using more than two conditions, watch out for how conditions are grouped together; this query, for example, may yield unexpected results:

select * from movie where title like '%DNA%' or title like '%Bio%' and title like '%:%';

At face value, the above query may read like “movies whose titles have either DNA or Bio which also have a colon (''':''');” in reality, the colon criterion is only ''and''-ed with movies that have “Bio” in the title. Thus, the database actually interprets this query as “movies whose titles have DNA, ''or'' whose titles have Bio and a colon.”

To eliminate any ambiguities, use parentheses to group conditions together:

select * from movie where (title like '%DNA%' or title like '%Bio%') and title like '%:%';

These parentheses force the database to pick out movies with either title first, and ''then'' check if these movies have a colon in their titles.

The ''like'' comparator can do simple text matches, but it does ''not'' use regular expressions (i.e., the search patterns recognized by '''grep''' and '''sed'''). This area is a little shaky in SQL-land; there is an official ''similar to'' comparator which is the official way to make regular expression comparisons, but the format for those expressions is not the same as the format used by '''grep''' and '''sed'''.

Fortunately, PostgreSQL has a specific, PostgreSQL-only comparator that ''does'' match the same patterns used by '''grep''' and '''sed''': the tilde ('''~'''). Comparing with '''~''' is equivalent to a '''grep'''- or '''sed'''-like comparison:

select * from movie where title ~ 'Vamp[iy]re';
select * from movie where title ~ 'End$';
select * from movie where title ~ 'Colou?r';

The caveat here is that '''~''' is a PostgreSQL-specific feature: if you move to other database systems (such as Microsoft Access), that feature may either be done differently or missing completely, since it is not part of the official SQL standard.

Numeric values, such as the ''year'' column, can be compared using ''='', ''<'', ''>'', ''<='' (greater than or equal to), ''>='' (less than or equal to), and ''<>'' (not equal):

select * from movie where year = 1960;
select * from movie where year < 1930;
select * from movie where year >= 1960 and year < 1970;

=== Sorting ===

As you play with various queries on the ''movie'' table, you’ve probably noticed that results are returned in no particular order; if you’d like to sort the results in some way, tack on an ''order by'' clause at the end of the ''select'' query:

select * from movie where year > 2000 order by year

This will display the records/rows for movies released from 2001 onward, sorted by year. You can add more fields for a very specific sort order:

select * from movie where year > 2000 order by year, title

The above query returns the same records, but this time sorted by year first, then by title ''within'' each year.

Sort order is ascending by default (e.g., A to Z, 0 to 9); for the reverse order, add ''desc'' to the field(s) that you’d like to see sorted in reverse:

select * from movie where year > 2000 order by year desc, title;

This will display records with years displayed most recent first; within each year, however, titles will still be sorted in ascending order.

=== Aggregate Queries ===

For large databases, SQL provides ''aggregate'' (a.k.a. “grouping”) queries that summarize multiple records in different ways.

The simplest form of summary is counting: ''how many'' records were retrieved? A simple overall count is done by using ''count(*)'' as the thing to select (or ''project'', in formal relational algebraic terms):

select count(*) from movie where year > 2000

This will display the number of movies released from 2001 onward.

Of course, you can mix and match everything you have learned so far. To count the movies whose titles begin with a “B” that were released in 1975, you can query:

select count(*) from movie where title ~ '^B' and year = 1975

Aggregators other than ''count'' are available, such as ''min'', ''max'', and ''avg'' (average or mean). Though somewhat odd-sounding, you can ask, for example, for the “average year” of movies with “London” in the title:

select average(year) from movie where title like '%London%'

Finally, you can aggregate ''multiple groups'' of data, so that you get different sets of statistics. The ''group by'' keyword does this:

select year, count(*) from movie where year < 1935 group by year

The main rule with ''group by'' is that the column being grouped should also be part of the ''select'' clause. This makes sense because otherwise, you wouldn’t be able to tell which group was which! And of course, you can mix and match. For example, the query above makes more sense if we arrange the data chronologically:

select year, count(*) from movie where year < 1935 group by year order by year

See any trends in that data?

=== Joins ===

Thus far, we’ve only been working with one table in the database: ''movie''. Our other tables, ''member'' and ''rating'', add some further interest. These tables store ratings, from 1 to 5, made by individual members on movies they have seen.

Examination of the ''rating'' table reveals that it has ''movie'', ''member'', and ''rating''. Thus, each record consists of a single rating, made by a particular member for a particular movie. Staying with one table for now, this query will list all of the ratings submitted by member no. 6:

select * from rating where member = 6

This returns the expected answer (based on the ''insert''s that you copy-pasted previously), but you may have noticed that the result isn’t quite as meaningful to us, since we get movie ''IDs'' back instead of ''titles''. These movie titles, however, are in the ''movie'' table, not ''rating''. We thus need to ''join'' the two tables. As you might have seen when looking at the schema of the ''rating'' table, the ''movie'' field is a ''foreign key'' to the ''id'' field in the ''movie'' table. Thus, every ''movie'' value in the ''rating'' table matches some ''id'' in the ''movie'' table, thus leading us to that movie’s title.

An SQL ''join'' uses the same basic ''select'' command, but requires the tables being joined (in this case ''movie'' and ''rating''), as well as the fields/columns to use for “joining” records. “Joining” records means that their fields are combined to create a new “virtual” record. All other parts of the ''select'' command retain the same meanings as before:

select '''columns''' from '''table1''' inner join '''table2''' on ('''join condition''') where '''conditions'''

Thus, the previous query, modified to retrieve the titles and ratings of the movies rated by member no. 6, looks like this:

select title, rating from movie inner join rating on (movie.id = rating.movie) where member = 6

As in the basic ''select'' command, you can tailor the ''where'' conditions as you need to pull ratings based on other criteria. For example, ratings for a particular movie, as opposed as for a particular member, can be retrieved by changing the ''where'' clause. The query below displays all ratings for the movie(s) whose title has an apostrophe (see the last section below for more on this):

select rating from movie inner join rating on (movie.id = rating.movie) where title like '%<nowiki>''</nowiki>%'

Note that the ''member'' column of the ''rating'' table is a foreign key as well—it refers to the ''id'' column of the ''member'' table. The ''member'' table in our sample only includes member names, but in practice it can hold much more information. We can join more than once; extending the query above, we can now produce all ratings for the movie(s) whose title has an apostrophe, ''and'' list the member names who gave those ratings:

select name, rating from movie inner join rating on (movie.id = rating.movie) inner join member on (member.id = rating.member) where title like '%<nowiki>''</nowiki>%'

Note how this approach helps avoid redundancy: instead of copying a member’s name over and over again, we can store the member’s name in one place, and use their ID as a reference to that name. This elimination of redundancy is called ''normalization''—tables are ''normalized'' if they eliminate data redundancy in their columns (except, of course, for foreign keys).

=== The Notorious Apostrophe ===

You might have noticed that, because the apostrophe or single quote is used to indicate specific values in SQL (e.g., 'The Godfather', 'Smith', '6/30/1980', etc.), we run into a potential problem when the value itself should contain an apostrophe. This is not as uncommon as one might think; for example, a good number of movie titles have apostrophes (''By Dawn's Early Light'', ''Zatoichi's Conspiracy'', ''Logan's Run'', and ''Dead Men Don't Wear Plaid'', to name a few), as do many names (“O'Malley,” “M'Benga,” “D'Angelo”). An ''insert'' command such as the one below will result in an error, since the apostrophe will be misinterpreted as ending a piece of text rather than as part of the text itself:

insert into person(id, firstname, lastname, dob) values(2000, 'Beverly', 'D'Angelo', '8/21/1960');

Fortunately, SQL has a solution: apostrophes ''inside'' text should be indicated via ''two consecutive'' apostrophes, or '''<nowiki>''</nowiki>'''. When encountered, SQL converts this pair of apostrophes into a single one, and does not interpret these apostrophes as ending a piece of text. Thus, the above command will work if rewritten in this way:

insert into person(id, firstname, lastname, dob) values(2000, 'Beverly', 'D<nowiki>''</nowiki>Angelo', '8/21/1960');

While the solution does exist, it isn’t automatic: you need to be aware that apostrophes have to be written as “double apostrophes” before passing any text values on to SQL. Keep this in mind when trying to load data from a text file into a database table.

@@ Line 10: / Line 10: @@
 #* Server Groups
 #** Servers (1)
-#*** PostgreSQL 9.4 (localhost:5432)
+#*** PostgreSQL 9.2 (localhost:5432)
 # Double-click on ''PostgreSQL 9.4 (localhost:5432)'' to connect to the database server
 # The password to start the server was given in class

@@ Line 16: / Line 16: @@
 == Creating a Database ==
-Once the server is running, the red '''x''' disappears from the ''PostgreSQL 9.2 (localhost:5432)'' icon, and additional icons appear beneath it.  If you click on the '''+''' button to the left of the ''Databases'' icon, you will see the databases that are currently available.  Initially, you will see a single database called '''postgres'''.
+Once the server is running, the red '''x''' disappears from the ''PostgreSQL 9.4 (localhost:5432)'' icon, and additional icons appear beneath it.  If you click on the '''+''' button to the left of the ''Databases'' icon, you will see the databases that are currently available.  Initially, you will see a single database called '''postgres'''.
 To do your work and practice some SQL, it is recommended that you work on a database of your own.  To create a database, right-click on the ''Databases'' icon and choose ''New Database...'' from the menu that appears.  In the '''New Database''' dialog, the only information you need to supply is your new database's name.  To avoid confusion in case multiple students use the same computer, use your Keck lab username as the name of your database.

PostgreSQL Tutorial - Revision history

Dondi: /* Running PostgreSQL on the Lab Machines */ It actually is 9.2 :-\

Dondi: Small title correction.

Dondi: /* Creating a Database */ Version update.

Dondi: /* Running PostgreSQL on the Keck Lab Windows Machines */ Slight version update.

Dondi: Initial transcription.