PostgreSQL update query works in SQL IDE and with a plain written boolean value in n8n. When using input data from a prior node or using {{false}} inside the query, it fails with the below stated error.
Other boolean values work in all three ways with same setting in the DB table.
ERROR: syntax error at or near “,”
Hey @SteffenN, I just gave this a quick go on my end but didn’t run into any trouble here when creating a quick test table with a single boolean column.
Could you confirm how the JSON data coming from your Format endTime node looks like and which of the columns in your table has a boolean data type?
Hi @MutedJam,
below the information requested and screenshots of the postgreSQL node.
Output of endTime node:
{
"headers": {
"host": "n8n.*host*.de",
"user-agent": "Vert.x-WebClient/4.2.5",
"content-length": "1246",
"clockify-signature": "XXXX",
"clockify-webhook-event-type": "TIME_ENTRY_UPDATED",
"content-type": "application/json",
"x-forwarded-for": "123.123.123.123",
"x-forwarded-host": "n8n.*host*.de",
"x-forwarded-port": "443",
"x-forwarded-proto": "https",
"x-forwarded-server": "22e83ac5e8a1",
"x-real-ip": "123.123.123.123",
"accept-encoding": "gzip"
},
"params": {},
"query": {},
"body": {
"id": "6270e040736b43623af5xxxx",
"description": "n8n_test",
"userId": "5e635f162fe7db4da05dxxxx",
"billable": true,
"projectId": "5f718cdb2410e76ceb13xxxx",
"timeInterval": {
"start": "2022-05-03T07:00:00Z",
"end": "2022-05-03T08:00:00Z",
"duration": "PT1H"
},
"workspaceId": "5f70bcb52410e76ceb11xxxx",
"isLocked": false,
"hourlyRate": null,
"costRate": null,
"customFieldValues": [],
"currentlyRunning": false,
"project": {
"name": "test",
"clientId": "5f7307f62586c76b9b83xxxx",
"workspaceId": "5f70bcb52410e76ceb11xxxx",
"billable": true,
"estimate": {
"estimate": "PT0S",
"type": "AUTO"
},
"color": "#3F51B5",
"archived": false,
"clientName": "Internal",
"duration": "PT42H8M12S",
"note": "",
"budgetEstimate": null,
"timeEstimate": {
"includeNonBillable": true,
"estimate": 0,
"type": "AUTO",
"resetOption": null
},
"activeEstimate": "TIME",
"id": "5f718cdb2410e76ceb13xxxx",
"public": true,
"template": false
},
"task": {
"name": "task1",
"projectId": "5f718cdb2410e76ceb13xxxx",
"assigneeId": "",
"assigneeIds": [],
"userGroupIds": [],
"estimate": "PT0S",
"status": "ACTIVE",
"workspaceId": "5f70bcb52410e76ceb11xxxx",
"budgetEstimate": 0,
"billable": true,
"hourlyRate": null,
"costRate": null,
"id": "62713f28736b43623afcxxxx",
"duration": "PT1H"
},
"user": {
"id": "5e635f162fe7db4da05dxxxx",
"name": "Steffen Xxxxxx",
"status": "ACTIVE"
},
"tags": []
},
"startTime": "2022-05-03 07:05:00",
"endTime": "2022-05-03 08:05:00"
}
]
Table:
timeentryid varchar,
description varchar,
userid varchar,
endtime timestamp,
starttime timestamp,
duration double precision,
workspaceid varchar,
is_locked boolean,
billable boolean,
projectid varchar,
clientid varchar,
activeuser boolean,
timestamp timestamp,
invoicestatus varchar,
invoice_id varchar,
invoice_number integer,
task_id varchar
Not working:
Working:
Thanks @SteffenN, I could reproduce this with the FALSE value you are sending for your isLocked field. It seems this simply sends an empty value by default when used in an expression like {{$json["body"]["isLocked"]}}:
When using {{$json["body"]["isLocked"].toString()}} however, the update works as expected:
Could you give this a go on your end and confirm if you’re still facing any trouble afterwards?
@MutedJam Thanks a lot! With the conversion it works for me.
Awesome, glad to hear and thanks so much for confirming!
