Local PostgreSQL documentationGetting Started
Java JDBC Access
Windows Access (ODBC)
In order to get a database setup on the PostgreSQL server:
- Go here and enter your username.
- You will be mailed an initial password.
- 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 "dbteach2" unless you are told otherwise.
- You should now have a "=>" prompt at which you can issue SQL commands e.g. "CREATE TABLE foobar etc..".
- 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.
- 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 dbteach2 -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 the School's managed Windows computers the jdbc driver should be installed under C:\Program Files\Java\jdbc
The argument for loading the postgresql JDBC driver with Class.forname(String Driver) is:
and the URL you give to the Driver.getConnection(String url, String user, String password) method is:
Look at the JDBC tutorial.
Access to Postgresql from Windows - unless you are using Java - is done via ODBC. To set up an ODBC data source:
- Note: For 64 bit Windows 7 machines: use C:\Windows\syswow64\odbcad32.exe to get to the Postgres drivers rather than step 2
- Go to "Start->Settings->Control Panel->Administrative Tools" and double-click on "Data Sources (ODBC)".
- In the "User DSN" tab select "Add".
- Select "PostgreSQL" from the list of drivers and hit "Finish".
- 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):
- 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
- Go to "File->Get External Data".
- Select either "Import" (just imports the data) or "Link" (synchronises the MS-Access tables with tables on server).
- At the bottom of the "Import" dialog set "Files of type" to "ODBC Databases()".
- 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.
- 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");
You use the pg_connect command thus:
$connection = pg_connect("host=dbteach2 port=5432 dbname=<database-name> user=<user name> password=<password>");