Delete table records and table in postgresql - issue in table name

[learnobyte]

hi, all i want to delete table and records, i used this query to delete, got error message. pls support. If i use table name instead of msg.payload, the query is working fine , able to delete table and records. what am i missing?

DROP TABLE IF EXISTS ('{{msg.payload}}');
DELETE FROM where tablename='{{msg.payload}}';

[E1cid]

If this query is created in the template node, is the node set to mustache, Otherwise please tell us which node creates this query and post the flow.json of the nodes used.

[learnobyte]

The query is created in postgresql node, not is template

[
{
"id": "8bd521c122332c1e",
"type": "inject",
"z": "8bfb4aab88ac7972",
"name": "",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": true,
"onceDelay": 0.1,
"topic": "",
"payload": "true",
"payloadType": "bool",
"x": 70,
"y": 3160,
"wires": [
[
"12af7b5e1bc34043"
]
]
},
{
"id": "12af7b5e1bc34043",
"type": "function",
"z": "8bfb4aab88ac7972",
"name": "set False",
"func": "msg.enabled = false;\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": ,
"x": 220,
"y": 3160,
"wires": [
[
"fe1e050c6aa877ec"
]
]
},
{
"id": "a50635d8b57dce4f",
"type": "change",
"z": "8bfb4aab88ac7972",
"name": "enabled false",
"rules": [
{
"t": "set",
"p": "enabled",
"pt": "msg",
"to": "false",
"tot": "bool"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 160,
"y": 3240,
"wires": [
[
"fe1e050c6aa877ec"
]
]
},
{
"id": "c88151a71093281e",
"type": "change",
"z": "8bfb4aab88ac7972",
"name": "enabled true",
"rules": [
{
"t": "set",
"p": "enabled",
"pt": "msg",
"to": "true",
"tot": "bool"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 170,
"y": 3340,
"wires": [
[
"fe1e050c6aa877ec"
]
]
},
{
"id": "fe1e050c6aa877ec",
"type": "ui_button",
"z": "8bfb4aab88ac7972",
"name": "",
"group": "2722107c9c033306",
"order": 4,
"width": 0,
"height": 0,
"passthru": false,
"label": "Delete Table",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "tablesdb",
"payloadType": "global",
"topic": "submit",
"topicType": "msg",
"x": 430,
"y": 3220,
"wires": [
[
"4895e1f0bd5dab7c",
"a1bdcf5018d40310"
]
]
},
{
"id": "4895e1f0bd5dab7c",
"type": "template",
"z": "8bfb4aab88ac7972",
"name": "",
"field": "payload",
"fieldType": "msg",
"format": "handlebars",
"syntax": "mustache",
"template": "Delete Table: '{{payload}}' !",
"output": "str",
"x": 660,
"y": 3220,
"wires": [
[
"4b07af975c478e1b"
]
]
},
{
"id": "4b07af975c478e1b",
"type": "ui_toast",
"z": "8bfb4aab88ac7972",
"position": "dialog",
"displayTime": "3",
"highlight": "",
"sendall": true,
"outputs": 1,
"ok": "CANCEL",
"cancel": "OK",
"raw": false,
"className": "",
"topic": "Delete Table?",
"name": "Delete Table?",
"x": 920,
"y": 3220,
"wires": [
[
"f10891a815e4d7f6"
]
]
},
{
"id": "cdc6028ede7f1277",
"type": "ui_toast",
"z": "8bfb4aab88ac7972",
"position": "top right",
"displayTime": "3",
"highlight": "",
"sendall": true,
"outputs": 0,
"ok": "CANCEL",
"cancel": "OK",
"raw": false,
"className": "",
"topic": "Message:",
"name": "Table is not deleted",
"x": 1050,
"y": 3420,
"wires":
},
{
"id": "304beea04bc5f013",
"type": "postgrestor",
"z": "8bfb4aab88ac7972",
"name": "Table name selection",
"query": "DROP TABLE IF EXISTS ('{{msg.payload}}');\n",
"postgresDB": "d39353cd.91093",
"output": true,
"outputs": 1,
"x": 1100,
"y": 3320,
"wires": [
[
"c1fefb3de52e94cf"
]
]
},
{
"id": "ce2e15d1e7a00e26",
"type": "change",
"z": "8bfb4aab88ac7972",
"name": "",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "tablesdb",
"tot": "global"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 800,
"y": 3320,
"wires": [
[
"c0020c464e972e2d",
"304beea04bc5f013"
]
]
},
{
"id": "f10891a815e4d7f6",
"type": "switch",
"z": "8bfb4aab88ac7972",
"name": "",
"property": "payload",
"propertyType": "msg",
"rules": [
{
"t": "eq",
"v": "OK",
"vt": "str"
},
{
"t": "eq",
"v": "CANCEL",
"vt": "str"
}
],
"checkall": "true",
"repair": false,
"outputs": 2,
"x": 550,
"y": 3340,
"wires": [
[
"ce2e15d1e7a00e26",
"f08f57107dade234",
"a50635d8b57dce4f"
],
[
"6cc0d9cf37a11282",
"f08f57107dade234",
"a50635d8b57dce4f"
]
]
},
{
"id": "6cc0d9cf37a11282",
"type": "function",
"z": "8bfb4aab88ac7972",
"name": "",
"func": "msg.payload = 'Table is not Deleted';\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": ,
"x": 840,
"y": 3420,
"wires": [
[
"cdc6028ede7f1277"
]
]
},
{
"id": "f08f57107dade234",
"type": "change",
"z": "8bfb4aab88ac7972",
"name": "",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 740,
"y": 3520,
"wires": [
[
"c17ffae8f161f987"
]
]
},
{
"id": "2722107c9c033306",
"type": "ui_group",
"name": "Delete all records",
"tab": "5e17a74c595a0ca0",
"order": 4,
"disp": true,
"width": "6",
"collapse": false,
"className": ""
},
{
"id": "d39353cd.91093",
"type": "postgresDB",
"name": "postgres@127.0.0.1:5432/OEE",
"host": "127.0.0.1",
"hostFieldType": "str",
"port": "5432",
"portFieldType": "num",
"database": "OEE",
"databaseFieldType": "str",
"ssl": "false",
"sslFieldType": "bool",
"max": "10",
"maxFieldType": "num",
"min": "1",
"minFieldType": "num",
"idle": "1000",
"idleFieldType": "num",
"connectionTimeout": "10000",
"connectionTimeoutFieldType": "num",
"user": "postgres",
"userFieldType": "str",
"password": "",
"passwordFieldType": "str"
},
{
"id": "5e17a74c595a0ca0",
"type": "ui_tab",
"name": "Database settings",
"icon": "dashboard",
"order": 22,
"disabled": false,
"hidden": false
}
]

[learnobyte]

[Steve-Mcl]

In your payload I can see the value table 5

Assuming the postgres node you are using actually support mustache syntax then you should add double quotes around it.

E.g. "{{payload}}"

[Steve-Mcl]

In order to make code readable and usable it is necessary to surround your code with three backticks (also known as a left quote or backquote ```)

``` 
   code goes here 
```

You can edit and correct your post by clicking the pencil icon.

See this post for more details - How to share code or flow json

[learnobyte]

Hi Thanks lot, it is working perfectly , now able to delete the table in database, also records in table, I missed out the double quotes

[learnobyte]

Hi m creating Query editor to create table in database from Node red;

after create , getting msg payload

Postgresql node:

m getting error, if i paste only query in postgresql able to create table, i tried double quotes got syntax error

[Steve-Mcl]

1. Remove the quotes (not necessary here)
2. Try using triple curly brackets to avoid escaping e.g. {{{payload.query}}}

[learnobyte]

yes, it is working perfectly, what is the logic behind this, m not able to understand, any reference ?
Now i can add table from query editor, delete table and records, Thanks lot,