CODEWITHSUNDEEP
postgresqljoinpostgresql-9.3dblink
JAN
JAN

Reputation: 21865

DBLINK in Postgres between 2 Databases always gives Syntax error

I have 2 databases and I'm trying to JOIN 2 tables (1 from each)

Both tables have the property userId of type character varying(40) , and the script as follows:

SELECT * FROM public.users tb1
INNER JOIN ((SELECT * FROM dblink('dbname=db_employees','SELECT id FROM employees')) AS tb2(userId character varying(40)))
on tb1.userId = tb2.userId
where "tb1"."userId" = 'gfgfg-43436-htrewd-1111'

Which produces:

ERROR:  syntax error at or near "character"
LINE 2: ...r_users','SELECT id FROM results')) AS tb2(userId character ...
                                                             ^

What's wrong with the script of the JOIN ?

Upvotes: 0

Views: 309

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

You cannot specify a data type in an alias.

If you need to cast the data type, do that in the SELECT list:

(SELECT CAST(userid AS text)
 FROM dblink(
         'dbname=db_employees',
         'SELECT id FROM employees'
      ) AS x(userid)
) AS tb2(userid)

Upvotes: 1

Related Questions