Join table from different schema

Putting this here as I’m not sure if it’s possible to do this currently.

The title says it all, basically. Let’s say we have two tables from two distinct schemas, is it possible to join the two tables together?

-- table "users" belongs to schema "static"
-- table "events" belongs to schema "analytics"
-- editing a query with the schema "analytics" open

SELECT
  u.id,
  e.timestamp,
  e.label
FROM events e
LEFT JOIN static.users u ON u.id = e.user_id

Using a PostgreSQL database with Redash.

When querying Postgres directly (e.g. via a terminal or a client), it is perfectly fine to join a table from a different schema, however any way I’ve tried to do it from Redash didn’t bring any successful result.

What error do you get?

Error running query: relation "static.users" does not exist LINE 6: LEFT JOIN static.users u ON u.id = e.user_id ^

I am absolutely positive that the table users exists under the schema static.

Same functions works on my self-hosted instance with one or several joins, between tables from different schemas.

Are you sure for example the postgres role used by redash to connect to your database have usage/select rights on this schema and this table ?

Interesting… Let’s have a closer look at the worlflow:

1. create a new Query
2. select “static” schema from the dropdown on the left panel
3. query: select id from users limit 10; -> no issue
4. query: select id from static.users limit 10; -> relation static.users does not exist

It looks like the format SELECT FROM [schema].[table] is not recognized.

Hello.
Your second test make me think that the users table is in the public schema. Maybe I am wrong if you set up before the search_path.

When I select the “static” schema, the “users” table appears in the left panel. However when I select the “analytics” schema, then “users” disappear and “events” appear.

Basically the left panel (in the Query window) behaves according to the expected schema/table hierarchy, it’s only when running actual queries that these problems happen.

I understand.
I just create the same database structure and for me it works fine (redash 8 & postgresql 10.5) so I don’t know how to help.

Capture d’écran_2020-04-04_12-44-551366×768 50.4 KB

Well thanks for the information. Not really sure what happens.

Using Redash 7.0 & PostgreSQL 9.6.11.