Using PostgreSQL

Delete Data

Accessing 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 dbname

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

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.

	Note
	PostgreSQL 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 PostgreSQL as your database account name. But it uses different passwords.

To disconnect from the PostgreSQL server, simply enter the \q command at the command-line prompt

cs481=> \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

  cs481=> \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 PostgreSQL, 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 within the `cs236` PostgreSQL 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.

All SQL commands are executed in PostgreSQL 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.

SET search_path TO gradebook;

You can view the list of schemas in the database using the PostgreSQL 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

SHOW search_path;

	Warning
	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.

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.
\?: 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.

Delete Data

SQL Reference