# Node-RED Postgres @open-kappa/node-red-contrib-mydb

**URL:** <https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001>\
**Category:** General\
**Created:** [8 June 2020 15:55 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001 "2020-06-08T15:55:20Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![79cj7jeep](https://avatars.discourse-cdn.com/v4/letter/7/e95f7d/32.png) [@79cj7jeep](https://discourse.nodered.org/u/79cj7jeep)\
**Post date:** [8 June 2020 15:55 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/1 "2020-06-08T15:55:20Z")

</div>

@open-kappa/node-red-contrib-mydb

Judging by the number of people who download this, I'm certain I'm just missing something basic in my attempt to use it.

I have a simple postgres database and table set up.

I have 5 columns.

```auto
AccessToken: (TEXT)
SecondsPassed: (SMALLINT)
RoomOccupied: (BOOLEAN)
RoomLightsOn: (BOOLEAN)
RoomAVSystemActive: (BOOLEAN)

```

When I try a simple insert command where I: manually assign the values, it works without issue:

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES (100, "TRUE");

```

The record shows up in the DB, and no errors, etc.

Now, I have a msg object that contains a payload:

```auto
msg.payload : Object
object
AccessToken: "1234ABCD5678EFGH"
SecondsPassed: 21
RoomOccupied: "FALSE"
RoomLightsOn: "FALSE"
RoomAVSystemActive: "TRUE"

```

When I try to use the data from the msg.payload object, I get errors.

The query looks like this:

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, {{msg.payload.RoomOccupied}});

```

The error states:

```auto
msg : error
"error: syntax error at or near ",""

```

Which is not exceptionally useful, but it does seem to relate to the comma that is separating the two values contained in the {{}}.

I have tried substituing a static value for each of the msg.payload objects above, ie:

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES (100, {{msg.payload.RoomOccupied}});

```

and the error message changes to:

```auto
msg : error
"error: syntax error at or near ")""

```

or the other value:

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, TRUE);

```

and the error reverts back to:

```auto
msg : error
"error: syntax error at or near ",""

```

I'm at a loss. For my purposes, it seems this particular DB connector would work very well for the simple context I'm looking for, but for the life of me, I cannot get it to work unless I use static values.

Am I missing something with the {{}} syntax or something? I've tried single {} and triple {{{}}}, and while the error message may change, it never produces a positive result.

Would really appreciate any help.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [8 June 2020 19:27 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/2 "2020-06-08T19:27:50Z")

</div>

Have you added a debug node to the output of the function or template you are using to create that, in order to check exactly what query you are passing into the node ?  
I've not used postgres in a while. How does it require the booleans to be converted/submitted exactly ?

---

<div class="post-metadata">

