Oracle SQL Developer and PostgreSQL

Question

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

First, if I add a third party driver on preferences, when adding a new connection there's no tab for PostgreSQL (it works fine for MySQL though). I'm using a JDBC4 version 9.1 driver, but I tried a JDBC3 of the same version and still get the same thing.

Second, there's nothing like manual configuration tab when adding a new connection. The closest is the Advanced option on Oracle tab, where I can provide a custom URL, but it fails because complains about the selected Driver (of course).

Finally, I got connected importing a connection from an XML file (contents below), but it displays only my schemas and doesn't show my tables inside them.

So, my question is: does Orable SQL Developer support PostgreSQL connections? Is there any other way to have my tables being displayed in the ObjectViewer?

<?xml version = '1.0' encoding = 'UTF-8'?>
<References xmlns="http://xmlns.oracle.com/adf/jndi">
    <Reference name="Lumea" className="oracle.jdeveloper.db.adapter.DatabaseProvider" credentialStoreKey="Lumea" xmlns="">
        <Factory className="oracle.jdeveloper.db.adapter.DatabaseProviderFactory"/>
        <RefAddresses>
            <StringRefAddr addrType="user">
                <Contents>lumea</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="subtype">
                <Contents>thirdParty</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="customUrl">
                <Contents>jdbc:postgresql://localhost:5432/versates</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="SavePassword">
                <Contents>true</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="password">
                <Contents>myencryptedpass</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="driver">
                <Contents>org.postgresql.Driver</Contents>
            </StringRefAddr>
            <StringRefAddr addrType="DeployPassword">
                <Contents>true</Contents>
            </StringRefAddr>
        </RefAddresses>
    </Reference>
</References>

score 186 · Accepted Answer · edited Aug 17 '23 at 16:12

186

Oracle SQL Developer 4.0.1.14 surely does support connections to PostgreSQL.

