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    lt  xml version    1 0  encoding    UTF-8   gt   lt References xmlns  http   xmlns oracle com adf jndi  gt       lt Reference name  Lumea  className  oracle jdeveloper db adapter DatabaseProvider  credentialStoreKey  Lumea  xmlns    gt           lt Factory className  oracle jdeveloper db adapter DatabaseProviderFactory   gt           lt RefAddresses gt               lt StringRefAddr addrType  user  gt                   lt Contents gt lumea lt  Contents gt               lt  StringRefAddr gt               lt StringRefAddr addrType  subtype  gt                   lt Contents gt thirdParty lt  Contents gt               lt  StringRefAddr gt               lt StringRefAddr addrType  customUrl  gt                   lt Contents gt jdbc postgresql   localhost 5432 versates lt  Contents gt               lt  StringRefAddr gt               lt StringRefAddr addrType  SavePassword  gt                   lt Contents gt true lt  Contents gt               lt  StringRefAddr gt               lt StringRefAddr addrType  password  gt                   lt Contents gt myencryptedpass lt  Contents gt               lt  StringRefAddr gt               lt StringRefAddr addrType  driver  gt                   lt Contents gt org postgresql Driver lt  Contents gt               lt  StringRefAddr gt               lt StringRefAddr addrType  DeployPassword  gt                   lt Contents gt true lt  Contents gt               lt  StringRefAddr gt           lt  RefAddresses gt       lt  Reference gt   lt  References gt

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Oracle SQL Developer 4 0 1 14 surely does support connections to PostgreSQL    download JDBC driver for Postgres  http   jdbc postgresql org download html  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

User · Answer

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

User · Answer

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

User · Answer

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

[postgresql] Oracle SQL Developer and PostgreSQL

Examples related to postgresql

Examples related to oracle-sqldeveloper