------- book cover ------- Database System Concepts
Sixth Edition
Avi Silberschatz
Henry F. Korth
S. Sudarshan

line separator

Setting up PostgreSQL

The PostgreSQL wiki has a nice page on how to set up PostgreSQL with sections covering each of the popular operating systems: http://wiki.postgresql.org/wiki/Detailed_installation_guides. On unix/linux systems, a user called postgres is created as part of PostgreSQL setup. We also recommend you install the pgAdmin3 user interface. After installing postgreSQL, you need to configure it. A tutorial for configuring it on Debian/Ubuntu can be found here: http://www.debianhelp.co.uk/postgresql.htm. By default, PostgreSQL allows access from IDEs on the same computer. If you want to access it from another machine, you have to configure it to accept remote connections. It it best to allow access only from a selected set of IP addresses, or range of IP addresses, to provide better security. You will also need to create users on the PostgreSQL database in order to use it.

Connecting to and Executing Queries on PostgreSQL

Once PostgreSQL is set up, you can connect to it using a tool such as pgAdmin3 to administer the system, browse databases, and execute queries. You can also browse the database and execute queries using the command line tool psql, or the Netbeans IDE.

SQL Tips on PostgreSQL

PostgreSQL provides good support for basic SQL syntax. Most queries in the book run as-is on current versions of PostgreSQL. PostgreSQL also has support for some advanced features such as recursive WITH queries, window queries, and some non-standard syntax such as the limit and offset clauses to fetch a specified number of rows from a specified starting point.