[Courses] [Postgresql] Finally, Week 3!

[Michelle Murrain]

Hi all,

Sorry for the delay! I'll also be responding by email and moodle to last 
week's assignments over the next few days. Since I got this in so late, 
you have until Wed to do the assignment. I will endeavor to get back on 
track next week.

Also - IRC sessions start next week! First one is on Monday December 
10th at 5:00 pm EST. I hope that will work out for folks. Go to 
irc.linuxchix.org - we'll be in the #courses channel.

This week we'll cover the basics of the PostgreSQL client, introduce you 
to the idea of schemas, and do some simple SQL.

**Postgresql Client**

The PostgreSQL client, which is a command line tool that allows complete 
access to databases, is called 'psql'. If you are the postgres user, the 
command: $ psql test

Should result in something like:
   Welcome to psql 8.2.5, the PostgreSQL interactive terminal.
   Type:  \copyright for distribution terms
          \h for help with SQL commands
          \? for help with psql commands
          \g or terminate with semicolon to execute query
          \q to quit
   test=#

Where the # (or > sometimes) is the prompt for commands.

The basic command is: psql [option] [dbname [username] ]

The options are not often used - check the man pages ($ man psql) for info.

If 'dbname' is omitted, the dbname that is chosen is the one that is 
called by the same name as the username.
If 'username' is ommitted, the assumed database user is the user who is 
logged in.

So, if you've just installed postgresql, the only database user is the 
user 'postgres'. So in order to use psql, you will have to be that user.

The first thing you should do is create a user for yourself.

   test=#create user username with password 'good_password_here';

This will allow your normal unix user to have access to postgresql. This 
is your first SQL statement! (more on user permissions and creating 
users later.)

Once you've gone back into psql with your normal user ($ psql test), 
have a look at the commands available, by using /? (very helpful). The 
ones you'll probably do most often are /l (listing all databases), /d 
(listing all relations in one database), /c (connecting to other 
databases.) \h gives you some help on SQL commands. More on that later.

In a new install, this is what you should see:

  test=> \l
         List of databases
      Name    |  Owner   | Encoding
   -----------+----------+----------
    postgres  | postgres | UTF8
    template0 | postgres | UTF8
    template1 | postgres | UTF8
    test      | postgres | UTF8
   (4 rows)

None of these databases have visible tables (there are a whole host of 
hidden tables - more on that in a few weeks).

**Schema**

So what is a schema? A schema is, basically, the structure of a 
database. Here is an example schema:

   Field Name    |   Type
   --------------+---------------
   userid        |  int
   firstname     |  varchar(20)
   lastname      |  varchar(20)
   address       |  varchar(100)
   city          |  varchar(20)
   stateprov     |  varchar(20)
   postalcode    |  varchar (15)
   totalspent    |  real
   comments      |  text
   code          |  char(2)

I made this up out of whole cloth, but it's just to give you a very 
simple example of a schema for a table. We'll start with very simple 
tables (we'll do relations between tables in a week or so). The data 
types I've used here are easy ones. "int" are 4 byte integers, "real" 
are 4 byte floating point, char are fixed length, and varchar are 
variable length (in other words, the 'char' types will use up that space 
no matter how many actual characters are in the string, whereas varchar 
will only use up as much room as the string takes.) Text is unlimited 
length text.  We will go much more deeply into data types later. So how 
would you create this table?

**Creating Tables**

The SQL command is "create table", and it would go like this:

   test=>create table testtable1 (
   test->

OK, a quick thing. Every command in postgresql has to be terminated with 
a semicolon. When psql thinks you are in the middle of a SQL command, it 
will give you a different prompt. You can always escape out of it by 
using Control-C. Whatever the command you were in the middle of will be 
aborted.

   test->userid int,
   test->firstname varchar(20),
   ...
   test->code char(2) );

A create table command always has parenthesis around the fields. If you 
want the full skinny on CREATE TABLE, see 
http://www.postgresql.org/docs/8.0/static/sql-createtable.html

then:

test=> \d
           List of relations
    Schema |    Name    | Type  | Owner
   --------+------------+-------+-------
    public | testtable1 | table | mpm
   (1 row)

And \d testtable1 will give you the schema of the table.

A note: http://www.postgresql.org/docs/ is your best friend. It is 
extremely well written - you'll find all sorts of great info there.

Next week: putting data in, getting it out, and creating relationships 
between tables.


Assignments:

   - Create a user that will be your standard user for the database
   - design a basic table that might be useful for your project. This is 
a draft - because undoubtedly, once you've learned more, you'll want to 
change your table. Use just the data types I've introduced, unless you 
are adventurous, and want to skip ahead 
(http://www.postgresql.org/docs/8.0/static/datatype.html)
   - Create that table

Extra credit:
   - Create a table with an autonumbered field and a primary key. How 
does this work in postgres?
   - Use the edit buffer in psql to create a table (hint - \e)

Have fun!!

Peace,
Michelle
-- 
Michelle Murrain
Coordinator, Nonprofit Open Source Initiative
http://nosi.net

Skype: pearlbear
AIM: pearlbear0