News: PostgreSQL Quick Start

Introduction

PostgreSQL is a very popular Open-Source database management system. It competes heavily with many proprietary and commercial database management systems. More information on what it is and it's history can be found here and here.

This article is intended to get anyone unfamiliar with PostgreSQL up to speed. It will not go into much detail, but will cover the basics of creating a database, populating it with data and some other basic SQL commands.

Assumptions and Prerequisites

• It is assumed the reader at least knows what a database management system is.
• This article is written from a Linux point of view on a home (private) computer. It is distribution independent. Using it on other (non-Unix like) operating systems shouldn't differ to much.
• Most of this tutorial is based on command line utilities, except step 1 where you will need to assign privileges to normal users.
• PostgreSQL isn't neciseraly installed by default on any Linux distribution. Most of the time you will have to install it yourself if you are using it on your private computer (or your employer will most probably have it installed already if you are using it at work). It consists of a server and a client.

Time to begin

So let's start with creating a database, then we need to add tables, and then populate those tables with data. Then we will view the data by creating simple queries. Next we will alter some data.

Step 1 Creating the database

One thing that might cause some headaches with PostgreSQL is creating the databases as normal users. By default the client will try and create a database using the user account you are currently logged into Linux with. Your user account needs to be given CREATEDB permissions by a superuser. The easiest way to do this is to use a graphical client like pgAdmin to assign yourself with the needed permissions.

Once you have the needed permissions, you can use the client program createdb to create the database. The syntax will look like follow:

$ createdb mydatabase

The database is now created and ready to be connected to in order to create some tables

Step 2 Connecting to the database just created

We now need to connect to the database we created in Step 1 above. This can be done using any PostgreSQL client, graphical or command line, but here we will use the command line client psql. An example of a graphical client is pgAdmin.

The syntax for the psql client is as follow:

$ psql mydatabase

The client then connects to the database specified in mydatabase

Step 3 Creating tables for the database

A Database consist of at least one table, and can have up to several tables. We now need to create a sample table with columns before we can populate any data. After connecting to the database, you will see a prompt like this:

mydatabase=>

To create your table, use this as an example:

mydatabase=> create table "player" (

mydatabase(> "playerid" integer not null,

mydatabase(> "playername" varchar(128) not null,

mydatabase(> "playerteam" integer not null default '5',

mydatabase(> constraint player_pk primary key ("playerid")

mydatabase(> );

If any errors was encountered (syntax etc), you will receive an error message, otherwise you will see TABLE CREATED.

To verify the table and columns, in the psql client, type \d tablename and you will be presented with the table's information.

Step 4 Populating a table with data

To populate some data into the table, use the following syntax:

> INSERT INTO player VALUES ('12345', 'John Doe', '12');

The order of the values you insert should be the same order as the columns in the database. You can mix the columns around by using:

> INSERT INTO player ("playername", "playerid") VALUES ('John Doe', '12345');

The columns left out will take the default value you specified while creating the table, or 'NULL'. If the column is a 'NOT NULL' column, you will receive an error, and will have to enter a value in order to continue.

Step 5 Modifying Data

You can modify data in the database by using the example below:

> UPDATE player SET playerteam = 7 WHERE playername = 'Peter Pan';

Step 6 Extracting or viewing data

To view data in the database, use the SELECT statement:

> SELECT * FROM player;

This will show all entries in that table. To narrow the amount of results, or display only certain columns, you can do something like:

> SELECT playername, playerteam FROM player WHERE playerid = '12345;

or

> SELECT playername FROM player WHERE playerteam = 11;

Conclusion

This was just a limited subset of SQL commands, intended to give you a quick start with PostgreSQL. There is off course a lot more to learn, and a lot more possibilities of what can be done with PostgreSQL (or any SQL database for that matter).

Usefull help can be obtained from within psql by using \h and enter.

For more information or some tutorials on PostgreSQL, visit the official website here.