This section is currently a thinly disguised copy of the Tutorial. Needs to be augmented. - thomas 1998-01-12 Although the site administrator is responsible for overall management of the PostgreSQL installation, some databases within the installation may be managed by another person, designated the database administrator. This assignment of responsibilities occurs when a database is created. A user may be assigned explicit privileges to create databases and/or to create new users. A user assigned both privileges can perform most administrative tasks within PostgreSQL, but will not by default have the same operating system privileges as the site administrator. The Administrator's Guide covers these topics in more detail. Databases are created by the CREATE DATABASE command issued from within PostgreSQL. createdb is a shell script provided to give the same functionality from the Unix command line. The PostgreSQL backend must be running for either method to succeed, and the user issuing the command must be the PostgreSQL superuser or have been assigned database creation privileges by the superuser. To create a new database named mydb from the command line, type % createdb mydb and to do the same from within psql type => CREATE DATABASE mydb; If you do not have the privileges required to create a database, you will see the following: ERROR: CREATE DATABASE: Permission denied. You automatically become the database administrator of the database you just created. Database names must have an alphabetic first character and are limited to 31 characters in length. PostgreSQL allows you to create any number of databases at a given site. The Administrator's Guide discusses database creation in more detail, including advanced options of the CREATE DATABASE command. Once you have constructed a database, you can access it by: Running the PostgreSQL interactive terminal program, called psql, which allows you to interactively enter, edit, and execute SQL commands. Using an existing graphical frontend tool like PgAccess or ApplixWare (via ODBC) to create and manipulate a database. These possibilities are not covered in this tutorial. Writing a custom application, using one of the several available language bindings. These possibilities are discussed further in The PostgreSQL Programmer's Guide. You probably want to start up psql, to try out the examples in this manual. It can be activated for the mydb database by typing the command: % psql mydb You will be greeted with the following message: Welcome to psql, 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 mydb=> This prompt indicates that psql is listening to you and that you can type SQL queries into a work space maintained by the terminal monitor. The psql program itself responds to special commands that begin with the backslash character, \. For example, you can get help on the syntax of various PostgreSQL SQL commands by typing: mydb=> \h Once you have finished entering your queries into the work space, you can pass the contents of the work space to the PostgreSQL server by typing: mydb=> \g This tells the server to process the query. If you terminate your query with a semicolon, the \g is not necessary. psql will automatically process semicolon terminated queries. To read queries from a file, say myFile, instead of entering them interactively, type: mydb=> \i myFile To get out of psql and return to Unix, type mydb=> \q and psql will quit and return you to your command shell. (For more escape codes, type \? at the psql prompt.) White space (i.e., spaces, tabs and newlines) may be used freely in SQL queries. Single-line comments are denoted by --. Everything after the dashes up to the end of the line is ignored. Multiple-line comments, and comments within a line, are denoted by /* ... */. If you are the owner of the database mydb, you can destroy it using the SQL command => DROP DATABASE mydb; or the Unix shell script % dropdb mydb This action physically removes all of the Unix files associated with the database and cannot be undone, so this should only be done with a great deal of forethought.