I am using pg-promise
to execute select query with like clause in PostgreSQL. Unfortunately the query is failing with error code as 08P01
and error message
bind message supplies 1 parameters, but prepared statement "" requires 0
The query is as follows
select user_name, user_id from users where user_name like '$1#%'
I am using Parameterized query as
var userQuery:pgp.ParameterizedQuery = new pgp.ParameterizedQuery("<above_query>", [userName]);
The API used for executing query is
each(query:TQuery, values:any, cb:(row:any, index:number, data:Array<any>)=>void, thisArg?:any):XPromise<Array<any>>;
I looked at the pg-promise examples but it ain't using LIKE clause with Parameterized Query.
Environment Details are
pg-promise: 4.3.2
PostgreSQL: 9.6
Node: 5.7.1
UPDATE: 1
I am able to run the query using query
API (plain text sql) but not with each
. Can anyone please explain why the LIKE clause is failing with each
while using Parameterized
API.
From ParameterizedQuery API:
Only the basic variables (
$1
,$2
, etc) can be used in the query, because Parameterized Queries are formatted by the database server.
Types PreparedStatement and ParameterizedQuery represent the corresponding objects within the node-postgres driver that executes them. Those objects encapsulate both the query and the formatting parameters.
i.e. the whole point of those two objects is to pass both query and the formatting parameters into the server, so they are formatted there, as opposed to using the internal query-formatting engine.
Because of that, you do not have access to the internal query-formatting features of pg-promise, such as $1#
syntax. You can use the pg-promise syntax for query formatting when you are using queries directly, as a query string or as a QueryFile object.
With PreparedStatement and ParameterizedQuery you only have access to the basic $1, $2,...
type of parameter formatting, as supported by the database server, nothing else.
P.S. I am the author of pg-promise.