Missing something in connection info String using dblink

Djabone

What I am trying to do is to connect 2 databases and move the data from one db to another via dblink. Here is what I did:

  1. I created a dblink extension
    • 1.1. I created a postgres_fdw extension
  2. I verified if my dblink works (see code)
  3. I testted the connection of the databse
  4. I created a foreign data wrapper
  5. I created a server with connection details 5.1 I mapped the server with the user
  6. I tested the created server (error message - my main issue!)
  7. I gave the required permission to map user
  8. Select query

I have been googling my issue for two days now and could not find anything that may help me.

SELECT dblink_connect('host=localhost user=postgres password=mypw dbname=nameofthedb')

create foreign data wrapper fdw validator postgresql_fdw_validator;

Create Server Ahmed FOREIGN DATA WRAPPER FDW OPTIONS (hostaddr '127.0.0.1',dbname 'nameofthedb');

CREATE USER MAPPING FOR postgres server Ahmed OPTIONS (user 'postgres', password 'mypw');

select dblink_connect('Ahmed') -- NOT WORKING
GRANT USAGE ON FOREIGN SERVER Ahmed TO postgres*/

select * from dblink ('Ahmed', 'select id from nameofthedb.public.pois') --not working AS DATA(id INTEGER);

Here is the error I get:

ERROR: could not establish connection

DETAIL: missing "=" after "Ahmed" in connection info string

SQL state: 08001

Laurenz Albe

This is a case sensitivity problem.

SQL is case insensitive, so PostgreSQL converts all SQL except string constants and identifiers in double quotes to lower case.

As a consequence, the foreign server is really called ahmed.

The first argument in the dblink call is the string literal 'Ahmed', which is not converted to lower case.

So dblink first looks for a named connection Ahmed, doesn't find it, then it looks for a foreign server Ahmed, again doesn't find it and finally resorts to interpreting it as a connection string, which causes the error message.

Solution: Use only lower case names.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Reuse persistent dblink connection

Is there something similar to C# connection string for FTP connection?

how do I create a dblink connection in postgres that I can keep using

PostgreSQL RDS avoid hard coding the connection password when using dblink_connect()

Using connection string in if statement

Not using database connection string

Azure easy table connection string missing

I think I'm missing something here -- string.replace()

Extract info with curl or something

Error Appstore connect : Missing Purpose String in Info.plist File

IOS app missing purpose string in info.plist while it is added

App store Missing Purpose String in Info.plist error

Word Files to Java string : Missing formatting info and showing invalid characters

ITMS-90683: Missing Purpose String in Info.plist

Flutter ios app submission: Missing Purpose String in Info.plist

ITMS-90683: Missing Purpose String in Info.plist NSCameraUsageDescription

Can select using dblink, cannot insert using dblink

How to remove connection string info from ipython-sql output?

"Something's wrong, perhaps a missing \item" error when using itemize

"Something's wrong--perhaps a missing \item" when using longtable

Using Button throwing a NullReferenceException; am I missing something?

Error on Oracle while using DBLINK

insert using dblink in procedure not working

Cross database query using dblink

Pro & Cons using dblink postgresql

Using LIKE to find something inside a string

Caret doparallel in Rmarkdown: Missing verbose info when using render()

How to change the connection info for Laravel 4 when using the redis driver?

Using string variable as the Text connection