Using SQL Developer with PostgreSQL

Quick guide on how to connect to a PostgreSQL database using Oracle SQL Developer.

Also it’s primary usage is for Oracle DB, SQL Developer also a good tool for managing other databases like PostgreSQL and you can benefit from known UX/UI. I assume you already have a local installation of SQL Developer and a PostgreSQL up & running at localhost. If you don’t have a local PostgreSQL DB, have look on this article how to achieve this in minutes using Docker.

Preparing SQL Developer to connect to PostgreSQL

To enable SQL Developer to connect to a Postgres DB, get the offical PostgreSQL JDBC driver first. Save the downloaded jar file in an appropriate folder like /opt/postgres and set the permissions so that SQLDeveloper is able to read the file.

In SQL Developer navigate to Tools --> Preferences and there to Database --> Third Party JDBC Drivers. Click on Add Entry and search for the Postgres JDBC jar.

sqldeveloper-postgres-driver

After restarting SQL Developer you have PostgreSQL available in the database type dropdown for a new connection.

sqldeveloper-postgres-new-connection

Create a connection to the PostgreSQL database

Having that, create a new PostgreSQL connection to localhost port 5432 with user postgres and the default password postgres. If you have started PostgreSQL using Docker, provide the password set in the POSTGRES_PASSWORD variable of the Docker run command.

sqldeveloper-postgres-new-connection-2

After saving and connect you are ready to use your PostgreSQL DB in SQL Developer.

sqldeveloper-postgres-connected

Connecting when username does not equal the database name

By default, the connection in SQL Developer is made to a PostgreSQL database named exactly like the user.

Supposing you have a user testuser (without having an own database named the same) and want to connect to a database called testdb. After entering the credentials for that user neither the Choose Database dropdown was populated nor did the button itself worked for me.

The trick – found in this article on StackOverflow – when connecting to a database with a different name is to add the databse name after a slash to the hostname and finally add a question mark at the end, like so: localhost/testdb?.

sqldeveloper-postgres-different-dbname

That’s it. Have fun using SQL Developer with your PostgreSQL DB 🙂

Useful links