2

I am converting an existing project from MySQL to Postgres. There are quite a few raw SQL literals in the code that use ? as a placeholder, e.g.

  SELECT
    id
  FROM
    users
  WHERE
    name = ?

But I get this error:

DB query error: error: operator does not exist: character varying = ?

I don't want to convert all my existing SQL from ? to postgres-style operators like $1.

Is there some way of having node-postgres accept the question marks instead, or an utility that can convert to postgres style params?

Note that some sort of Regex-based hack is not acceptable because question marks can be inside quotes, or backslash escaped to any depth.

Improve this question
4
  • 1
    Using a ? in a query is not MySQL, but rather the app layer, e.g. Java. This should not need to be changed most likely. Can you show us this query in the context of the code? Commented Aug 1, 2016 at 11:28
  • What version of Postgres are you using? Because the documentation mentions using ? (postgresql.org/docs/9.1/static/ecpg-commands.html) as well as this random SO question (stackoverflow.com/questions/10659737/…) Commented Aug 1, 2016 at 11:33
  • 1
    What is node-postgres, and why is it not in the tags, as it appears to be the core of your question? Commented Aug 1, 2016 at 11:45
  • @joop It looks like this is a node.js interface to postgres. Which makes my comment irrelevant. Commented Aug 1, 2016 at 12:02

2 Answers 2

Reset to default
2

Is there some way of having node-postgres accept the question marks instead?

NO. And there is no direct correspondence between ? and $1 syntax, because the latter implies parameter re-use, while ? doesn't allow it. For example, using ? ? ? implies that you have 3 formatting parameters, while $1 $2 $2 implies that you have two formatting parameters.

or an utility that can convert to postgres style params?

Not likely, since there is no direct correspondence, the conversion is possible only one-way, which would make such an utility fairly useless. You can replace everything yourself, with a single regular expression, replacing each ? with $ + index + 1.

I don't want to convert all my existing SQL from ? to postgres-style operators like $1.

You don't really have much choice in this. It has to be done. Besides, $1 is way more flexible than ?, due to parameter re-use, plus optional extensions. For example, pg-promise extends them very nicely, with various formatting modifiers that are needed frequently: ^, ~, :json, :csv, etc...

Note that some sort of Regex-based hack is not acceptable because question marks can be inside quotes, or backslash escaped to any depth.

You will likely spend less time converting your SQL by hand, than the time to write an utility for the one-way proper conversion.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Actually converting ? to $1, $2 etc will always work. Converting $1, $2 back to ? won't work if you've repeated parameters.

It would be valuable for those porting software from MySQL and others if PG supported the ? parameter stub.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.