Re: [GENERAL] Using tables in other PostGreSQL database
Wow seems like this post took on a life of it's own. All I wanted to do was to be able to use a table that someone else has all ready created. Seems like somewhere someone mentioned a DBA ( which I'm assuming to be Database Administrator ) well as far as I know we don't have one though I wish we did. The basis of my question comes from the fact that I currently use Multiple access databases. Each database contains 1 piece of information ( information that on it's own has no relationship to other data ), but information in other databases will use items from it in it. Hence in MSAccess I LINK the tables in. Which I know is nothing more than a connection. I wish I could say that I knew with certainty what schemas are or 2PC is. Would be nice if I had exposure to other databases as well. I'm sure that I'd have the same questions about MSft's SQL server. But I thank the forum here for all the input. Regards, Barry Pettis CSO Atmel Corp Project Tech -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Daniel Verite Sent: Monday, March 31, 2008 3:36 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Using tables in other PostGreSQL database Scott Marlowe wrote: Even in Oracle you don't have cross db queries. On the contrary you do. You can refer to objects in another database by [EMAIL PROTECTED], very useful to mix local and remote data in no time. DBLINK_NAME represents a connection to another database. What you don't have is OTHERDB.OBJECT_NAME to refer to a different database within the same instance, because there is only one database in an Oracle instance. What you are talking about are cross schema references, not cross db. No I'm definitively referring to cross db, not cross schema. See this piece from Oracle documentation: http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elem ents009.htm#i27761 Oracle instances can have 1 database, it's just not that common. I know this because we had an internal instance at the last company I worked at that had 2 databases in it, each with their own schemas. Or maybe they somehow had two instances of oracle running on the same box. CREATE DATABASE is to Oracle what initdb is to PG, it's something you do once per instance. So no, an Oracle instance doesn't have 1 databases, just like a PG instance doesn't have 1 data directories. Regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using tables in other PostGreSQL database
My bad. 1. I work at a Manufacturing plant. 2. We identify different process steps using numbers along with a short and long description 3. For a part we manufacture the system identifies SOME properties of said part. Things like nuper of process steps, part name, part family, and other items. 4. Using various other properties, the total of all process steps of said part being one, I can identify what kind of metals said part uses by referencing the SHORT DESC of the process steps. Now on it's own the PROCESS STEP is what I consider an atomic data meaning that the if I look at it there is not a sub piece that it could be broken down to. So when I need to store that data I place it into it's own MSAccess Database. However, I have a database for all of the parts my site manufactures. I have a need to be able to extract a parts full process flow so using some other tables which will identify and order the process steps I can make a process flow which lists the short or long description depending on my need. I have another database which I need to look at the inventory ( not caring what particular part just qty ) at a particular process step. Now I may want to see one desc or another. However, the fact that a part is placed in a family value of say Microcontroller or ASIC or EEPROM it doesn't have any relationship to say a particular PROCESS STEP hence my comment of no relation ( I guess I should have said tenuous relation ) instead of no relation. Now as I see it logically I have 3 different databases ( 1 for holding atomic information about a process step, 1 for being able to list a products manufacturing flow, and 1 that is an inventory ). So in my logic I either have to have the PROCESS STEP data in 2 databases or I have it in one database and other databases just LINK to it. Now with the discussion in this thread I'm beginning to see that the term LINK is kind of MSAccess unique. So yes I use multiple databases. ( 4 of which are ) 1. Lists all pats I manufacture ( includes general summary information like cycle time, number of process', number of critical process', number of metals used, etc... ) 2. Lists all process steps at my site, and indicates if it is a metal and which type, if it's a critical process or not, which functional area said process resides ) 3. Lists the substeps for a particular PROCESS STEP to determine how many internal steps there are to any given process ( this can be dependent on the part ) 4. Lists IF a part has an ENGINEERING restriction to a particular tool and which tool part has to run on at a given Process Step. I guess the biggest problem is trying to describe something in terms that all can understand. On a forum like this most CONTRIBUTORS those who answer questions speak a language that the typical novice poster doesn't use or understand. So when the question is posed there is a disconnect between poster and responder. So I'm hoping that I've been able to adequately clear the air. If not it's always fun to try. -Original Message- From: Adrian Klaver [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 01, 2008 8:30 AM To: pgsql-general@postgresql.org Cc: Pettis, Barry Subject: Re: [GENERAL] Using tables in other PostGreSQL database On Monday 31 March 2008 4:20 am, Pettis, Barry wrote: Wow seems like this post took on a life of it's own. All I wanted to do was to be able to use a table that someone else has all ready created. Seems like somewhere someone mentioned a DBA ( which I'm assuming to be Database Administrator ) well as far as I know we don't have one though I wish we did. The basis of my question comes from the fact that I currently use Multiple access databases. Each database contains 1 piece of information ( information that on it's own has no relationship to other data ), but information in other databases will use items from it in it. Hence in MSAccess I LINK the tables in. Which I know is nothing more than a connection. Now I am confused. If the data has no relationship to other data why do you need to link to it? Second in your original post you said you where trying to pull data from multiple Postgres databases now you say you are using multiple Access databases. I wish I could say that I knew with certainty what schemas are or 2PC is. Would be nice if I had exposure to other databases as well. I'm sure that I'd have the same questions about MSft's SQL server. But I thank the forum here for all the input. Regards, Barry Pettis CSO Atmel Corp Project Tech http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using tables in other PostGreSQL database
I work at a fairly large company 2000 people just at my site alone. I've been given access to a new database which will be used for source data for some software that the company just purchased. Now this PostGreSQL server has many databases in it. Some of which I have access to. I don't want to reinvent the wheel ( so to speak ) by having to replicate the table in my database. Then having to create routines that will extract from A to replicate in B. How do I reference a table in another database? The other user all ready updates and adds important information so I don't want to have to replicate his/her work. I just want to purloin his finished product... Though I think that that's the whole point. Also to respond in the thread... Do I just reply to the message? Regards, Barry Pettis http://www.ozgrid.com/forum/misc.php?do=getsmilieseditorid=vB_Editor_00 1 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using tables in other PostGreSQL database
It sounds to me like you want to share a single database between
users,
possibly using a suitable mix of schemas and roles to apply suitable
permissions. If you don't want them to have shared access to the data
then you can have separate databases and grant them access only to
their
own DB.
Ok this kind of hits my issue right on the head. One of my tasks is to
generate a summary report of metrics from the local site here. Now each
week I need to summarize information on the product that was shipped
that week. My IS dept exports a text report each week which contains (
some of the information on the shipped product ). I know that another
individual uses this information for reports that he has to do. So
right now he is parsing this data back out from the report and storing
the info in a PostGre table. You said the point of a database was
coherent set of connected facts. Now his use of the report is to report
to his supervisor what and how product for his business group the
factory has made and shipped each week. Now the raw report is ALL
devices. Because he is extracting it he stores the entire thing not
just his small section of it.
Now like I said I'm being tasked to report for the mfg division on how
much, how fast, and other metrics which begins with data in his
database. Now the coherent facts thing would state that I shouldn't
put a mfg report info into the business group ( marketing ) groups
database. However, he has this table in his database. I want to use
it. Now for me I'd store this information in it's own database and give
everybody restricted access. This way if they need it they can get it
and they can use it. But as of right now I can't tell one database to
look in another database and use a table that it finds there. I think
that kind of restricts the use of data, or it promotes the duplication
of data being stored.
I can't answer to foreign-keys or databases on different servers
etc... due to my lack of ( sufficient { will that work } ) knowledge.
So maybe as my experience grows things will become clearer.
It's just right now I had to make my own data loader and store this data
that I know is being done by another... From a business productivity
point of view I see this as a waste of money.
Regards,
Barry Pettis
-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 27, 2008 5:55 AM
To: Pettis, Barry
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using tables in other PostGreSQL database
Pettis, Barry wrote:
An addon Being self schooled in databases to me this seems to be
a
kludge.
Ah, well, self-schooling is always a good position from which to make
sweeping generalisations.
If you work in a large company environment the odds that
someone somewhere is all ready storing or collecting data that you
need
( by this I mean base data ) could probably be pretty high. So why,
if
PostGre is so old/established, is the ability to share information
between databases have to be done through an add on.
Because the whole point of a database is to be a coherent set of
connected facts. What is your opinion on handling:
1. Differing character-sets in different databases
2. Differing locales+sorting in different databases
3. Cross-database foreign-keys and other constraints
4. Differing objects with the same names in different databases
5. Handling #1-4 when the databases are on different physical servers.
Now let's say that another person NEEDS that very information in a
query
or table in their own database. Are you saying that each person needs
to generate this. To me the sharing of information seems to be so
basic
that within a said postgre server, that as along as you have access to
a
said database you should be able to say use the data stored here. And
that that ability should be a rudimentary ability not an addon.
It sounds to me like you want to share a single database between users,
possibly using a suitable mix of schemas and roles to apply suitable
permissions. If you don't want them to have shared access to the data
then you can have separate databases and grant them access only to their
own DB.
Reason why I don't' have ability to install addon's onto the database.
Nobody is forcing you to. You'll have problems with procedural
languages, custom types, dictionaries, replication, GIS though.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using tables in other PostGreSQL database
An addon Being self schooled in databases to me this seems to be a kludge. If you work in a large company environment the odds that someone somewhere is all ready storing or collecting data that you need ( by this I mean base data ) could probably be pretty high. So why, if PostGre is so old/established, is the ability to share information between databases have to be done through an add on. So let me give an example to help clarify. 1. I work in a manufacturing environment 2. Our product can have 150 to 450 different / unique process steps 3. We have a description of each process step 4. So with a product we can look at it's flow and see the descriptions of each step Now say person A pulls this information on a daily basis and then summarizes the product manufacturing information and creates a table that has say the total number of process modules ( aka group of steps ), the total number of steps, the total number of a particular type of step. Now let's say that another person NEEDS that very information in a query or table in their own database. Are you saying that each person needs to generate this. To me the sharing of information seems to be so basic that within a said postgre server, that as along as you have access to a said database you should be able to say use the data stored here. And that that ability should be a rudimentary ability not an addon. Reason why I don't' have ability to install addon's onto the database. Regards, Barry Pettis -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2008 3:45 AM To: Pettis, Barry Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Using tables in other PostGreSQL database Pettis, Barry wrote: Now this PostGreSQL server has many databases in it. Some of which I have access to. I don't want to reinvent the wheel ( so to speak ) by having to replicate the table in my database. Then having to create routines that will extract from A to replicate in B. How do I reference a table in another database? There is an add-on in contrib/ called dblink that lets you connect to a remote database. There's also a dbilink project that uses Perl. Also to respond in the thread... Do I just reply to the message? Reply-All is the default on the pg lists. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Make MS Access UPDATE PostGre SQL Table
Hi, I'm fairly new to DBMS's and PostGre SQL in particular. Most of my experience is with MS Access. I've created MS Access to retrieve data from PostGre by linking tables and have done so without error. However, I am now in need of being able to update a PostGre table. The source will be a .csv file. I've seen a COPY command in PostGre that will allow you to specify a .csv file as the source, but in looking at it it appears that it deletes and then creates the table with the stored data. I need to be able to Update or Append data from a file. Maybe going the route of a temp file then update from that. I'm using Access as that is a point of comfort for me. I can write a .net procedure though I'm not exactly firm on the procedure. Thoughts on how to do the entire front end in Access. Or Thoughts on whether to do it from a .net perspective. MSAccess ( 2003 ) PostGreSQL ( 8.1.8 ) Regards, Barry Pettis CSO Atmel Corp
