Local PostgreSQL documentation

Getting Started
Java JDBC Access
Windows Access (ODBC)
PHP Access

Getting Started

In order to get a database setup on the PostgreSQL server:

  1. Go here and enter your username.
  2. You will be mailed an initial password.
  3. Under Linux do "setup Postgresql" and add this command to your ~/.login so that you don't have to do it every time you log in.
  4. Now do "psql -h <database server>" (without the <> brackets of course) and enter your initial password at the prompt. The database server will normally be "dbteach" unless you are told otherwise.
  5. You should now have a "=>" prompt at which you can issue SQL commands e.g. "CREATE TABLE foobar etc..".
  6. You should change your password first with the command "ALTER USER <your userid> WITH PASSWORD '<new password>';" without the <> brackets of course.
    DO NOT set your PostgreSQL password to be the same as your unix password as PostgreSQL passwords are not particularly secure.
  7. Hit CTRL-D to exit

By default you will be connected to the database with the same name as your username unless you give the "-d" option e.g. "psql -h dbteach -d shared-database".

Commands you can issue at the => prompt can be found in the Reference Manual for PostgreSQL.

Java JDBC Access

On Unix make sure you have "setup Postgresql" in your ~/.login so that the postgresql JDBC driver is added to your classpath.
On Windows you need to double-click on "J:\setup\java\Postgresql.bat"; you only need to do this once for it to be permanently added to your classpath.

The argument for loading the postgresql JDBC driver with Class.forname(String Driver) is: 

"org.postgresql.Driver"

and the URL you give to the Driver.getConnection(String url, String user, String password) method is: 

"jdbc:postgresql://dbteach/<database-name>"

Look at the JDBC tutorial.

Windows Access

Access to Postgresql from Windows - unless you are using Java - is done via ODBC. To set up an ODBC data source:

  1. Go to "Start->Settings->Control Panel->Administrative Tools" and double-click on "Data Sources (ODBC)".
  2. In the "User DSN" tab select "Add".
  3. Select "PostgreSQL" from the list of drivers and hit "Finish".
  4. In the "PostgreSQL driver Setup" dialog enter a unique name for the data source (this is the DSN name you will use when connecting from Windows apps such as Access); enter the name of the database you want to connect to, the name of the postgresql server, and your postgresql username and password as below (leave the port as 5432):

  5. Hit "OK" and you should see your data source listed in the "User DSN" tab of the "ODBC Data Source Administrator".

To access Postgresql from MS-Access

  1. Go to "File->Get External Data".
  2. Select either "Import" (just imports the data) or "Link" (synchronises the MS-Access tables with tables on server).
  3. At the bottom of the "Import" dialog set "Files of type" to "ODBC Databases()".
  4. In the "Select Data Source" dialog, select the "Machine Data Source" tab and select your ODBC data source from the list that you set up in step 4 above or create a new data source by hitting "New" then select "User Data Source" and then proceed as from step 3 above.
  5. Hit "OK" then from the Link/Import dialog select the tables you want to Link/Import into MS-Access.

To access Postgresql from the .NET platform

You need to use the ODBC .NET Data Provider add-on for the MS .NET platform (provides Microsoft.Data.Odbc classes), below is a C#.NET code sample:

Using System.Data;
Using Microsoft.Data.Odbc;
:
OdbcConnection myconnection = new OdbcConnection("DSN=<dsn name of your data source>;");
OdbcCommand mycommand = new OdbcCommand("<SQL here>", myconnection);
OdbcDataAdapter myadapter = new OdbcDataAdapter(mycommand);
DataSet mydataset = new DataSet();
myadapter.Fill(mydataset, "tablename");

PHP access

You use the pg_connect command thus: 

$connection = pg_connect("host=dbteach port=5432 dbname=<database-name> user=<user name> password=<password>");

See the PostgreSQL functions section of the PHP manual.