Syntax error at end of input in PostgreSQL

Question

I have used the next SQL statement in both MySQL and PostgreSQL, but it fails in PostgreSQL

db.Query(`SELECT COUNT(*) as N FROM email WHERE address = ?`, email)

with this error:

pq: F:"scan.l" M:"syntax error at end of input" S:"ERROR" C:"42601" P:"50" R:"scanner_yyerror" L:"993"

What's the problem? The error messages in PostgreSQL are very cryptic.

Solution 1

You haven't provided any details about the language/environment, but I'll try a wild guess anyway:

MySQL's prepared statements natively use ? as the parameter placeholder, but PostgreSQL uses $1, $2 etc. Try replacing the ? with $1 and see if it works:

WHERE address = $1

The error messages in PostgreSQL are very cryptic.

In general, I've found that Postgres error messages are better than competing products (ahem, MySQL and especially Oracle), but in this instance you've managed to confuse the parser beyond sanity. :)

Solution 2

In golang for queries we have use

• MySQL uses the ? variant
• PostgreSQL uses an enumerated $1, $2, etc bindvar syntax
• SQLite accepts both ? and $1 syntax
• Oracle uses a :name syntax

Solution 3

You are using Go right?

try:

db.Query(`SELECT COUNT(*) as N FROM email WHERE address = $1`, email)

Solution 4

In my case, it was due to using a -- line comment where the program that was responsible for interacting with the database read in the multiple lines of my query all as one giant line. This meant that the line comment corrupted the remainder of the query. The fix was to use a /* block comment */ instead.

View more solutions

Comments

[Craig Ringer]

Note that this is dependent on the programming language and database access driver used. In JDBC, for example, placeholders are always ?. The idea of having different placeholders based on DB driver is pretty horrid.

[kostix]

@CraigRinger, but forcing each driver to understand a specific placeholder (say, ?) forces the client side to implement full SQL parser matching that of the target DB engine. Moreover, $N potentially allows you to specify one value for several placeholders while ? does not.

[intgr]

@CraigRinger Finding the instances of ? is easy. But it's a hell of a lot harder if you consider that ? is a valid character within strings -- and shouldn't be changed there! Coupled with all the possible different escaping rules, which depend on server-side settings and Postgres versions, along with some pretty complicated parsing cases involving PostgreSQL's $foo$?$foo$ syntax. It gets VERY nasty.

[Admin]

This worked for me too. Way to fill in where the docs were missing.

[Z4-tier]

So the source of the error OP was seeing (I'm assuming he's moved on by now) was incorrect use of syntax for bind variables? It might be good to add a bit more detail to your answer.