Cirro uses four-part object names (db.catalog.schema.object) to facilitate federated queries and other tasks. Create a Database Alias if your SQL Client can only address two-part names.

Issue

Four-part SQL query fails, for example:

SELECT * FROM postgresRDS.postgres.public.employees;

Solution

There are two ways to solve the issue:

First, you can run the query in Cirro’s Federated Query Builder which can handle 4 part object names.

Second, Create a Remote Database to remap databases and create an alias.

  • Query the database alias.

Examples

For example table: postgresRDS.postgres.public.employees

Create the remote database with name MyPostgres.

CREATE REMOTE DATABASE MyPostgres ON postgresRDS.postgres;

Check the Remote Database exists.

SELECT * FROM SYS.SYSREMOTEDATABASES

Example result:

SYSTEMID DATABASEID DATABASENAME NATIVEDATABASENAME DATABASECREATOR CREATED LASTMODIFIED
13 3 MyPostgres postgres CirroUser 2017-10-18 06:08:00:085 2017-10-18 06:08:00:085

Query your database.

SELECT * FROM MyPostgres.public.employees

Query for ODBC Driver set with tableNamePattern=true.

SELECT * FROM MyPostgres-public-employees

See Also