Join between databases or (???)
Hello,
I have situation where multiple databases need to use data from a common
source and it would consume way too much disk space to reproduce this
data into the many databases which require it. Is there some way to do a
join between databases or some other way of making the data in one
database available in another in a space-efficient manner? Of course it
is necessary that if this is possible any queries need to take advantage
of indexes/etc.
Thanks!
- Greg
Import Notes
Reply to msg id not found: 20041120055308.7C88C5AF7B6@svr4.postgresql.orgReference msg id not found: 20041120055308.7C88C5AF7B6@svr4.postgresql.org
I suspect that schemas are the best way to go, but gosh is it going to be
a bear to re-engineer all this stuff... Unfortunately it was all done in
the "pre-schema" days of Postgres and just evolved from there.....
If I want to take an existing table and add it into a schema is it
basically "alter database [database] rename [table] to schema.[table]"?....
.. I guess that I was just looking for confirmation there was not
something in postgres which would allow this "full featured cross-
database join" before pulling an a few all nighters...
Thanks!
- Greg
Show quoted text
On Sat, Nov 20, 2004 at 06:09:49PM -0700, Net Virtual Mailing Lists wrote:
I have situation where multiple databases need to use data from a common
source and it would consume way too much disk space to reproduce this
data into the many databases which require it. Is there some way to do a
join between databases or some other way of making the data in one
database available in another in a space-efficient manner? Of course it
is necessary that if this is possible any queries need to take advantage
of indexes/etc.You could use dblink but it might not provide all the functionality
you're looking for. Could the multiple databases possibly be converted
into multiple schemas in the same database?--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Import Notes
Reply to msg id not found: 20041121030339.GA64590@winnie.fuhr.orgReference msg id not found: 20041121030339.GA64590@winnie.fuhr.org | Resolved by subject fallback
On Sat, Nov 20, 2004 at 06:09:49PM -0700, Net Virtual Mailing Lists wrote:
I have situation where multiple databases need to use data from a common
source and it would consume way too much disk space to reproduce this
data into the many databases which require it. Is there some way to do a
join between databases or some other way of making the data in one
database available in another in a space-efficient manner? Of course it
is necessary that if this is possible any queries need to take advantage
of indexes/etc.
You could use dblink but it might not provide all the functionality
you're looking for. Could the multiple databases possibly be converted
into multiple schemas in the same database?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Sat, Nov 20, 2004 at 07:37:12PM -0700, Net Virtual Mailing Lists wrote:
If I want to take an existing table and add it into a schema is it
basically "alter database [database] rename [table] to schema.[table]"?....
Unfortunately not. See a recent thread in pgsql-sql that discussed
this:
http://archives.postgresql.org/pgsql-sql/2004-11/msg00139.php
.. I guess that I was just looking for confirmation there was not
something in postgres which would allow this "full featured cross-
database join" before pulling an a few all nighters...
PostgreSQL doesn't have any inherent cross-database capabilities.
You can use dblink to query another database and join the results
against the current database, but its capabilities probably aren't
what you'd consider "full-featured." Still, you might want to
check it out if you're not familiar with it.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Thanks for all your help Michael!
I am having one problem I just can't figure out.... In my dump file I
have something like:
CREATE SEQUENCE testschema.industries_industry_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
);
CREATE TABLE testschema.industries (
industry_id integer DEFAULT nextv
al('"testschema.industries_industry_id_seq"'::text) NOT NULL,
industry character varying(80) NOT NULL,
entered_dt timestamp with time zone,
updated_dt timestamp with time zone
);
When I try to insert a value into schema.industries it complains about
testschema.industries_industry_id_seq not existing, yet I can execute
"nextval" against that very schema.... Any idea what might be going
wrong here?...
Thanks!
- Greg
Show quoted text
On Sat, Nov 20, 2004 at 07:37:12PM -0700, Net Virtual Mailing Lists wrote:
If I want to take an existing table and add it into a schema is it
basically "alter database [database] rename [table] to schema.[table]"?....Unfortunately not. See a recent thread in pgsql-sql that discussed
this:http://archives.postgresql.org/pgsql-sql/2004-11/msg00139.php
.. I guess that I was just looking for confirmation there was not
something in postgres which would allow this "full featured cross-
database join" before pulling an a few all nighters...PostgreSQL doesn't have any inherent cross-database capabilities.
You can use dblink to query another database and join the results
against the current database, but its capabilities probably aren't
what you'd consider "full-featured." Still, you might want to
check it out if you're not familiar with it.--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Sun, Nov 21, 2004 at 12:27:11AM -0700, Net Virtual Mailing Lists wrote:
I am having one problem I just can't figure out.... In my dump file I
have something like:
CREATE SEQUENCE testschema.industries_industry_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
);
The closing parenthesis is a syntax error. Did you cut and paste
this or type it by hand?
CREATE TABLE testschema.industries (
industry_id integer DEFAULT nextv
al('"testschema.industries_industry_id_seq"'::text) NOT NULL,
industry character varying(80) NOT NULL,
entered_dt timestamp with time zone,
updated_dt timestamp with time zone
);When I try to insert a value into schema.industries it complains about
testschema.industries_industry_id_seq not existing, yet I can execute
"nextval" against that very schema.... Any idea what might be going
wrong here?...
I think you mean that you can execute nextval() against the sequence,
not the schema. Anyway, it looks like whatever created the dump
file incorrectly added double quotes around the sequence name in
the nextval() expression. What created that dump?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
See comments below..
On Sun, Nov 21, 2004 at 12:27:11AM -0700, Net Virtual Mailing Lists wrote:
I am having one problem I just can't figure out.... In my dump file I
have something like:
CREATE SEQUENCE testschema.industries_industry_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
);The closing parenthesis is a syntax error. Did you cut and paste
this or type it by hand?
Yeha, sorry, I cut and pasted but fumbled.. The ); is not there - the
sequence did get created correctly...
CREATE TABLE testschema.industries (
industry_id integer DEFAULT nextv
al('"testschema.industries_industry_id_seq"'::text) NOT NULL,
industry character varying(80) NOT NULL,
entered_dt timestamp with time zone,
updated_dt timestamp with time zone
);When I try to insert a value into schema.industries it complains about
testschema.industries_industry_id_seq not existing, yet I can execute
"nextval" against that very schema.... Any idea what might be going
wrong here?...I think you mean that you can execute nextval() against the sequence,
not the schema. Anyway, it looks like whatever created the dump
file incorrectly added double quotes around the sequence name in
the nextval() expression. What created that dump?
pg_dump created it with the double quotes, I have been modifying the dump
to make it so appropriate things get created inside the schema, so I
added in the testschema. part of it in this example.
- Greg
Show quoted text
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
From: "Net Virtual Mailing Lists" <mailinglists@net-virtual.com>
See comments below..
CREATE TABLE testschema.industries (
industry_id integer DEFAULT nextv
al('"testschema.industries_industry_id_seq"'::text) NOT NULL,
[...]When I try to insert a value into schema.industries it complains about
testschema.industries_industry_id_seq not existing, yet I can execute
"nextval" against that very schema.... Any idea what might be going
wrong here?...pg_dump created it with the double quotes, I have been modifying the dump
to make it so appropriate things get created inside the schema, so I
added in the testschema. part of it in this example.
if you put the schema name inside the double quotes, it gets interpreted
as part of the relation name.
try DEFAULT nextval('testschema."industries_industry_id_seq"'::text)
or DEFAULT nextval('"testschema"."industries_industry_id_seq"'::text)
or DEFAULT nextval('testschema.industries_industry_id_seq'::text)
gnari
"Net Virtual Mailing Lists" <mailinglists@net-virtual.com> writes:
CREATE TABLE testschema.industries (
industry_id integer DEFAULT nextval('"testschema.industries_industry_id_seq"'::text) NOT NULL,
pg_dump created it with the double quotes, I have been modifying the dump
to make it so appropriate things get created inside the schema, so I
added in the testschema. part of it in this example.
Ah-hah. You put the testschema. part in the wrong place then. Correct
is
nextval('"testschema"."industries_industry_id_seq"'::text)
Or you could leave out the double quotes.
regards, tom lane