Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Issue connecting to PostgreSQL with Oracle SQL Developer

user2277610Feb 20 2015 — edited Feb 23 2015

I'm trying to connect to a PostgreSQL 9 database using Oracle SQL Developer 4.1.0.17_29, but I'm not having any success so far.

I added my Third Party JDBC drivers for PostgreSQL, the tab shows up.

However, after I fill in the Username/Pwd and Hostname/Port and then select Choose Database - I get:

Failure -FATAL: database "user1" does not exist

After some researches, it seems that SQLDevelopper is not able to connect a Postgre DB with a different "user name" and "database name". It works only of both are same.

Is it a bug or is it a known limitation ?

The JDBC driver I am using is postgresql-9.4-1200.jdbc41.jar (also tried with postgresql-9.3-1103.jdbc3.jar).

Thanks for your help,

Elise.

Comments

jmarton

In the connection definition, fill in username, password. On the PostgreSQL tab, enter hostname in the form: 127.0.0.1/testdb? (ending with the question mark). This way I managed to query the databases list and connect to the testdb database with username testuser.

Credits goes to Panagiotis Piperopoulos answered Sep 30 '14 at 13:05 at Oracle SQL Developer and PostgreSQL - Stack Overflow

Software versions used:

SQL Developer version: 4.1.0.17.29

Oracle JDK: 1.8.0_31, 64bit, Linux

Instant client: x64-12.1.0.2.0 (inactive per SQL Developer configuration)

PostgreSQL JDBC driver: postgresql-9.4-1200.jdbc4.jar

2 people found this helpful
user2277610

Thanks for the workarround.

I finally managed to connect to my DB by this way:

username: testuser

password: mypass

Host: 127.0.0.1:1234/testdb?

Port: 1234

Then in select Database, i can select testdb again and connection works.

This really sounds like a bug.

However, i am not able to list existing DB schema or table. Is it normal ?

1 person found this helpful
jmarton

Using 4.1 EA1, I can list the schemas and tables, as the screenshot taken from the Connections pane shows (see below). It might be some privilege-related issue. The user and db I was experimenting with was created like:

createuser -U postgres -d -A -P -R testuser

createdb -U testuser -E unicode -h 127.0.0.1 testdb

sqldev4.1ea1-postgresql-schemas.png

@"Jeff Smith Sqldev Pm-Oracle": would it be possible that the Connection definition/PostgreSQL tab (and possibly any JDBC-tabs except Oracle DB) would allow for a custom JDBC url with properties instead of the current Hostname/Port/choose DB.

The following figure shows a possible draft. This way, java.sql.DriverManager.getConnection(String url, Properties info) would be called, where the info would be populated with the username and password, and the custom properties given in the Properties form.

sqldev-custom-jdbc-connection-form.png

1 person found this helpful
user2277610

Exactly what we need.

Netbeans DB connexions is implemented as this. Very usefull to customize database URL if required.

About listing the schemas and tables, i cannot find what happens. There is no error message.

I also use the latest SQLDevelopper 4.1 available and same JDK/JDBC driver than you.

In testdb, my user has rights on a specific schema only (other are not accessible), but i can request this without limitation: select * from pg_catalog.pg_tables;

Do you know how SQLDevelopper lists tables?

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 23 2015
Added on Feb 20 2015
#sql-developer, #sqldeveloper
4 comments
11,065 views