Issue while casting jsonb to bigint in deeplink

Question

While casting in same DB working fine, But failing while with deeplink

Working fine in the same DB (Getting result with this query):

SELECT id, 
       total_inventory, 
       hotel_id, 
       ( room_type -> 'id' ) :: bigint AS room_type_id, 
       created_by, 
       created_date, 
       modified_by, 
       modified_date 
FROM   hotel_inventory; 

Not working while connecting with other DB (using deeplink) (Error with this query):

INSERT INTO hotel_inventory 
            ( 
                        id, 
                        total_inventory, 
                        hotel_id, 
                        room_type_id, 
                        created_by, 
                        created_date, 
                        modified_by, 
                        modified_date 
            ) 
SELECT * 
FROM   dblink('demopostgres', 'SELECT id, total_inventory, hotel_id, (room_type -> 'id')::bigint as room_type_id,  created_by, created_date, modified_by, modified_date FROM hotel_inventory') 
AS data(id bigint, total_inventory integer, hotel_id bigint, room_type_id bigint, created_by jsonb, created_date timestamp without time zone, modified_by jsonb, modified_date timestamp without time zone);

Error:

ERROR:  syntax error at or near "id"
LINE 3: ...ECT id, total_inventory, hotel_id, (room_type -> 'id')::bigi...
                                                             ^
SQL state: 42601
Character: 221

Answer 1

You need to double up the single quotes to avoid such an error:

dblink('demopostgres',
       'SELECT . . . (room_type -> ''id'')::bigint as room_type_id . . . '
      )

The issue is a simple parsing error. The single quote ends the string -- hence the error. The double single quote is the standard way of putting a single quote in a string, although different databases often support other methods (such as a backslash).