Postgres syntax error for parameterized ALTER TYPE statement

[amkhrjee]

I have a tags array of strings.

I am looping through the array to add to the enum type tags that is defined in my database.

Here is the relevant code:

tags.forEach(async (tag) => {
  if (!existingEnums.includes(tag)) {
    try {
      await sql`
	ALTER TYPE tags ADD VALUE ${tag}
      `;
    } catch (err) {
      console.error(`Error adding ${tag}: ${err}`);
    }
  }
});

This always ends up emitting the following error, for each array item:

PostgresError: syntax error at or near "$1"

When I run the query with ALTER TYPE tags ADD VALUE 'actual_name_of_the_tag' - it works. But otherwise, when used with the variable, it doesn't. The library is doing fine with other INSERT and SELECT queries.

What am I doing wrong? And how do I fix it?

[amkhrjee]

Some Wiresharking reveals the SQL query postgres.js sends over to the Postgres instance.

Apparently, there are no follow-up packets sending the parameter values. The next packet recorded contains the error message from the Postgres instance.

This is weird because SELECT and INSERT statements do send Parameter description packets following the parameterized query.

[boromisp]

I'm not sure, if there is more specific documentation somewhere, but based on this: https://www.postgresql.org/docs/current/sql-prepare.html you probably can't use parameter for the value in ALTERT TYPE queries.

The usual workarounds for similar issues are:

• some flavor of stored procedure that uses format() and EXECUTE, that you can call as

sql`SELECT add_new_tag(${value})`

• build / escape you query in javascript, and use sql.unsafe() to execute it

[porsager]

You can't use protocol level parameters for queries like that. You need to use sql.unsafe, so something like this perhaps

await sql`
  ALTER TYPE tags ADD VALUE ${ sql.unsafe(`'${ tag.replace(/[^a-z]/g, '') }'`) }
`

If you're sure that tag is properly sanitized and not a way for sql injection to sneak in you can leave out the replace..