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.
Table of Contents
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.
After restarting SQL Developer you have PostgreSQL available in the database type dropdown for a 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.
After saving and connect you are ready to use your PostgreSQL DB in SQL Developer.
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?.
That’s it. Have fun using SQL Developer with your PostgreSQL DB 🙂