Postgresql, syntax error while trying to insert into remote table via dblink

Question

I have created trigger function that will try to insert data into remote database table like this:

db_res := dblink_exec('dbname=test user=test password=test host=localhost port=5432', 'INSERT INTO test (table, action) VALUES (''table'',''action'')');

When this trigger launches, it throws me this error:

syntax error (at or near: ",")

Why this is happening? How simple comma can cause this error?

Answer 1

table is a reserved word in SQL, so using it as a column name will cause this syntax error. If the column is really called table, you have to refer to it using the quoted identifier "table".

In order to avoid problems like this, it is highly commendable to not name objects with reserved keywords. You can find a list of those keywords in appendix C of the documentation.