Connecting to Amazon Redshift (or generic PostgreSQL) with Oracle SQL Developer

I guess old habits die hard; I've never used TOAD in my life but having a GUI-based SQL client is often convenient. I've used Oracle SQL Developer on and off for years (after all, it is free as in beer) and it works well enough for me.

I've recently had the issue of connecting to Amazon Redshift using SQL Developer, and after some poking around managed to do it, so I'm documenting it here.

1. Get yourself SQL Developer from Oracle's web site

2. Download the PostgreSQL JDBC drivers (the latest ones work fine)

3. Install the PostgreSQL JDBC drivers into SQL Developer as per Oracle's documentation (also see Gokhan's blog post)

The wrinkle is that SQL Developer wants the PostgreSQL database name to match the username, which is not the case for Redshift - Redshift has a separate username/password and database name.

Basically, you need to modify the Hostname field in SQL Developer. Instead of putting in just the hostname of your Redshift cluster, you need to put in hostname:port/databasename?

Note that there is a Port field but this gets ignored if you suffix databasename? to the hostname.

Everything should work as expected after that.

Enjoy columnar database goodness!