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?

Laurenz Albe · Accepted Answer · 2022-09-29 05:34:38Z

3

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.

answered Sep 29, 2022 at 5:34

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr.D Over a year ago

Damn it! This Postgresql should be saying that this name is reserved instead of throwing obscure messages!

Laurenz Albe Over a year ago

Well, there is nothing fundamentally wrong about using an SQL keyword in an SQL statement. Perhaps it would be possible to do better, but a reserved keyword in an impossible makes the parser get lost in the woods.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related