Thread: [GENERAL] 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