Thread: Join with other database's table
Hi. I am newbie of POSTGRESQL. Please, share your knowledge with me. I have 2 diffrent databases. There is table that has member's information in the first database and bbs table is in another database. I'd like to join these two tables. But I can not found related information. Is it impossible with POSTGRESQL?
Thank you for your answer. I am developing WWW site that is serviced in 4 difference language, english, chinese, japanese, korean. I allocated one database for one language. each DB will have same tables that have same kind of information in different language. But I want to keep member's information in one table. In my eyes, It is basic feature in recent DBMS that support joinning tables cross over the databases, isn't it? I'm sorry. The PostgreSQL is GREAT one, but it is not perfect as you know. Chris Bitmead wrote: > > Sungchul Park wrote: > > > > Hi. > > I am newbie of POSTGRESQL. Please, share your knowledge with me. > > I have 2 diffrent databases. There is table that has member's > > information in the first database and bbs table is in another database. > > I'd like to join these two tables. But I can not found related information. > > Is it impossible with POSTGRESQL? > > No. > > Why do you have them in different databases? Why not copy them into one > database? > >
Sungchul Park <scpark@gen128.com> writes in pgsql-general@postgresql.org: > I am developing WWW site that is serviced in 4 difference language, > english, chinese, japanese, korean. I allocated one database for one > language. each DB will have same tables that have same kind of > information in different language. But I want to keep member's > information in one table. If you're using 7.0 you should be able to do this in one database if you select a multilingual database encoding. I've tested mule_internal encoding and it works. Unicode (UTF-8) encoding should also work, though there are no conversion routines to go from a Unicode database to an EUC Japanese client (for example).
> I am developing WWW site that is serviced in 4 difference language, > english, chinese, japanese, korean. > I allocated one database for one language. Why?
Because I want to use same table name. I mean... For example, I have a BBS code. That code store data into 'pubbbs' table. As you know I need 4 tables for one service. Yes, I can use 4 different table name. As 'pubbbs_en', 'pubbbs_jp', 'pubbbs_kr', 'pubbbs_cn'. But I will make much more services and there will be too many table in one database. (Could you understand? I am not well english speaker.) I like simple and it looks simple way to allocate datebase for each language. I believed this schema is not special one. Many DBMSs are support this, even MySQL. Chris Bitmead wrote: > > > I am developing WWW site that is serviced in 4 difference language, > > english, chinese, japanese, korean. > > > I allocated one database for one language. > > Why? > >
Thank you. I already decide to use Mule-Internal encoding. In fact, there is no other choice. But I'm still not sure that I can solve my problem with mule -internal encoding. 1. How can I copy(or backup) mule-internal encoded data. Mule-Internal is for just back-end. If there is a table that have rows in many different kind of language. I think, I could not back it up. Am I wrong? 2. I think Unicode is better then mule-internal. So, I will use unicode encoding as soon as postgresql support automatic unicode translation. At that time, should I translate mule-internal encoded table to unicode. ( I know there is positive side in mule-internal. One is It store string with it's char set code. So I can distinguish the original char set of character string. Another is postgresql use UTF-8 to store Unicode data. It has a lot of over head when you encode CJK char. string.) 3. I don't know how postgresql sort mule-internal encoded data. Mule- internal is not another char set. It just store 2 byte char with it's char set code. I mean it store 1 character(2bytes) in 3 bytes. I just guess, postgresql may sort data by it's char set, first. SL Baur wrote: > > Sungchul Park <scpark@gen128.com> writes in pgsql-general@postgresql.org: > > > I am developing WWW site that is serviced in 4 difference language, > > english, chinese, japanese, korean. I allocated one database for one > > language. each DB will have same tables that have same kind of > > information in different language. But I want to keep member's > > information in one table. > > If you're using 7.0 you should be able to do this in one database if > you select a multilingual database encoding. I've tested mule_internal > encoding and it works. Unicode (UTF-8) encoding should also work, > though there are no conversion routines to go from a Unicode database > to an EUC Japanese client (for example). > >
Sungchul Park wrote: > > Because I want to use same table name. I mean... For example, I have a > BBS code. That code store data into 'pubbbs' table. As you know I need > 4 tables for one service. > Yes, I can use 4 different table name. As 'pubbbs_en', 'pubbbs_jp', > 'pubbbs_kr', 'pubbbs_cn'. But I will make much more services and there > will be too many table in one database. Too many in what way? Personally I think the whole idea of having 4 tables that store the same kind of data will lead you to problems later on. I think I'd have a language attribute in the one table. > (Could you understand? I am not > well english speaker.) > I like simple and it looks simple way to allocate datebase for each > language. I believed this schema is not special one. Many DBMSs are > support this, even MySQL.
> I am developing WWW site that is serviced in 4 difference language, > english, chinese, japanese, korean. I allocated one database for one > language. each DB will have same tables that have same kind of > information in different language. But I want to keep member's > information in one table. Some system tables are already shared among databases. Can we do this for user tables? -- Tatsuo Ishii
> 1. How can I copy(or backup) mule-internal encoded data. Mule-Internal
> is for just back-end. If there is a table that have rows in many
> different kind of language. I think, I could not back it up. Am I wrong?
Just do it. You can use copy, pg_dump etc as ordinary encodings. The
result would be a mule_internal text file. Even you could view it
using the famous multilingual editor, mule.
> 2. I think Unicode is better then mule-internal. So, I will use unicode
> encoding as soon as postgresql support automatic unicode translation. At
> that time, should I translate mule-internal encoded table to unicode. (
> I know there is positive side in mule-internal. One is It store string
> with it's char set code. So I can distinguish the original char set of
> character string. Another is postgresql use UTF-8 to store Unicode data.
> It has a lot of over head when you encode CJK char. string.)
Yes, once we translate CJK encoding to Unicode, we could not translate
it back to the original encoding unless we have separate
information. That's the big problem with Unicode.
> 3. I don't know how postgresql sort mule-internal encoded data. Mule-
> internal is not another char set. It just store 2 byte char with it's
> char set code. I mean it store 1 character(2bytes) in 3 bytes. I just
> guess, postgresql may sort data by it's char set, first.
Your guess is almost correct. Each mule_internal letter has a "leading
character" (1 byte) first, then actual charset data (variable length
bytes, 1 to 4 depending on its charset) follows. So sorting would
tend to take charsets having smaller leading charcters first. The
order could be roughly:
ASCII ISO8859 Chinese Japanese Korean...
See src/include/mb/pg_wchar.h for more details.
--
Tatstuo Ishii