November 13, 2005
Anything described here is done with pgAdminIII, which is automatically installed when installing PostgreSQL.
We might as well start at the very beginning and create a server for these tests.
Go to the menu option 'File' and select 'Add Server...'. A dialog box will appear, which you will fill in the following way.
Address is where the PostgreSQL that you want to connect to is located. 'localhost' is the PostgreSQL name for the machine you are on. In this first example we stay on the same machine that will run Pragma.
Description is the name and description that you will give to the new server. Try to be descriptive. In this cabe it is my personal server on the machine I call BIGBOY.
Service you can leave blank since it does not seem to matter and I do not know what it really is.
Port is the number of the port PostgreSQL listens to. 5432 is the default and leave it like it is, unless you changed something in PostgreSQL.
Initial DB I choose template1 from the combo box, but I don't think that it matters foe now.
Username is the user that creates the new server and will have all the rights. PostgreSQL created the user postgres during the installation as a superuser and I continue to be postgres.
Need password Leave unchecked so that you will not need a password. It would only haunt you in the future.
You can always make some changes in the future.
Next you must create a database in your server. A database can then have as many tables you want.
In pgAdminIII inthe newly created server click on Databases (0) which means that the server does not have any databases yet. Then from the menu bar select the toolbox, which lets you create a new object of the same type as the selected object, in our case a database. A dialog box will appear, which you will fill in the following way.
Name is the name that you will give to the database and 'Pragma Tutorial' seemes just right. Just use something descriptive, not Mickey Mouse.
OID appered by itself and does not concern us. (I hope never)
Owner is very important. Keep things simple and use the same as the username of the server.
Encoding Use the default.
Template Leave blank.
Tablespace I chose pg_default, mainly because I don't really know what the tablespace is and you usually do not go very wrong if you use a default.
Comment is a nice touch to keep things tidy.
It is actually very nice and instructive to see how pgAdminIII created the new database. Just click on the 'Pragma Tutorial' database to see the SQL code that pgAdminIII sent to PostgreSQL to create the database.
In the well established Pragma tutorial tradition the first table to create is an address table. In SQL a table is more or less equivalent to a Pragma file and all the tables are grouped together in a database, in our case the 'Pragma Tutorial' database. A table consists of columns and rows, where the columns are the nouns of the file and create what we call a record..
The newly created database should be empty, but, as you can see from the number (2) besides Tables, when you expand your newly created database, it is not. It contains two tables that were created by default. Don't worry about them.
To create a new table highlight the Table(2) entry and click on the red toolbox that lets you create a new object of the same type as the selected object, in our case a new table. A pop up dialog box appears that lets you create a new table.
Fill the first page of the dialog box that appears in this way:
Name The name of the table. In our case 'addresses'.
Owner Keep things simple and use the same owner as the username of the server.
Tablespace Leave blank.
Has OID's Leave the default, unchecked. I don't know what it is (and don't want to know it right now).
Then click on the tab 'columns' and add all the columns you need. Same as adding nouns to a just created file in Pragma.
I added 11 columns to the table, all with self explanatory names. Again, the names are very important to be descriptive so that anyone looking at the addresses table will understand them.
All the columns are of the text type, since we want to be able to mix numbers and letters even in the phone fields. Number must not be null, since you must fill at least a column in a record.
The addresses table shows something that you should not do if you want to access the table from Pragma 6, namely use an underline in a column name, since you are not allowed to use an underline in Pragma 6.
This is the SQL code that pgAdminIII sent to PostgreSQL to create the database.
If you got this far the left hand side of pgAdminIII will look something like this:
Notice the lonely 'postgres' user at the bottom. Leave him alone and lonely but keep things simple.
To insert data into a table, row by row (a rather tedious job) you use the INSERT command.
Click on the pencil icon to write and then execute the following SQL query:
The good news is that before you execute the query you can check if the syntax is OK by clicking on the Explain query button of the menu bar. The bad news is it tells you the error but not how to fix it.
A rather tedious job, but you can save the query for further use so you don't have to start every time from scratch.
For our tutorial purposes it is enough that at this moment you add only two or three addresses. Later on we will use a much more efficient way to fill our table.
The simplest SQL command to read data from a table is to use the SELECT command in the following way:
You can even omit the last line 'order by city', and just enter 'select * from addresses', but the example shows how powerful SQL is, since you can order by any column.