Solved

Creating a view of two tables from two different databases in postgresql

Posted on 2007-11-30
4
2,795 Views
Last Modified: 2013-12-07
I have this MYSQL code.  This code creates a view in database asterisk and reads from another databse called openser.

CREATE VIEW vmusers AS
SELECT phplib_id as uniqueid,
  username as customer_id,
  'default' as context,
  username as mailbox,
  vmail_password as password,
  CONCAT(first_name,' ',last_name) as fullname,
  email_address as email,
  NULL as pager,
  datetime_created as stamp
FROM openser.subscriber;

I'm trying to figure out how to translate that into Postgresql code.  I have looked around but cant seem to find any docs on how to accomplish this.

Right now I get
ERROR: schema "openser" does not exist
SQL state: 3F000
When I try to execute the above code in postgres.
0
Comment
Question by:sean-keys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 20385981
See the source distribution directory /contrib/dblink
0
 

Author Comment

by:sean-keys
ID: 20386439
The two databases exist on the same server.  Isn't there a way to do it like it was done in MySql  (FROM openser.subscriber; " ?   Dblink looks like it will perform the task, I was just hoping for a cleaner way.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 20386601
If the two databases do not need to be on separate machines you can put the two tables in two different SCHEMA
0
 

Author Comment

by:sean-keys
ID: 20386686
I'm sure I'll make it work one way or the other.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question