Cross-database references are not implemented

Products

• VMware Tanzu Greenplum

Issue/Introduction

This articles pertains to all versions of Pivotal Greenplum Database (GPDB).

This article explains what to do when cross-database references are not Implemented.

Symptoms:

Any database query against a table name with two "." inside the name, will produce the following error message:

test=#select count(*) from temp.temp.test; 
ERROR: cross-database references are not implemented: "temp.temp.test"

Cause

A table with two "." inside the name will be interpreted as a database.schema.table. 

 

Resolution

Rename the table to only have one "." inside the name. 

Note: If there must be two "." in the table name, the following commands must be executed to create the table and select the table. 

gpadmin=# create table "public.test" (a int, b int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
gpadmin=# select * from "public.test";
 a | b
---+---
(0 rows)

gpadmin=# select * from public."public.test";
 a | b
---+---