How to access a second database

Hello Sherman,

You cannot cross reference databases like this in PG. You should re-think the schema you are going to use.
Check this line from the documentation (https://www.postgresql.org/docs/current/static/ddl-schemas.html):
“A PostgreSQL database cluster contains one or more named databases. Users and groups of users are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request.”

Regards,
Stanislav

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sherman Willden
Sent: Tuesday, October 31, 2017 9:15 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to access a second database

I am trying to access a table from another database. I have the permissions to create under my own login. I have performed the following so far:
sherman@sql-dev: createdb sandbox01
sherman@sql-dev:~$ createdb sandbox02.
After logging into sandbox02 I performed the following: sandbox02=# CREATE TABLE last_names(last_name TEXT);
sandbox02=# INSERT INTO last_names VALUES(willden);

Now I think I want to use a foreign key in sandbox01. Is the following how it works after logging into sandbox01?
sandbox01=# CREATE TABLE first_and_last(first_name TEXT, last_name FOREIGN KEY last_name REFERENCES sandbox02(last_names(last_name))

and then sandbox01=# INSERT INTO first_and_last(sherman, willden);

Thank you;
Sherman

On 10/31/2017 12:15 AM, Sherman Willden wrote:

I am trying to access a table from another database. I have the
permissions to create under my own login. I have performed the
following so far:
sherman@sql-dev: createdb sandbox01
sherman@sql-dev:~$ createdb sandbox02.
After logging into sandbox02 I performed the following: sandbox02=#
CREATE TABLE last_names(last_name TEXT);
sandbox02=# INSERT INTO last_names VALUES(willden);

Now I think I want to use a foreign key in sandbox01. Is the following
how it works after logging into sandbox01?

sandbox01=# CREATE TABLE first_and_last(first_name TEXT, last_name
FOREIGN KEY last_name REFERENCES sandbox02(last_names(last_name))

and then sandbox01=# INSERT INTO first_and_last(sherman, willden);

if you're doing a lot of this, why not use two schema in the same
database?      then its just ...schema.table...

otherwise, you need to use FDW and foreign tables.

see....
https://www.postgresql.org/docs/current/static/postgres-fdw.html

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 10/31/2017 12:41 AM, John R Pierce wrote:

if you're doing a lot of this, why not use two schema in the same
database?      then its just ...schema.table...

otherwise, you need to use FDW and foreign tables.

see....
https://www.postgresql.org/docs/current/static/postgres-fdw.html

oh, I should add... the advantage of using FDW is the other database can
be on another server.   the disadvantage is, the remote data has to be
queried and merged with the local query, the optimizer may not be able
to do as good a job as it might with tables in different schema of the
same database (which are treated exactly the same as tables in the same
schema, other than naming).

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general