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
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).