Connect to Postgresql 9.2 from Eclipse Data Tools Platform

Question

GOAL: My goal is to write sql code within Eclipse to query a Postgresql database. The reason I want to write my queries within Eclipse is to then write R or Python code to perform statistical analysis on my data.

SOFTWARE DETAILS:

• I am working on Windows XP.
• My Postgresql database is installed locally on my hard drive. I am using Postgresql 9.2.2
• Eclipse Juno enterprise edition with Data Tools Platform. I downloaded this on 19 December 2012 so it is all the latest software.
• jdk 1.7
• jre 1.5
• jre 7
• postgresql-9.1.901.jdbc4.jar

METHODOLOGY: To start within Eclipse I choose "File", "New", "Other". Under the "Connection Profile" I expand and choose "Connection Profile". A New Connection Profile dialogue wizard comes up and from the list of profile types I choose PostgreSQL. I name it PostgreSQL922 and click the Next button.

The "New JDBC Connection Profile" dialogue comes up. Under "Drivers" pull down menu I choose what I created previously "PostgreSQL922". I then click the icon for New Driver Definition.

The dialogue "New Driver Definition" has three tabs. Under the first tab "Name/Type" there is only one option and that is the PostgreSQL JDBC Driver. However I do find it confusing that the system version of this option is 8.x. I am not sure why that option is pre-set. Under the tab "Jar List" I click the "Remove Jar/zip" button to remove the "Postgresql-8.1-404.jdbc2.jar" driver file. I then click the "Add Jar" button and navigate to my new driver: "Postgresql-9.1-901.jdbc4.jar". When I choose this jar file, the "OK" button in my dialogue box is no longer grayed out. But rather it is now clickable. Under the tab "Properties" There are 5 different values:

• Connection URL: jdbc:postgresql://localhost:5432/database
• Database Name: postgres
• Driver Class: org.postgresql.Driver
• Password: nikolasdad
• User ID: postgres

I then click the OK button to accept all my values. This brings me back to "New JDBC Connection Profile". Here I click the "Test Connection" button and an error message box pops up that states "Ping Failed!" Under the "Details" button the message is shown:

org.postgresql.util.PSQLException: FATAL: database "database" does not exist
    at 
org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:471)
    at 
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
    at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:32)
    at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:393)
    at org.postgresql.Driver.connect(Driver.java:267)
    at 
org.eclipse.datatools.enablement.internal.postgresql.PostgreSQLJDBCConnection.createConnection(Post
greSQLJDBCConnection.java:87)
    at 
org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBa
se.java:105)
    at 
org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
    at 
org.eclipse.datatools.enablement.internal.postgresql.PostgreSQLJDBCConnection.<init>(PostgreSQLJDBC
Connection.java:47)
    at 
org.eclipse.datatools.enablement.internal.postgresql.PostgreSQLConnectionFactory.createConnection(P
ostgreSQLConnectionFactory.java:51)
    at 
org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFa
ctoryProvider.java:83)
    at 
org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.ja
va:359)
    at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
    at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:53)

I used this same methodology to connect to a SQL Server 2005 database and I was able to write queries against it. I did this correctly once before, but with PostgreSQL I am failing. How can I fix this? Am I using the wrong version of PostgreSQL? Does Eclipse Juno not interface with Postgresql 9.2.2?

Answer 1

The (initial) database name has been taken from the JDBC URL: "jdbc:postgresql://localhost:5432/database"

Try taking off the "database" suffix from that URL

Answer 2

Thank you swasheck and araqnid. That is the answer.

I returned to Eclipse and deleted my old connection. I then created a new one, using the "New JDBC Connection Profile". Under the Genera properties tab I chose the following values:

Database: Postgres
URL: jdbc:postgresql://localhost:5432/postgres*
User Name: postgres
Password: ***************

_{* Please note, here it used to be the generic term database, just as swasheck mentioned. I swapped 'postgres' where it used to be 'database'.}

I then clicked the "test connection" button and it connected flawlessly. Thank you very much for your astute observation.