If you have used Oracle SQL Developer, you might have struggled to connect to databases other than Oracle. This is especially true for Oracle SQL Developer users that want to connect to Amazon Redshift. There is a lot of piecemeal documentation out there, some of which are outdated or inaccurate.
We thought a cohesive step-by-step guide on how to connect Oracle SQL Developer to Redshift was needed.
If you tried to use JDBC drivers provided by Amazon Redshift you will quickly find that Oracle SQL Developer doesn’t support those. You must use PostgreSQL JDBC drivers instead.
Also, if Redshift forces SSL connections, this can be a roadblock. The connection parameters need to be explicitly set for SSL. Without setting SSL the connection will fail.
Our guide will walk you through the process of setting up a PostgreSQL JDBC driver and an SSL connection.
How to connect Amazon Redshift to Oracle SQL Developer with Postgres JDBC driver
Step 1: Download PostgreSQL JDBC driver
Download the PostgreSQL JDBC driver from this URL: https://jdbc.postgresql.org/download.html.
Find the most recent version of the driver and click the link to download it. After that save the resulting .jar file on your computer to a location where you can easily find it.
Grab the drivers directly from Postgres
Step 2: Establish a connection from Oracle SQL Developer UI
In Oracle SQL Developer, click ‘Tools’ then ‘Preferences’ from the top menu bar:
Launch Oracle SQL Developer Tools
2. Under ‘Database’ select ‘Third Party JDBC Drivers’
Add the drivers
3. Click ‘Add Entry’, and in the ‘Select Path Entry Window’, click on the .jar file you saved in Step 1 and click ‘Select’
Select the drivers
4. Click ‘OK’ in the Preferences window
5. Now when you click ‘New Connection…’ you will see a PostgreSQL tab
Setup the connection
6. Enter a ‘Connection Name’ and the ‘Username’ and ‘Password’.
7. In the ‘Hostname’ box replace
with your connection string containing the SSL parameters:
What if you are not using SSL? Simply remove those SSL parameters:
8. You can delete what is in the ‘Port Box’ and leave it blank. The post is part of the connection string. Also, leave ‘Choose Database’ as is.
9. Click ‘Save’ and then click ‘Connect’. If everything went according to plan, you should be connected to Redshift! However, don’t celebrate just yet. Time to test.
Step 3: Test
Once you have connected run this quick test to make sure queries run properly. It should list all the tables for a given database and schema:
SELECT DISTINCT tablename FROM pg_table_def WHERE schemaname = ‘replacewithschemaname’ ORDER BY tablename;
Run the query. If you see a list of tables, awesome! If you don’t, check your permissions or run a different test query based on your access/permissions.