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

line separator

Downloading and Installing PostgreSQL

PostgreSQL can be downloaded from the postgresql.org download page. Note that PostgreSQL comes preinstalled on most linux distributions, although the version may not be the latest PostgreSQL version; to get permissions to that installation you may need to use the postgres user login. If you download your own copy, you do not need to worry about the postgres user login, but you will have to carry out several setup operations, detailed below. We also recommend you install the pgAdmin4 user interface. On Windows, the PostgreSQL installer also install pgAdmin.

Setting up PostgreSQL

After installing PostgreSQL, you need to configure the server and create a database. 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 securely.

Connecting to and Executing Queries on PostgreSQL

Once PostgreSQL is set up, you can connect to it using a tool such as pgAdmin4 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 any of a variety of IDEs such as Eclipse, Idea4J, Netbeans, etc. You will have to use the host as localhost, port number you created, database name and username and password.

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.