How to replace double quotes to single quotes when use Sequelize to do raw query to Postgresql

Question

I use Sequelize.js and follow this post to update primary key value.
I run:

await sequelize.query(`SELECT SETVAL('${idSeqence}', max(id)) FROM "${tableName}";`)

but error message says:

error: relation "my_bottemplates_id_seq" does not exist
sql: `SELECT SETVAL('my_botTemplates_id_seq', max(id)) FROM "myBotTemplates";

Then I follow this post to try to replace double quotes
First try:

await sequelize.query(`SELECT SETVAL(trim(both '"' from '"${idSeqence}"'), max(id)) FROM "${tableName}";`)

but I got the same error message again:

error: relation "my_bottemplates_id_seq" does not exist
sql: `SELECT SETVAL(trim(both '"' from '"my_botTemplates_id_seq"'), max(id)) FROM "myBotTemplates";`

Second try:

await sequelize.query(`SELECT SETVAL(replace('${idSeqence}', ", '), max(id)) FROM "${tableName}";`)

This error message says:

error: syntax error at or near "myBotTemplates"
sql: `SELECT SETVAL(replace('my_botTemplates_id_seq', ", '), max(id)) FROM "myBotTemplates";`

Is there any method to run the SETVAL to update primary key value successfully?

You need to omit double quote, instead of `"${tableName}"`, try `${tableName}` that is without quote — Arif Khan, Feb 10 '23 at 03:33
@ArifKhan If I use `${tableName}`, error says: `error: relation "mybottemplates" does not exist`, but my table name is myBotTemplates — jadokao, Feb 10 '23 at 03:52

score 0 · Accepted Answer · answered Feb 10 '23 at 14:14

Since the sequence contains upper case letters, it must be doublequoted in addition to the single quotes:

SELECT SETVAL('"my_botTemplates_id_seq"', max(id)) FROM "myBotTemplates";

or

await sequelize.query(`SELECT SETVAL('"${idSeqence}"', max(id)) FROM "${tableName}";`)

How to replace double quotes to single quotes when use Sequelize to do raw query to Postgresql

1 Answers1