Accessing the PostgreSQL database

Once you have created the database, you can access it:

• Terminal program psql , which runs PostgreSQL interaction, allows you to interactively enter, edit, and execute SQL commands.

• Create and manage databases using our existing graphical front-end tools, such as pgaccess or office suites with ODBC support. This method is not covered in this tutorial.

• Write a customer app using one of several language bindings. These possibilities have a more in-depth discussion in part Iv.

You may need to start psql to experiment with the examples in this tutorial. You can activate it for the mydb database with the following command:

$psql mydb

If you omit the database name, it defaults to the name of your user account. You already know this in the previous section.

In psql , you will see the following welcome message:

Welcome to Psql 8.2.3, 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 mydb=>

The last line may also be

mydb=#

This prompt means that you are a database Superuser and most likely to be in the case of your own PostgreSQL installation. Being a superuser means that you are not restricted by access control. For the purposes of this tutorial, it doesn't matter if the superuser is super-user.

If you run into a problem when you start psql , go back to the previous section. The method of diagnosing Createdb is similar to the method of diagnosing psql , if the former can run then the latter should also be able to run.

The last line that psql prints is the prompt, which means that psql is listening to you, and this time you can type SQL queries into a psql maintained workspace. Try the following command:

mydb=>SELECT version();                            Version----------------------------------------------------------------PostgreSQL 8.2.3 on I586-pc-linux-gnu, Compiled by GCC 2.96 (1 row) mydb=> SELECT current_date;    Date------------2002-08-31 (1 row) mydb=> SELECT 2 + 2; ? column?----------        4 (1 row)

The psql program has some internal commands that are not part of the SQL command. They start with a backslash "\". Some of these commands are listed in the Welcome message. For example, you can use the following command to get the help syntax for various PostgreSQL SQL commands:

mydb=>\h

To exit psql , type:

mydb=>\q

Psql then exits and returns to the command line shell (for more information about internal commands, you can type \? ) at the psql prompt. The full functionality of the Psql is in the psql documentation. If PostgreSQL is installed correctly, you can also read the document by typing man psql at the shell prompt of the operating system. In this document, we will not explicitly use these features, but you can use them at the right time.

Accessing the PostgreSQL database