Missing something in connection info String using dblink

Question

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

Answer 1

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.