How to use Postgres

Page outline

More information

Basics

Let's start with a few basic things that are essential to know about Postgres. The links lead you to the corresponding section of the official PostgreSQL documentation, where you can find all the details. You may also want to read the (short) official tutorial.

Please make sure you are familiar with the concepts above before you attack the rest of this section.

Creating databases

Before you start using your database cluster, you have to create at least one database. The easiest way to do this, is to simply enter the following command after logging in:

user01@lsir-cis-pc1:~$ createdb
CREATE DATABASE
user01@lsir-cis-pc1:~$

That's it! Simple, wasn't it?

If you specify no name like we did above, the name of the newly created database is the same as your login name (in our case user01. Of course you can specify a different name if you want to create another database:

user01@lsir-cis-pc1:~$ createdb actors
CREATE DATABASE
user01@lsir-cis-pc1:~$

To see a list of all existing databases use the following command:

user01@lsir-cis-pc1:~$ psql -l
       List of databases
   Name    | Owner  | Encoding
-----------+--------+-----------
 actors    | user01 | SQL_ASCII
 template0 | user01 | SQL_ASCII
 template1 | user01 | SQL_ASCII
 user01    | user01 | SQL_ASCII
(4 rows)

user01@lsir-cis-pc1:~$

Don't worry about the templateX databases, you won't need them and you should leave them as they are.

Logging in and out

Now you can already log in to your database like so:

user01@lsir-cis-pc1:~$ psql
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

user01=#

This is the Postgres shell where you can enter SQL commands plus a few helper commands that you can list by entering \? and pressing enter. Note that all SQL commands must be terminated by a ;, just pressing enter is not enough. This is actually a very nice feature as you will see later.

To get help on a certain SQL command use the help function like this:

user01=# \h create database
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] dbowner ]
           [ LOCATION [=] 'dbpath' ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ] ]

user01=#

You can see that capitalization is generally not important as everything you enter is converted to lower-case before being processed. Exceptions are single and double quoted strings. More details about this can be found here.

To log out again you can simply enter \q or, even shorter, press Ctrl+D:

user01=# \q
user01@lsir-cis-pc1:~$

Now let's log into another database, not the default one. To do that you can use the following syntax:

user01@lsir-cis-pc1:~$ psql actors
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

actors=#

You can see from the prompt that you are now logged into a different database.

Creating tables

Let's now create a simple table containing two fields. Say we want to collect some basic information about actors, an identifier and the name:

