Introduction to PostgreSQL dblink
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. 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. In this article, we will learn about the dblink method of PostgreSQL, its syntax, and examples demonstrating retrieving records situated at the remote database.
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])
where the 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 – It is 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 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. For example –
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 the datatype and name of the 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
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 –
SELECT * FROM remote_educba_data WHERE technologies LIKE 'psql%';
Example
Let us perform the above-mentioned solution on our terminal. Firstly, we will log in to my psql command prompt and connect to my educba database.
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 the existing educba table on Postgres database using query –
SELECT * FROM educba;
Now, we will use a dblink database extension. But before that, we will have to create the dblink extension using the following query –
CREATE EXTENSION dblink;
that gives the following 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 –
SELECT *
FROM dblink('dbname=postgres user=postgres password=a',
'select id, technologies from educba')
AS demo(id integer, technologies varchar)
WHERE technologies LIKE 'psql%';
that gives the following output –
Now, we will use a dblink database extension. But before that, we will hao create the dblink extension using the following query –
CREATE EXTENSION dblink;
that gives the following 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 specify column names and type again.
Recommended Articles
This is a guide to PostgreSQL dblink. Here we discuss introduction, syntax, parameters, and examples with code implementation. You may also have a look at the following articles to learn more –
