Using Postgre SQL

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.

Accessing the Sever

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

, you use the @@psql@@ command from the terminal command-line. To connect from a department workstation, enter the following command at the Linux prompt

[@ psql -h pascal dbname @]

where @@pascal@@ is the name of the department's Linux server and @@cs236@@ is the name of the database to which you are connecting. You can only connect to one database at a time.

<note>

has it's own user accounts and passwords that are managed as part of the database server. On the Linux system, your user account name is automatically used by

as your database account name. But it uses different passwords. </note>

You will be prompted for your database system password. As you type the password, no characters will be displayed on the terminal, but they are being entered. Press ENTER after typing your password. Upon a successful connection, you should see the

command-line prompt

[@ cs236=> @]

which accepts queries to be performed and commands for working with the DBMS. To disconnect from the

server, simply enter the @@\q@@ command at the command-line prompt

[@ cs236=> \q @]

Changing Your Password

You need to change your initial password to something more secure than the one I chose for you. At the @@psql@@ prompt, enter

[@ cs236=> \password @]

You will be prompted for your new password. After entering your new password, you will be prompted to enter it again. Enter the new password a second time, after which you will be returned to the @@psql@@ prompt.

Working With Schemas

Most client/server enterprise level databases provide some mechanism for creating and using sub databases. This allows for easier management and security on a single database in which one person or a group needs to create and work with multiple databases. In

, tables are created and belong to a specific ''schema''. You can think of a schema as a sub database or container within the outer database into which a table can be constructed. You can view a list of the schemas within the database using the ''psql'' command @@\dn@@.

<note> The tables for the various databases used in class are defined within schemas in the @@cs236@@ ''

'' database. You have limited permissions on the ''cs236'' database. For the @@gradebook@@ schema, you have read (select) and temp creation (create temporary tables/views) permissions. </note>

All SQL commands are executed in

within the context of a ''current'' schema. In order to use simplified query commands, you need to set the search path to the appropriate schema which identifies the ''current schema'' similar to the ''current working directory'' on UNIX systems.

(:source lang=sql:)[@ SET search_path TO gradebook; @]

You can view the list of schemas in the database using the

DBMS command @@\dn@@. You will notice a number of "system" schemas in addition to the ''gradebook'' schema. To view the current "search path" (current schema), use the command

(:source lang=sql:)[@ SHOW search_path; @]

<warn> The @@search_path@@ setting is not permanent and must be set each time you connect to the database. By default, all SQL commands use the "search path" to locate objects such as tables and views. If your search path is not set correctly, you may inadvertently query or manipulate the wrong objects. </warn>

Common Commands

The command-line provided by the

client application is not only used to enter SQL commands, but also various DBMS commands. In

these commands between with a backslash (@@\@@).

:@@\q@@: Quit the @@psql@@ client.

:@@\?@@: Displays help on the DBMS commands.

:@@\h@@: Displays 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@@: List the tables in the current schema.

:@@\dn@@: List the schemas in the database.

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

:@@\l@@: List all the databases.