EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL dblink

PostgreSQL dblink

Updated May 24, 2023

PostgreSQL dblink

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.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

PostgreSQL dblink 1

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:

Postgres database

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 –

create dblink extension

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 –

create database extension

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.

  1. PostgreSQL NOT IN
  2. PostgreSQL SET
  3. Integer in PostgreSQL
  4. PostgreSQL Query Optimization
ADVERTISEMENT
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW