如何通过PostgreSQL连接器在Azure Data Studio中连接Amazon Redshift

I’ve helped peers work through this exact scenario, so let’s break down what’s going on and how you can get this working more smoothly.

First, Yes—People Have Made This Work #

While it’s not an officially supported setup, many users have gotten Azure Data Studio (ADS) talking to Redshift with some tweaks to the Postgres extension and query behavior. Let’s tackle your specific issues one by one.

1. Fixing the "Cannot Expand Node" Tree View Error #

The root problem here is that ADS’s Postgres extension relies on Postgres-native system tables to populate the object explorer. Redshift uses a modified Postgres fork with different system table structures, so the extension can’t parse Redshift’s metadata correctly.

Workarounds:

• Option 1: Try the modified Redshift extension you found
  Even though it’s a clone of the Postgres extension and hasn’t been updated in a while, many users report it fixes the tree view issue by adjusting system table queries to match Redshift’s schema. You can install the VSIX file manually (download from the repo, then in ADS go to Extensions > ... > Install from VSIX). It’s not perfect, but it often works for basic object browsing.
• Option 2: Skip the tree view and use direct metadata queries
  If you don’t want to rely on an unmaintained extension, you can query Redshift’s system tables directly to get schema info. For example:

  SELECT tablename, schemaname FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
  

2. Resolving the "cursors can only be used within the transaction that created them" Query Error #

Two issues are causing this:

• Incorrect query syntax: Redshift doesn’t use the SQL Server-style [server].[database].[table] naming convention. Instead, it uses database.schema.table (and if you’ve already specified the database in your connection, you just need schema.table or just table if it’s in the default public schema).
• ADS Postgres extension’s cursor behavior: By default, the extension uses cursors to stream query results, which Redshift handles differently than vanilla Postgres.

Fixes:

• Adjust your query syntax: Replace your query with Redshift-compatible syntax, e.g.:

  SELECT * FROM your_schema.your_table;
  

• Disable cursor usage in the Postgres extension:
  1. Open ADS Settings (File > Preferences > Settings)
  2. Search for Postgres: Use Cursors for Results
  3. Uncheck the box to disable this feature. This will make the extension execute queries without using cursors, which should resolve the error.

3. Connection Configuration Tips #

Make sure your connection settings are correct for Redshift:

• Port: Use 5439 (Redshift’s default port, not Postgres’s 5432)
• Authentication: If you’re using IAM credentials, you’ll need to generate temporary credentials via the AWS CLI (aws redshift get-cluster-credentials) and use those to connect—vanilla Postgres drivers don’t support direct IAM auth.
• Database: Redshift clusters have multiple databases; make sure you specify the exact database you want to connect to in the connection settings.

Final Notes #

The modified Redshift extension is hit-or-miss depending on your ADS version, but it’s worth trying if you need the tree view. If stability is a priority, sticking with the official Postgres extension (plus disabling cursors and using correct Redshift syntax) is a more reliable approach.

内容的提问来源于stack exchange，提问作者EILNP