download JDBC driver for Postgres (http://jdbc.postgresql.org/download/)
in SQL Developer go to Tools → Preferences, Database → Third Party JDBC Drivers and add the jar file (see http://www.oracle.com/technetwork/products/migration/jdbc-migration-1923524.html for step by step example)
now just make a new Database Connection and instead of Oracle, select PostgreSQL tab

Edit:

If you have different user name and database name, one should specify in hostname: hostname/database? (do not forget ?) or hostname:port/database?.

(thanks to @kinkajou and @Kloe2378231; more details on https://stackoverflow.com/a/28671213/565525).

edited Aug 17 '23 at 16:12

Sky

25
5

answered Mar 10 '14 at 12:02

Ján Sáreník

1,517
1
16
13

1

Except that it will not work if your user name and database name are differents. It sounds like an SQLDeveloper bug and i can't find any workaroud. – Kloe2378231 Feb 20 '15 at 15:56
@user2378231In my earlier-dated comment that says the same exact thing as his, I explained the workaround too. – John O May 07 '15 at 18:52
1

It would require `localhost/database?` in hostname field. – kinkajou May 09 '15 at 03:25
7

To resume - if you have different user name and database name, one should specify in hostname: `hostname/database?` (do not forget `?`) or `hostname:port/database?` (more details on http://stackoverflow.com/a/28671213/565525) – Robert Lujo Oct 10 '15 at 18:35
1

At first i was not able to connect and i saw 'choose database' showing nothing in dropbox.So here is work around, put the database name in place of user name then Test .if its success , then you will see choose database dropbox enabled and you can see a lot of databases there .select which ever you want to , then put the actual username and test , it will be success. – Ankur Srivastava Feb 23 '17 at 21:05
wow, if code completion worked here, this could just replace practically all the custom pg admin-ish tools – chrismarx Feb 28 '17 at 18:26
After adding postgres third party jar, Connection button does not open the pop-up to connect. Nothing happens on its click. Any idea why so? – Mital Pritmani Apr 03 '18 at 13:32
Working, but almost no options or tools like the ones you have with an Oracle Database connection. – Paul Feb 06 '22 at 11:12
I had the same issue, the ? at the end worked. Thanks @RobertLujo – NullPointer Jan 23 '23 at 14:47

score 15 · Answer 2 · answered Dec 24 '13 at 04:19

15

I've just downloaded SQL Developer 4.0 for OS X (10.9), it just got out of beta. I also downloaded the latest Postgres JDBC jar. On a lark I decided to install it (same method as other third party db drivers in SQL Dev), and it accepted it. Whenever I click "new connection", there is a tab now for Postgres... and clicking it shows a panel that asks for the database connection details.

The answer to this question has changed, whether or not it is supported, it seems to work. There is a "choose database" button, that if clicked, gives you a dropdown list filled with available postgres databases. You create the connection, open it, and it lists the schemas in that database. Most postgres commands seem to work, though no psql commands (\list, etc).

Those who need a single tool to connect to multiple database engines can now use SQL Developer.

answered Dec 24 '13 at 04:19

John O

4,863
8
45
78

Is there a special syntax you need to use for any of the fields or is it simply hostname/port/username/password as is? When I try it, I get 'database x doesn't exist' where x is what I entered in the username field. – Jordan Parmer Dec 31 '13 at 22:14
@JordanParmer It is expecting a database name the same as the user, by default. If you click the test button, though, I can get it to populate the download list. Hmm. I think I am misremembering, you may also need to create the same-named db first as well, before it will populate the list. If you still have trouble, please reply and I will attempt to duplicate the method I used and add it to the answer. – John O Jan 01 '14 at 05:13
I thought the third party connector can be found on update windows of sql developer. Good thing you have Postgres JDBC links. – Charlesliam Jan 09 '14 at 08:43
I have the same problem as Jordan. My username is not the same as the database I want to connect to. Any ideas? – Neets Mar 27 '14 at 10:41
2

You need to create a database with the same name as your user. It can be left empty, doing so let's you see the other databases to connect to... then the other show up in the list and can be chosen. It's awkward, but a rather painless workaround for now. – John O May 01 '14 at 14:42
It seems that click on table doesn't open it in right panel. I can confirm only that `select query + F9` does work. – mpapec Jun 06 '14 at 12:15
1

We added the support in 4 as described above - but it's not officially supported yet, AKA an undocumented feature. Keyword, being 'yet.' As with all of the other 3rd party database supported platforms, we add them to help you migrate your databases and applications to Oracle. – thatjeffsmith Jul 29 '14 at 17:16

score 7 · Answer 3 · edited Sep 29 '11 at 17:02

7

Oracle SQL Developer doesn't support connections to PostgreSQL. Use pgAdmin to connect to PostgreSQL instead, you can get it from the following URL http://www.pgadmin.org/download/windows.php

edited Sep 29 '11 at 17:02

DrColossos

12,656
3
46
67

answered Sep 29 '11 at 06:38

francs

8,511
7
39
43

21

no longer correct. sql developer supports postgres for versions >= 4 – oligofren Apr 29 '14 at 13:21

score 3 · Answer 4 · answered Sep 30 '14 at 13:05

I managed to connect to postgres with SQL Developer. I downloaded postgres jdbc driver and saved it in a folder. I run SQL Developer -> Tools -> Preferences -> Search -> JDBC I defined postgres jdbc driver for the Database and Data Modeler (optional).

This is the trick. When creating new connection define Hostname like localhost/crm? where crm is the database name. Test the connection, works fine.

score 3 · Answer 5 · answered Oct 29 '19 at 00:00

If there is no database with the same name as the username, then clicking "Choose Database" will fail with an error like "Status : Failure -FATAL: database "your_username" does not exist"

To work around this, put 5432/database_name? in the Port field, where 5432 is the port of your Postgres instance and database_name is the name of at an existing database that your_username has access to. Then click "Choose Database" again and it should work. Now you can choose the database you want and remove the extra /database_name? from the Port field.

score 2 · Answer 6 · answered Apr 23 '20 at 04:53

I think this question needs an updated answer, because both PostgreSQL and SQLDeveloper have been updated several times since it was originally asked.

I've got a PostgreSQL instance running in Azure, with SSLMODE=Require. While I've been using DBeaverCE to access that instance and generate an ER Diagram, I've gotten really familiar with SQLDeveloper, which is now at v19.4.

The instructions about downloading the latest PostgreSQL JDBC driver and where to place it are correct. What has changed, though, is where to configure your DB access.

You'll find a file $HOME/.sqldeveloper/system19.4.0.354.1759/o.jdeveloper.db.connection.19.3.0.354.1759/connections.json:

{
  "connections": [
    {
      "name": "connection-name-goes-here",
      "type": "jdbc",
      "info": {
        "customUrl": "jdbc:postgresql://your-postgresql-host:5432/DBNAME?sslmode=require",
        "hostname": "your-postgresql-host",
        "driver": "org.postgresql.Driver",
        "subtype": "SDPostgreSQL",
        "port": "5432",
        "SavePassword": "false",
        "RaptorConnectionType": "SDPostgreSQL",
        "user": "your_admin_user",
        "sslmode": "require"
      }
    }
  ]
}

You can use this connection with both Data Modeller and the admin functionality of SQLDeveloper. Specifying all the port, dbname and sslmode in the customUrl are required because SQLDeveloper isn't including the sslmode in what it sends via JDBC, so you have to construct it by hand.

score 1 · Answer 7 · answered Jan 07 '14 at 05:23

1

I got the list of databases to populate by putting my username in the Username field (no password) and clicking "Choose Database". Doesn't work with a blank Username field, I can only connect to my user database that way.

(This was with SQL Developer 4.0.0.13, Postgres.app 9.3.0.0, and postgresql-9.3-1100.jdbc41.jar, FWIW.)

answered Jan 07 '14 at 05:23

substars

81
3

can you provide me how to install the postgresql-9.3-1100.jdbc41.jar step by step. [Postgresql Documentation](http://www.postgresql.org/docs/7.2/static/jdbc.html) is quite a shortcut to follow. I'm new to java jdbc. – Charlesliam Jan 10 '14 at 02:17
@Charlesliam I wrote a quick blog post about what I did: http://substars.github.io/2014/01/06/oracle-sql-developer-and-postgres. I'm on OSX but other platforms should be mostly the same. – substars Jan 14 '14 at 17:19
Wanted to connect to Redshift database using the same way, since it utilizes Postgres drivers. But it doesn't populate the databases for me, given a different username and password – Rakesh Singh Jul 02 '14 at 17:25

score 1 · Answer 8 · edited Jul 10 '20 at 13:56

Oracle SQL Developer 2020-02 support PostgreSQL, but it is just the basics by adding postgreSQL driver under jdbc dir and configure by adding as a 3rd party driver.

The supported functionality:

multiple databases which can be selected at connection definition
CRUD operations like query tables
scheme operations
basic modelling support: show tables without pk, fk, connections

Not supported functionalities:

no table or field completion
no indexes are shown in a tab
no constraints are shown in a tab like: fk, pk-s, unique, or others
no table or field completions in the editor
no functions, packages,triggers, views are shown

The sad thing is Oracle should only change the queries behind this view in case of PostgreSql connections. For example for indexes they need to use this query: select * from pg_catalog.pg_indexes;

score 0 · Answer 9 · answered May 29 '18 at 09:17

0

Except that it will not work if your user name and database name are differents. It sounds like an SQLDeveloper bug and i can't find any workaroud

Maybe there are some bugs in Oracle SQL Developer when it connect to the postgresql.However I connect postgresql with navicat successfully.(My postgresql username and database name are different

answered May 29 '18 at 09:17

Mike Liu

1
1

Solution:If you have different user name and database name, one should specify in hostname: hostname/database? (do not forget ?) or hostname:port/database?. – Mike Liu May 29 '18 at 09:27
1

How is "*when I use Navicat*" a solution to the problem "*it doesn't properly work with SQL Developer*"? – May 29 '18 at 10:15

panatechnica · Answer 10 · 2022-08-14T12:20:48.873

0

host= localhost/postgres? worked for me if you need a schema/database use:

localhost/postgres?currentSchema=myschema

ex: localhost/postgres?currentSchema=public

once connected, you can also use the [Choose Database] button and pull down

sql developer postgres jdbc url for the rest of us

edited Aug 14 '22 at 12:20

answered Aug 14 '22 at 12:11

panatechnica

1
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 19 '22 at 08:27

Oracle SQL Developer and PostgreSQL

10 Answers10

Linked

Related