**Author:** ![79cj7jeep](https://avatars.discourse-cdn.com/v4/letter/7/e95f7d/32.png) [@79cj7jeep](https://discourse.nodered.org/u/79cj7jeep)\
**Post date:** [9 June 2020 12:02 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/3 "2020-06-09T12:02:54Z")

</div>

I do have a debug node on the output of the mydb node that is sending the query to postgres, but it just produces the error messages I posted such as:

```auto
msg : error
"error: syntax error at or near ",""

```

As far as the formatting goes, I'm not sure specifically what formatting issue there may be, but when I enter a query with static values, as opposed to trying to retrieve the data from the msg object, the postgres query works properly.

I appreciate the ideas, but still at a loss. Ideally, I'd like a more verbose error response so I can drill down what exactly the query being sent looks like, but I do not see any way of doing this with the node-red-contrib-mydb node.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [9 June 2020 12:11 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/4 "2020-06-09T12:11:01Z")

</div>

No We need to see the debug from the input side of the db node

---

<div class="post-metadata">

**Author:** ![79cj7jeep](https://avatars.discourse-cdn.com/v4/letter/7/e95f7d/32.png) [@79cj7jeep](https://discourse.nodered.org/u/79cj7jeep)\
**Post date:** [9 June 2020 12:19 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/5 "2020-06-09T12:19:47Z")

</div>

Sorry, I misunderstood.

Here is the debug node for the msg.payload being used by the db node:

```auto
6/9/2020, 8:17:27 AM
msg.payload : Object

{"AccessToken":"ABCD1234EFGH5678",
"SecondsPassed":301,
"RoomOccupied":"FALSE",
"RoomLightsOn":"FALSE",
"RoomAVSystemActive":"FALSE"}

```

 ![Image 57](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/0/109b6bcc192ea8beeedc872e9e608854e6d48764.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 June 2020 12:48 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/6 "2020-06-09T12:48:34Z")

</div>

> [@79cj7jeep](#):
>
> `VALUES (100, "TRUE");`

I note that you have quotes round TRUE

> [@79cj7jeep](#):
>
> `VALUES ({{msg.payload.SecondsPassed}}, {{msg.payload.RoomOccupied}});`

here you have not got quotes, that will expand to  
`VALUES( 21,FALSE);`

---

<div class="post-metadata">

**Author:** ![FStefanni](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/fstefanni/32/23624_2.png) [@FStefanni](https://discourse.nodered.org/u/FStefanni)\
**Post date:** [9 June 2020 13:20 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/7 "2020-06-09T13:20:54Z")

</div>

Hi,

even if we use this node in production at work, I have re-checked just to be sure, and I confirm that it works fine. It is indeed an issue about how you write the query, so in general I suggest to write and test it in plain SQL, e.g. by using pgAdmin.

The problem seems exactly what Colin has written: the missing quotes. For example, I have created this message:

```auto
return {
    "payload": {
        "value1": "Hi!"
    }
};

```

The template inside mydb node is:

```auto
INSERT INTO "testTable" ("value1", "value2")
VALUES ('{{msg.payload.value1}}', 'TRUE');

```

where my testTable has two columns of type text,  
and it works fine.

Please also note: in your insert you write:

```auto
INSERT INTO "MYDATABASE"."test"

```

But "MYDATABASE" should be the schema name, written with **single quotes** , and "test" the table name. In my example, I have used the default schema (namely "public"), so I can avoid to specify it.

I hope this helps.

---

<div class="post-metadata">

**Author:** ![79cj7jeep](https://avatars.discourse-cdn.com/v4/letter/7/e95f7d/32.png) [@79cj7jeep](https://discourse.nodered.org/u/79cj7jeep)\
**Post date:** [1 July 2020 15:32 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/8 "2020-07-01T15:32:43Z")

</div>

Appreciate the advice on this, and I have double checked this. In fact, if I statically use:

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ( 123, TRUE);

```

or If I add quotes around the "TRUE":

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, "TRUE");

```

or if I used single quotes around 'TRUE':

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, 'TRUE');

```

This all works and returns no error.

Now If I use no quotes around the second value:

> INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")  
> VALUES ({{msg.payload.SecondsPassed}}, {{msg.payload.RoomOccupied}});

or if I use double quotes around the the second value:

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, "{{msg.payload.RoomOccupied}}");

```

or if I use single quotes around the second value:

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, '{{msg.payload.RoomOccupied}}');

```

debug shows this error message:

```auto
7/1/2020, 11:14:36 AM[node: DB Insert](http://x.x.x.x:1880/#)
msg : error
"error: syntax error at or near ","

```

Please note, the DB types are SMALLINT (SecondsPassed) and BOOLEAN(RoomOccupied). It seems the boolean is the issue for some reason.. I cannot get the boolean value to pass, quoted with single or double quotes, or without quotes.

Again, if I send:

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, TRUE);

```

It works just fine.

If I send:

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, "TRUE");

```

it works just fine.

If I send:

```auto
INSERT INTO "MYDATABASE"."test" ("SecondsPassed", "RoomOccupied")
VALUES ({{msg.payload.SecondsPassed}}, 'TRUE');

```

it works just fine.

It's only if I try and resolve the boolean value by means of {{msg.payload.RoomOccupied"}} that it is an issue.

If I debug the msg object for msg.payload.RoomOccupied, I get: "TRUE" as the value.

---

<div class="post-metadata">

**Author:** ![79cj7jeep](https://avatars.discourse-cdn.com/v4/letter/7/e95f7d/32.png) [@79cj7jeep](https://discourse.nodered.org/u/79cj7jeep)\
**Post date:** [1 July 2020 16:54 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/9 "2020-07-01T16:54:35Z")

</div>

I think I found the cause of the issue. In the sample solution shown earlier the key and value pairs in the object are shown as quoted value.

In the JSON object I am receiving from a HTTP POST object, the keys are not quote deliniated.

They appear as:

> [@79cj7jeep](#):
>
> ```auto
> msg.payload : Object
> object
> AccessToken: "1234ABCD5678EFGH"
> SecondsPassed: 21
> RoomOccupied: "FALSE"
> RoomLightsOn: "FALSE"
> RoomAVSystemActive: "TRUE"
> 
> ```

So, when the {{msg.payload.SecondsPassed}} is resolved, it is not working.

Following the previous advice, if I create an object and put the data as such:

> return {  
> "payload": {  
> "SecondsPassed": 200,  
> "RoomOccupied": "TRUE"  
> }  
> };

This works just fine with the previos DB insert code I was using.

Any suggestions on how to deal with this JSON issue? I tried the json node and converted to Javascript object, but same issue persists there as wel;l.

Thanks for all the time and help.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [1 July 2020 18:42 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/10 "2020-07-01T18:42:57Z")

</div>

What node are you using the '{{msg.payload.SecondsPassed}}' in?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [30 August 2020 18:43 UTC](https://discourse.nodered.org/t/node-red-postgres-open-kappa-node-red-contrib-mydb/28001/11 "2020-08-30T18:43:04Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.