Introduction to PostgreSQL Link
Whenever we want to access the database that is situated away from us and other than out local machine then we can take the remote connection of that database and fire any query on that database that in turn returns the number of the rows as the result using dblink in PostgreSQL database. To link to other databases, PostgreSQL has provided us with the module or you can say an extension named dblink. The remote access of the database is only possible if you have permissible rights to access remote databases and you have sufficient information about the connection details.
Syntax:
dblink_connect is responsible for setting up the connection while dblink is basically used to execute the SQL query on a remote server. Most often, the select query is used with dblink. However, any query that returns the number of rows as return value can be used with dblink.
There are three possible syntaxes of dblink that are as follows:
dblink(text name_of_connection, text query_statement [, boolean fail_on_error])
dblink(text connection_string, text query_statement [, boolean fail_on_error])
dblink(text query_statement [, boolean fail_on_error])
Parameters specified have the following purpose:
- name_of_the connection: It is the name of the connection that you want to provide. It can even be skipped in case if you want to create an unnamed connection.
- connection_string: It is the string that contains information about the database connection to be made with the remote database server in the libpq-style.
The structure of that information string is somewhat like the following example:
hostaddr=192.16.10.11 port=5432 dbname=demoDatabaseName user=anyPostgresUser password=password_that_is_set options=-c.
- query_statement: The query statement that you wish to execute on the remote database server to retrieve the rows stored there and that satisfy your query specifications.
- fail_on_error: This is a boolean parameter that has the default value set to true when skipped or omitted in the specification as it is an optional parameter. When specified true or not specified it results in an error on your database when the error has occurred on the remote database and if the value is set to false then it raises the notice on your side when an error has occurred on the remote database server.
There are two syntaxes that have two text parameters at the beginning of it and have the same signature. Hence, whenever two text parameters are supplied to the dblink method then firstly it goes for analyzing whether the first text parameter is any database connection name that exists. If not then it goes for considering it as connection information string and builds up the connection accordingly.
Returned Value and Usage
The dblink() functions return the set of the rows as records and you need to specify the names of the columns you need to retrieve from the remote database’s table and as the retrieving query will not have any idea about the type of the columns that will be fetched, it is required to specify the name of the columns as well as the data type of the columns that will be retrieved from dblink() call’s return value.
Example:
Code:
SELECT *
FROM dblink('dbname=demoDatabaseName user=anyPostgresUser password=password_that_is_set options=-csearch_path=',
'select id, technologies from educba')
AS demo(id integer, technologies varchar)
WHERE technologies LIKE 'psql%';
As shown above, it is necessary to specify the column names in the SQL query password as a parameter to the dblink() method and while retrieving the records from the select query on the return value from dblink(), it is necessary to mention the name of the columns and the data type of the columns so that the system will understand that the column will take that much space after retrieving. Specifying names of the column was already compulsory in SQL syntax while using the alias. Specifying data types along with column names is the norm introduced and used in PostgreSQL while using the alias.
During the run-time, if the column values retrieved from dblink() do not match the column count from the select query’s alias an error will be thrown saying that column count does not match with the retrieved results. As it is a very clumsy thing to mention the names of columns as well as the datatypes every time the dblink call is given, the most convenient way to do this is to create the view for the same. In this way, we will not have to mention datatype and name of column every time we want to retrieve the values from the remote database. We will just fire a select query on the view that we have created on our database.
For example, using the same use-case as of above example we will create a view named remote_educba_data and later whenever required will retrieve the data from that view instead of using dblink and all that lengthy syntax.
Code:
CREATE VIEW remote_educba_data AS
SELECT *
FROM dblink('dbname=demoDatabaseName user=anyPostgresUser password=password_that_is_set options=-csearch_path=',
'select id, technologies from educba')
AS demo(id integer, technologies varchar)
WHERE technologies LIKE 'psql%';
For later usage, we will use the following query:
Code:
SELECT * FROM remote_educba_data WHERE technologies LIKE 'psql%';
Example of PostgreSQL Link
Given below is the examples mentioned:
Let us take the above-mentioned solution on our terminal. Firstly, we will log in to my psql command prompt and connect to my educba database.
Code:
sudo su - postgres
Output:
Now, we want to access the table named educba stored in a Postgres database using dblink in my educba database connection. As my remote database is on the same machine my database host address and port will be default 127.0.0.1 and port will be 5432. So, we don’t need to mention them over here. My user is Postgres and the password is ‘a’.
Firstly, let us check the contents of existing educba table on Postgres database using query.
Code:
SELECT * FROM educba;
Output:
Now, we will use a dblink database extension. But before that, we will have to create the dblink extension using the following query.
Code:
CREATE EXTENSION dblink;
Output:
Now, we will fire our query in the educba database to retrieve data from the Postgres database using dblink.
Open educba database command prompt and fire following query.
Code:
SELECT *
FROM dblink('dbname=postgres user=postgres password=a',
'select id, technologies from educba')
AS demo(id integer, technologies varchar)
WHERE technologies LIKE 'psql%';
Output:
Let us now create a view that can be helpful in retrieving the data from the same database remotely using dblink.
Code:
CREATE VIEW remote_educba_data AS
SELECT *
FROM dblink('dbname=postgres user=postgres password=a',
'select id, technologies from educba')
AS demo(id integer, technologies varchar)
WHERE technologies LIKE 'psql%';'
Output:
For later usage, we will use the following query without mentioning the column names or their datatypes in future.
Code:
SELECT * FROM remote_educba_data WHERE technologies LIKE 'psql%';
Output:
Conclusion
In this way, we can use dblink to connect to remote database servers and retrieve the results from them using the dblink extension provided in Postgres. Further, we can use views to create the dblink select query structure storage. So that, we won’t have to call db_link() directly again and again in the future and specifying column names and type again.
Recommended Articles
This is a guide to PostgreSQL Link. Here we discuss the introduction to PostgreSQL Link, returned value and usage along with example respectively. You may also have a look at the following articles to learn more –
