SQL Query to List All Amazon Redshift Databases on a Cluster

AWS data warehouse developers using Amazon Redshift cluster can execute SQL query to list all Redshift databases created on a particular cluster using the SQL code given in this tutorial. Recently I need the list of all Amazon Redshift databases for documentation on a wiki page. So I build SQL query to display the Redshift databases on each cluster. In this guide I want to share the SQL script with other Redshift developers.

Since while connecting to Amazon Redshift database cluster, in connection string developers provide the database name for the connection endpoint, most database clients do not show databases other than the current connected database. This can be a problem when as database developer you want to see all other databases existing on that current Redshift cluster.
Although some SQL editors as client tools have tricks to display all Amazon Redshift databases, using the SQL query I shared its codes in this tutorial, you can get the databases list easily.

Before starting, I want to remind database programmers that Amazon Redshift is based on PostgreSQL. Redshift is converted into a MPP OLAP data warehouse platform by Amazon Web Services (AWS) from an OLTP database.

So SQL developers can query system tables and system views in pg_catalog schema which exist in each database. pg_catalog schema is inherited from PostgreSQL database actually.
Here is a sample SQL query that database developers can use to display the list of all databases created on Amazon Redshift cluster.

SQL programmers working on AWS cloud platform will see that I use pg_database_info view and pg_user system view from pg_catalog schema in this SQL query to fetch Amazon Redshift databases list created on that current Redshift cluster


select

 d.datid as database_id,

 d.datname as database_name,

 u.usename as createdby

from pg_catalog.pg_database_info as d

inner join pg_catalog.pg_user u

 on u.usesysid = d.datdba

order by d.datid;

SQL query

Here is the execution result of the above SQL query on SQL Workbench/J which lists all databases created on a sample Amazon Redshift database cluster.

SQL query to list all databases on Amazon Redshift cluster

Following databases: dev, padb_harvest, template0, and template1 are system databases.
Other databases are user databases created by Amazon Redshift administrators and database developers with CREATE DATABASE permission on that specific Amazon Redshift cluster. You will see different customer databases on your Redshift cluster. But the system databases exist on all Amazon Redshift clusters.