Thread: 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
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/
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 >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
>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
>--
>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