Skip site navigation (1) Skip section navigation (2)

Is SELECT FROM multiple databases possible??? (USE db?)

From: J Kinsley <jkinsley(at)horus(dot)bticc(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Is SELECT FROM multiple databases possible??? (USE db?)
Date: 1999-06-24 23:40:46
Message-ID: Pine.LNX.4.02.9906241940260.2013-100000@horus.bticc.net (view raw or flat)
Thread:
Lists: pgsql-sql
Is it possible to do something like the following in PostgreSQL???

CREATE DATABASE pub;
CREATE TABLE states (
	st_abv		CHAR(2),
	st_name		VARCHAR(24)
);
INSERT INTO states VALUES('AL', 'Alabama');
...
INSERT INTO states VALUES('OH', 'Ohio');
...
INSERT INTO states VALUES('WY', 'Wyoming');



CREATE DATABASE accounts;
CREATE TABLE address (
	adr_id		INT4,
	...
	adr_city	VARCHAR(24),
	adr_state	CHAR(2),
	...
);

INSERT INTO address VALUES ('1',...,'Akron','OH',...);
INSERT INTO address VALUES ('2',...,'Canton','OH',...);

-- Connect to accounts database
USE accounts;	-- From MySQL

SELECT a.adr_id AS id, a.adr_city AS city, s.st_name as state 
	FROM address a, pub.states s 
		WHERE a.adr_state = s.st_abv;

This query returns a parse error on the '.' in pub.states.  If I move
the states table into the accounts and replace pub.states with
states, the query works properly.  However, I would like to keep one
copy of states in the pub database instead of having to create a copy
in every databae that will use it.

While I chose the above as an example, I have other tables which I
would also like to store in a single database while being accessable
from an arbitrary number of other databases.

In MySQL, this can be accomplished with the USE statement which from
the documentation appears to be for Sybase compatibility.  Since
there is no USE statement in PostgreSQL, is there another way to
achieve the desired results?

TIA

Regards,
Jarrod Kinsley 



pgsql-sql by date

Next:From: Wayne PiekarskiDate: 1999-06-25 06:52:36
Subject: Re: trouble creating log table with rules
Previous:From: J KinsleyDate: 1999-06-24 23:32:48
Subject: Is SELECT FROM multiple databases possible??? (USE db?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group