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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Sat, Nov 20, 2004 at 06:09:49PM -0700, Net Virtual Mailing Lists wrote:

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/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Sat, Nov 20, 2004 at 07:37:12PM -0700, Net Virtual Mailing Lists wrote:

Unfortunately not. See a recent thread in pgsql-sql that discussed
this:

http://archives.postgresql.org/pgsql...1/msg00139.php

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/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

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




---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Sun, Nov 21, 2004 at 12:27:11AM -0700, Net Virtual Mailing Lists wrote:

The closing parenthesis is a syntax error. Did you cut and paste
this or type it by hand?

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/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

See comments below..

Yeha, sorry, I cut and pasted but fumbled.. The ); is not there - the
sequence did get created correctly...

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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

From: "Net Virtual Mailing Lists" <mailinglists (AT) net-virtual (DOT) com>


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'::t ext)

gnari



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

"Net Virtual Mailing Lists" <mailinglists (AT) net-virtual (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster