Cross-Database Queries

You can write queries that select from tables in different databases on the same appliance. You can run cross-database queries from any database that belongs to the same appliance as the tables listed in the FROM clause. The database you are connected to does not have to contain any of the listed tables from which these queries read data.

The FROM clause must reference the tables unambiguously, typically using a combination of database name, schema name, and table name. (The database and schema names may be omitted from the local tables, but fully qualified table references are recommended.) For example:

premdb.public.awayteam
yellowbrick.public.match
test.schema1.table1
devel.schema1.table1

SQL Commands That Support Cross-Database Reads

Several SQL commands that read from remote databases are supported. However, writing data to remote databases is not supported. The following SQL commands support cross-database reads:

CREATE TABLE AS (CTAS): A locally created table may read some or all of its data from remote databases. You cannot create the table in a remote database.
CREATE VIEW: A locally created view may read some or all of its data from remote databases. You cannot create the view in a remote database.
DECLARE: You can declare cursors that reference remote tables. You can also fetch from, move, and close these cursors.
DELETE and TRUNCATE: You can delete rows from a local table based on a query against one or more remote tables. You cannot delete (or truncate) rows from a remote table.
EXPLAIN: You can explain queries that select from local and remote tables.
INSERT: You can insert rows into a local table based on a query that selects from one or more remote tables. You cannot insert rows into a remote table.
PREPARE and EXECUTE: You can prepare and execute queries that reference remote tables.
SELECT, SELECT INTO, TABLE: You can select from any combination of tables and views in local and remote databases.
UPDATE: You can update rows in a local table based on a query that selects from one or more remote tables. You cannot update rows in a remote table.

Restrictions

SQL statements that write to a table (INSERT, UPDATE, DELETE, TRUNCATE) must be executed from the database where the table resides. For example, the following INSERT fails because it attempts a cross-database write:

yellowbrick=# insert into premdb.public.match select * from yellowbrick.public.match;
ERROR:  cross-database modifications are not allowed: premdb.public.match

You cannot create, drop, or alter tables, views, and procedures remotely. You must create, drop, or alter them from the current database. For example, the following command fails because it attempts a cross-database ALTER VIEW:

yellowbrick=# alter view premdb.public.vteam rename to viewteam;
ERROR:  cross-database references are not allowed: "premdb.public.vteam"

DESCRIBE: You cannot describe remote tables. To run the DESCRIBE command on a table, connect to the database where the table was created.

CREATE PROCEDURE: You can create stored procedures in the current database only, and you can call stored procedures only if they were created in the current database. A stored procedure that is created with the SETOF table syntax must refer to a table that is created in the current database (not a remote table).

Queries and other commands cannot read from a remote UTF8 database and write to a local LATIN9 database. The opposite path is supported: reading from a remote LATIN9 database and writing to a local UTF8 database.

Note: If you create a view with cross-database table references, and one of the referenced databases is dropped and re-created or restored, the restore will not contain the view. The view was based on a reference to the original database ID, not the restored database ID. The sys.view system view will still contain a record of the view, but it will not be functional.

Examples

Assume that the same tables exist in both the yellowbrick database and the premdb database on the same appliance:

yellowbrick=# \d
          List of relations
 Schema |   Name   | Type  |  Owner  
--------+----------+-------+---------
 public | awayteam | table | yb007
 public | hometeam | table | yb007
 public | match    | table | yb007
 public | season   | table | yb007
 public | team     | table | yb007
(5 rows)

yellowbrick=# \c premdb
You are now connected to database "premdb" as user "yb007".
premdb=# \d
          List of relations
 Schema |   Name   | Type  |  Owner  
--------+----------+-------+---------
 public | awayteam | table | yb007
 public | hometeam | table | yb007
 public | match    | table | yb007
 public | season   | table | yb007
 public | team     | table | yb007
(5 rows)

In the following examples, premdb is the "local" database.

This example selects from one remote table:

premdb=# select distinct winners from yellowbrick.public.season where winners is not null;
      winners      
-------------------
 Manchester United
 Blackburn Rovers
 Chelsea
 Manchester City
 Leicester City
 Arsenal
(6 rows)

This example is a union of a local table and a remote table.

premdb=# select * from yellowbrick.public.hometeam union select * from premdb.public.awayteam 
order by htid;
 htid |          name           
------+-------------------------
    2 | Arsenal
    3 | Aston Villa
    4 | Barnsley
    5 | Birmingham City
...

This example creates a local table by joining a local table and a remote table:

premdb=# create table home_and_away as 
select pph.htid, ypa.atid, pph.name 
from premdb.public.hometeam pph join yellowbrick.public.awayteam ypa on pph.name=ypa.name 
order by pph.name;
SELECT 47
premdb=# select * from home_and_away;
 htid | atid |          name           
------+------+-------------------------
    2 |   51 | Arsenal
    3 |   52 | Aston Villa
    4 |   53 | Barnsley
    5 |   54 | Birmingham City
    6 |   55 | Blackburn Rovers
    7 |   56 | Blackpool
...

In this example, the user is connected to the test database, but the query joins a table in the yellowbrick database with a table in the premdb database:

test=# select * 
from premdb.public.awayteam ppa
join yellowbrick.public.match ypm
on ypm.atid = ppa.atid 
order by ypm.atid limit 3; 
 atid |  name   | seasonid |      matchday       | htid | atid | ftscore | htscore 
------+---------+----------+---------------------+------+------+---------+---------
   51 | Arsenal |        6 | 1997-09-27 00:00:00 |   18 |   51 | 2-2     | 0-2
   51 | Arsenal |        3 | 1995-02-25 00:00:00 |   16 |   51 | 0-3     |    
   51 | Arsenal |        3 | 1994-12-28 00:00:00 |   21 |   51 | 0-2     |    
(3 rows)

The following INSERT selects from the remote table and inserts into the local table.

premdb=# insert into public.awayteam select * from yellowbrick.public.awayteam;
INSERT 0 50

The following statements are not allowed because they attempt to write to the remote database:

premdb=# insert into yellowbrick.public.awayteam select * from public.awayteam;
ERROR:  cross-database modifications are not allowed: yellowbrick.public.awayteam
premdb=# delete from yellowbrick.public.awayteam;
ERROR:  cross-database modifications are not allowed: yellowbrick.public.awayteam