Error when I try to INSERT INTO

[FlorianBfr]

Hello,
I'm using your node-red node, thank for this great job, but when I'm trying to INSERT data I've got an error :
message: "error: cannot insert multiple commands into a prepared statement"
My Node-red version is 3.0.2 and run under Docker; the node-red-conrib-postgresql is 0.12.0; and the database is Potgresql 15 install in Ubuntu server 22 which run on Promox as a virtual Machine.
So I've tried the mustache template but I obtain a syntax error that I can't solve.
The query is Ok under Pgadmin 4.
Here is my flow in mustache template :

Here is the queries in mustache template :
SET search_path TO "owntrack";
INSERT INTO "owntracks.event" (geom, battery, velocity, altitude, course, device, event_datetime)
VALUES (ST_MakePoint({{{payload.lat}}}, {{{payload.lon}}}), {{{payload.battery}}}, {{{payload.velocity}}}, {{{payload.altitude}}}, {{{payload.course}}}, {{{payload.device}}}, {{{payload.event_datetime}}});
Here is the result in the debug window :

Here is my query with msg.params:
INSERT INTO "owntracks.event" (geom, battery, velocity, altitude, course, device, event_datetime) values (ST_MakePoint($1, $2), $3, $4, $5, $6, $7, $8);
Here is the flow with the msg.params:

This is my first time with Postgresql, may I've don't understand something ? If you have any idea...

[Alkarex]

Bonjour,
You are probably missing some single quotes around some of your fields, in particular for your device and datetime fields, which are strings.

INSERT INTO "owntracks.event" (geom, battery, velocity, altitude, course, device, event_datetime)
VALUES (ST_MakePoint({{{msg.payload.lat}}}, {{{msg.payload.lon}}}), {{{msg.payload.battery}}}, {{{msg.payload.velocity}}}, {{{msg.payload.altitude}}}, {{{msg.payload.course}}}, '{{{msg.payload.device}}}', '{{{msg.payload.event_datetime}}}');

Check your PostgreSQL logs, which should have more details about the wrong request received.

In general for debugging, I suggest you try your query by hand directly in psql or pgAdmin.

[FlorianBfr]

Hi
Thank for your answer. As you tel me I have checked the log of postrgesql and see that all the data are empty ! There is a trouble in my flow between the Convert MQTT payload function and the postgresql.
Here is my function

[Alkarex]

In addition to missing the single quotes around your strings, it looks like you did not refer to the right object.
In your case, I believe it should be something like '{{{msg.payload.device}}}'
Check the doc https://github.com/alexandrainst/node-red-contrib-postgresql/blob/main/README.md

When in doubt, you can also skip completely the templating and generate the full query completely by hand, referring to it via {{{ msg.payload }}}

[FlorianBfr]

Yes it's what I'm doing. With this syntax '{{{msg.payload.device}}}' I have the values for all. But now I have a new trouble, I'm not sure you can help me, the two values lat and lon are any but the ST_MakePoint need float. You may be know how to convert ?
Instead of, I really thank you for your answer.

[FlorianBfr]

Hello,
every things ok but....
I'd like to insert timestamp in my database, this the last value of my query, but I don't know why the format is changed as we can see in this capture:

Why the query convert the time in GMT ?

[Alkarex]

This looks like because you are likely mislead by your debug output. You are sending a JavaScript DateTime object while you should be sending a string.

[Alkarex]

Moved to #43 as this is a new question