1

Using the answer from the Oracle SQL Developer and PostgreSQL question, I have connected Oracle SQL Developer (4.1.3.20) to my PostgreSQL (9.1.20) database.

But only a weird subset of the objects in the database appears in the SQL Developer "Connections" browser. Specifically:

  1. The public schema (which is the default PG user data schema) doesn't show up. But if I create another schema (e.g. one named schema1 in the screen shots below), the schema does show up.
  2. Within a visible schema (schema1), tables, views, their columns, and their data are visible. So are triggers. There are headings for indexes and functions, but the functions, trigger functions, and indexes that exist are not visible. There aren't even headings for sequences, domains, catalogs, and extensions. Refreshing the display of the schema evokes 'ERROR: syntax error at or near ""INDEX_NAME"" Position: 99'.

Here it is in screen shots:

This is the database as shown in the PgAdminIII object browser

This is the database as shown in the Oracle object browser - also showing the above-mentioned error message

Another weirdness: I can open a SQL window in the Oracle tool and execute both DML (select, insert, etc) and DDL (create table, create function, etc), even in the (invisible) public schema. I can even query the PG catalogs (information_schema and pg_catalog).

So the question is: How can I make the missing objects (most importantly, the public schema) visible in SQL Developer?

~ Thanks, Ken

Improve this question
6
  • Are you really wedded to SQL Developer? While it might be possible to connect to PostgreSQL, it certainly isn't designed with PG in mind. I seriously doubt it will ever really work well. pgAdmin is far more common. Commented Mar 5, 2016 at 5:39
  • Not wedded; not even engaged; just dating ;-) Commented Mar 5, 2016 at 20:19
  • Sorry, sent off the previous comment prematurely. To continue: pgAdmin is a great DB management tool (I love it and have used it for years), but what I also need is a data model diagrammer, and one that is integrated / synchronized with the PG databases that implement the diagrams. I have tried the most promising of those listed at wiki.postgresql.org/wiki/GUI_Database_Design_Tools and other places, and they all have fallen short in one way or another. Would welcome suggestions. Maybe this should be in a new thread? Or is there such a thread already? Commented Mar 5, 2016 at 20:34
  • Then I strongly recommend just finding a different tool. It doesn't have to be pgAdmin. Oracle's entire system is extremely quirky and unintuitive (from the nonstandard system views to the lack of OFFSET/LIMIT or similar to DBMS_OUTPUT to DUAL to the conflation of users and schemas). It really hasn't changed that much from the 90s... SQL Developer was designed for Oracle, and the differences between it and other databases is so vast that I doubt any tool designed for Oracle first will function well with another DB. Commented Mar 5, 2016 at 20:38
  • You might try Software Recommendations. See this before you post anything there, though. They have very strict standards (by necessity of the nature of the site's target subject). Commented Mar 5, 2016 at 20:44

1 Answer 1

Reset to default
0

With SQLdeveloper 4.2, public schema is back ;o)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.