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
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
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.
sys.view
system view will still contain a record of the view, but
it will not be functional. Examples
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.
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)
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
...
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)
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
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