Updated May 24, 2023
Introduction to PostgreSQL dblink
Whenever we need to access a database located remotely rather than on our local machine, we can establish a remote connection to that database. Using dblink in PostgreSQL, we can execute queries on the remote database and retrieve the number of rows as a result. Remote access to the database is only possible if you have permissible rights to access remote databases and 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 a return value can be used with dblink.
Three possible syntaxes of dblink 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 is the name of the connection you want to provide. It can even be skipped if you want to create an unnamed connection.
- connection_string – The string 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 you wish to execute on the remote database server to retrieve the rows stored there that satisfy your query specifications.
- fail_on_error – This is a boolean parameter with the default value set to true when skipped or omitted in the specification as 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. Setting the value to false will raise a notice on your side when an error occurs on the remote database server.
Two syntaxes 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, it analyzes whether the first text parameter is any existing database connection name. If not, it considers it as a 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 the names of the column was already compulsory in SQL syntax while using the alias. Specifying data types and column names is the norm introduced and used in PostgreSQL while using the alias. During runtime, if the column values obtained from dblink() do not align with the column count specified in the select query’s alias, an error will be thrown, indicating a mismatch in the column count between the retrieved results and the query.
Since it can become cumbersome to repeatedly mention the column names and datatypes in every dblink call, the most convenient approach is to create a view for the same purpose. 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 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 host address and port will default to 127.0.0.1, and the 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 the Postgres database using query –
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 –
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 the educba database command prompt and fire the 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 have to create the dblink extension using the following query –
CREATE EXTENSION dblink;that gives the following output –
Conclusion- PostgreSQL dblink
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() again and again in the future, specify column names, and type again.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL dblink” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
