PostgreSQL cross-Database Query

First, I declare that I have just entered the PostgreSQL camp from the MSSQL camp. I have been deeply impressed by MSSQL over the past few days!

Go to the topic. In MSSQL, you can easily use select * From otherdb for cross-database (same server or different servers) queries. DBO. mytable (different databases on the same server) or select * From otherserver. otherdb. DBO. mytable (different databases on different servers, where otherserver is a linked server) is complete, which is very convenient!

When I used PostgreSQL over the past few days, I suddenly found that cross-database queries need to be implemented in PostgreSQL. I thought it could be done in a way similar to "database. Data Table". The results failed ...... I checked it from the Internet and learned that PostgreSQL needs to use the dblink function to perform cross-database or cross-server queries. It is said that it is modeled on the dblink of Oracle and it is quite good. I tried to configure it, detailed configuration process is provided for your reference.

1. Install dblink Extension

For more information about Linux, see:

PostgreSQL tips

Http://tieba.baidu.com/F? Kz= 163201318 #

For Windows, see:

Configuring the dblink function of postgresql in Windows

Http://blog.csdn.net/lr2651/article/details/1375769

In Windows, dblink configuration is easier than in Linux, because after you install PostgreSQL, dblink exists in the PostgreSQL/8.1/lib directory of your installation directory. DLL file (equivalent to dblink in Linux. so file), this is the function file necessary to use dblink, of course, in Linux environment you can create through the following method. so file: # cd contrib/dblink # Make # make install the next task is to import the dblink function in the database where you need to remotely access other databases, these functions have been officially written for you, that is, dblink stored in the PostgreSQL/8.1/share/contrib directory. SQL file, you only need to enter the PostgreSQL command line to execute the following statement :...... bin> Psql-d [database name]-f [SQL file name (that is, dblink. SQL file path + name)]. Okay, if you see a string of CREATE FUNCTION, it means it is successful. Next, let's get a feel of the dblink function.

Note: I use postgresql9.1 and cannot use Psql-D mydb-F dblink -- 1.0. SQL:

Use "CREATE EXTENSION dblink" to load this file.

Later, I simply used

psql -d mydb -U lslxdx

Enter the password, enter the PLSQL shell, and enter

CREATE EXTENSION dblink;

Return

CREATE EXTENSION

Success.

2. Use dblink

Enter the query window in pgadmin and execute:

select * into stu from dblink('host=127.0.0.1 dbname=postgres user=lslxdx password=123456','select * from stu') as stu(sid int,sno varchar(32),sname varchar(64));

You can query the items in the S library in other mydb databases ~

For use of dblink, refer:

A. Questions about PostgreSQL cross-Database Query

Http://topic.csdn.net/u/20100203/14/def81dd8-e6cc-4524-867d-6f4b6f99836c.html

B. PostgreSQL 8.3.17 documentation

Http://www.postgresql.org/docs/8.3/static/contrib-dblink.html

3. Note

Run

psql -d mydb

The default logon User Name (username) is the computer user name. For example, if leesonlog is used, if the user name is not in the database, the password is entered later, it must be difficult, so we should use

psql -d mydb -U lslxdx

-You can add the database login name to the U parameter. You can use Psql to access the PLSQL shell, or double-click "SQL shell (Psql)" in the PostgreSQL program group from the Start menu, and press Enter, directly go to the shell of PLSQL and add "C: \ Program Files \ PostgreSQL \ 9.1 \ bin" to the path of the environment variable. Therefore, you can directly enter Psql in any directory, otherwise, you must enter "C: \ Program Files \ PostgreSQL \ 9.1 \ bin" to run Psql. In postgresql9.1, dblink. SQL is placed in "C: \ Program
Files \ PostgreSQL \ 9.1 \ share \ extension "folder named" dblink -- 1.0. SQL ". When you run" Psql-D mydb-F dblink -- 1.0. SQL ", you must specify dblink. SQL file path (relative or absolute ).