actors=# CREATE TABLE Actors (
actors(#     ID INTEGER,
actors(#     Name VARCHAR(255)
actors(# );
CREATE TABLE
actors=#

You can now use the different commands to see a list of tables (\dt) or the details of a table (\d tablename):

actors=# \dt
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+--------
 public | actors | table | user01
(1 row)

actors=# \d Actors
            Table "public.actors"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 id     | integer                |
 name   | character varying(255) |

actors=#

You can see that Postgres has converted everything into lower case. If you wanted to preserve the case you'd have to write the commands like in CREATE TABLE "Actors".

Inserting data

After you have created the table you can start inserting data. For this you can (among other methods) use standard SQL commands.

actors=# INSERT INTO actors VALUES(1, 'Dorian Davis');
INSERT 17259 1
actors=# INSERT INTO actors VALUES(2, 'Melissa Dos Santos');
INSERT 17260 1
actors=# INSERT INTO actors VALUES(3, 'Raul Julia');
INSERT 17261 1
actors=#

If you have a lot of data to import or want to execute multiple commands at the same time you can store the commands in a text file and then run them all at the sime time. If you're already in Postgres, use the \i command. Say you want to import a file called actors-data.sql:

actors=# \i actors-data.sql
INSERT 17262 1
INSERT 17263 1
INSERT 17264 1
...
INSERT 17290 1
actors=#

Alternatively you can import a file directly from the shell like so:

user01@lsir-cis-pc1:~$ psql actors -f actors-data.sql
INSERT 17291 1
INSERT 17292 1
INSERT 17293 1
...
INSERT 17319 1
user01@lsir-cis-pc1:~$

Displaying data

You can display your data using the SQL SELECT command. Here is an example of how you can get a sorted list of all actors starting with an M:

actors=# SELECT Name FROM Actors WHERE Name Like 'M%' ORDER BY Name;
        name
--------------------
 Mayte Pelroni
 Megan Ferrara
 Melissa Dos Santos
 Melissa Dos Santos
 Michaela Saike
 Michael Orefice
(6 rows)

actors=#

Tip: Try hitting the tabulator key at different moments during the entry of an SQL command. Postgres will then give you a list of suggestions on what you can type next or (in case of disambiguity) automatically complete what you were about to type. It's magic! :-)

Creating a password

By default the PostgreSQL password for a newly created user is empty. This is also the case for the default user userXX that we have used above. If you want to access your database using Java like you need to for this project, you must set a password for your user, otherwise Postgres will refuse the connection.

To change the password of an existing user you can use the ALTER USER SQL command:

user01=# ALTER USER user01 PASSWORD 'mynewpassword';
ALTER USER
user01=#

Note that the password is not necessary when you log into Postgres locally from your lsir-cis-pc accounts. (For the technically interested: This is due to the line starting with local in the /var/lib/pgsql/dataXX/pg_hba.conf configuration file more information on which can be found here. Locally UNIX sockets are used whereas JDBC connections run over TCP/IP.)

If you want to test such an account you can force the use of TCP/IP (and therefore the password prompt) like this:

user01@lsir-cis-pc1:~$ psql -h localhost
Password: XXXXXX
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

user01=#

The -h option specifies localhost as the target and makes psql use a TCP/IP connection. You can also specify a different user using the -U option like this:

user01@lsir-cis-pc1:~$ psql -h localhost -U products_readonly products
Password: XXXXXX
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

products=#

Tip: For security reasons it is best to create an extra user for each given application and only grant it the minimal necessary privileges needed for the application to run. As an example, say you have an application that lists all products from a certain table but never changes the existing products. Then you could add a new user (e.g. products_readonly) and only grant it the SELECT privilege, but not those for UPDATE or INSERT. Now if something bad happens, like an intruder gets hold of the source code which contains products_readonly's password in cleartext, all he can do with it is see the product list, but not modify the database.

Managing your server

If for some reason you want to restart your Postgres server you can do this using the following command:

/sbin/service postgresqlXX restart

As always, replace the XX by your user number.

There is also a log file where you can see useful information output by your Postgres instance. It can be found in:

/var/lib/pgsql/dataXX/log/postgres.log

If you feel that the log is too detailed and becoming too large you can edit the postgresql.conf and comment out a few of the log statements starting from line 49. To apply the changes you have to restart your server like described above.

Using pgAdmin

For those of you who prefer a graphical user interface for certain tasks we have installed pgAdmin, a graphical administration tool for Postgres. If you want to work with it you have two possibilities. The first one is to use X forwarding, i.e. the transmission of the X11 protocol via SSH. To do this you can start the secure shell session and pgAdmin like this: (a little patience may be in order, X forwarding tends to be somewhat slow)

in1sun1# ssh -X user01@lsir-cis-pc1
user01@lsir-cis-pc1's password: XXXXXX
user01@lsir-cis-pc1:~$ pgadmin3&
[1] 26615
user01@lsir-cis-pc1:~$

Alternatively you can download pgAdmin III from the pgAdmin homepage and install it on your own computer. This solution is probably the better choice in terms of speed and comfort. There are packages for various Linux distributions and Windows available.

To get started managing your server choose Add Server... from the File menu. In the dialog be sure to enter the right address and port number (leave the Service field empty). From here on you are on your own. :-) Well, not quite; there's still the online support page.

That being said, note that in general most tasks can be done more efficiently using Postgres' text console, so don't jump to (possibly wrong) conclusions at the sight of the word GUI.

Using fuzzy string matching

There exist special functions in Postgres that provide functions for the following algorithms:

You can find more information about the corresponding Postgres functions in the README files in the fuzzystrmatch directory of the Postgres CVS repository.

However, they are not installed by default, so if you want to use these functions in your database please send a quick e-mail to Martin so we can activate it for your databases.

Copyright © Martin Rubli & Patrik Bless – Last change:
This page uses valid XHTML 1.0 Strict and valid Cascading Style Sheets, Level 2. This page uses valid XHTML 1.0 Strict. This page uses valid Cascading Style Sheets, Level 2.