PostgreSQL Link

Updated May 19, 2023

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 primarily used for executing SQL queries on a remote server. Users commonly utilize the dblink function in conjunction with select queries. 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])

Parameters specified have the following purpose:

• name_of_the connection: It is the name of the connection that you want to provide. In some cases, it is possible to skip providing a name when creating a connection, resulting in 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: The parameter fail_on_error is a boolean with a default value of true when it is skipped or omitted in the specification since it is an optional parameter. If set to true or not specified, an error occurs on your database when an error has occurred on the remote database. However, if the value is set to false, it raises a notice on your side when an error has occurred 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 goes for considering 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. You need to specify the names of the columns you need to retrieve from the remote database’s table. 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 and the data type of the columns that will be retrieved from the 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 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 number of column values retrieved from dblink() does not match the column count specified in the alias of the select query, the system will throw an error indicating that the column count does not match the retrieved results.To avoid the inconvenience of mentioning column names and data types repeatedly during every dblink call, creating a view offers the most convenient solution. 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 the above example, we will create a view named remote_educba_data and later 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 are 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 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 a 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 the educba database command prompt and fire the 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 help retrieve 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 the 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.

We hope that this EDUCBA information on “PostgreSQL Link” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL RANDOM
2. PostgreSQL round
3. DECODE() PostgreSQL
4. PostgreSQL Trunc()