Using Postgresql

• Connecting to the Server
• Common Commands

Connecting to the Sever

PostgreSQL like most modern DBMS uses a client/server model. The server manages the databases while the client provides an interface between the user and the database. The server is typically run as a daemon which is always running on the server system.

To use the DBMS, you must connect the client to the server. With PostgreSQL, you use the psql command

   psql -h serverhost -U username dbname

where

serverhost

is the name or address of the system running the PostgreSQL server.

username

is your database username (note this can be different from the host system username).

dbname

the name of the database to which you are connecting. You can only connect to one database at a time.

Search Path

After connecting to the server, you need to set the search path to the active schema. Otherwise, you will need to refer to preppend the schema name to the table names for every query. To set the search path, enter the command

   set search_path to schema_name;

where schema_name is the name of the schema (or database as used in class). For example, to work with the gradebook database, you would enter

   set search_path to gradebook;

Change Password

To change your password, use the \password command at the psql prompt.

   \password 

You will be prompted for the new password. Enter a new password and press ENTER.

Common Commands

The command-line provided by the PostgreSQL client application is not only used to enter SQL commands, but also various DBMS commands. In PostgreSQL these commands between with a backslash (\).

\q

Quit the psql client.

\?

Get help on the DBMS commands.

\h

Get help on the SQL commands. To get help on a specific command append the command after \h. For example, to get help on the SQL SELECT command, use \h select.

\dt

View a list of tables in the database.

\d table

Describe the attributes of the given table. (i.e. \d grade)