postgresqlinsertspecial-charactersdblinkstringescapeutils

pgSQL: am trying to insert into a table using dblink_exec() method, but getting following error

select dblink_exec('hostaddr=59.89.210.56 port=5432 dbname=mydb user=postgres password=****','insert into gtab04 (productid,product,itgrid,itemtype,patentid,taxid,convfact,boxpack,cntid )values (2301,''D PEARL 2000 Caps 10's'',4,2,7,3,10,'''',0)')

while exe this query am getting the following error

ERROR:  syntax error at or near "s"
LINE 2: ...oxpack,cntid )values (2301,''D PEARL 2000 Caps 10's'',4,2,7,..

the problem is in product's name ie my product name is D PEARL 2000 Caps 10's (i think ' makes the problem) query concats this as ''D PEARL 2000 Caps 10's'' <- here is the problem

Solution

  • Caps 10's should be Caps 10''''s

    You double the single-quotes for dblink once, and for being inside a string the second time.