knex: Conflict with Postgresql jsonb operators
Hi!
I have sql:
SELECT id, name, price, files->'photos'->0->>'name' as photo, annotation
FROM product
WHERE tags ?& array['17']
ORDER BY (tags->>'17')::int
LIMIT 10
If i run this sql direct in postgresql client it work fine
If i run this sql through node-postgres it work fine again:
var pg = require('pg');
var connPg = "postgres://user:password@localhost/mydbname";
var clientPg = new pg.Client(connPg);
var sqlProductsByTags = [
"SELECT id, name, price, files->'photos'->0->>'name' as photo, annotation ",
"FROM product ",
"WHERE tags ?& array['17'] ",
"ORDER BY (tags->>'17')::int ",
"LIMIT 10 "
];
clientPg.connect(function(err) {
if(err) {
return console.error('could not connect to postgres', err);
}
//console.log('SQL = ' + sqlProductsByTags.join(''));
clientPg.query(sqlProductsByTags.join(''), function(err, result) {
if(err) {
return console.error('error select products by tags', err);
}
for(var i = 0, max = result.rows.length; i < max; i++) {
console.log('name: ' + result.rows[i].name);
}
clientPg.end();
});
});
If i run raw sql by knex, it fail with error (below listed):
var connPg = "postgres://user:password@localhost/mydbname";
var pg = require('knex')({
client: 'pg',
connection: connPg
});
var sqlProductsByTags = [
"SELECT id, name, price, files->'photos'->0->>'name' as photo, annotation ",
"FROM product ",
"WHERE tags ?& array['17'] ",
"ORDER BY (tags->>'17')::int ",
"LIMIT 10 "
];
pg.raw(sqlProductsByTags.join(''))
This Error
{ [error: syntax error (at or near "$1")]
name: 'error',
length: 142,
severity: 'ERROR',
code: '42601',
detail: undefined,
hint: undefined,
position: '98',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
file: 'scan.l',
line: '1053',
routine: 'scanner_yyerror' }
I think that it fail because of i used jsonb operator ‘?&’, precise only one symbol ‘?’ Reference to PostgreSQL JSONB operators
About this issue
- Original URL
- State: closed
- Created 10 years ago
- Comments: 33 (19 by maintainers)
Commits related to this issue
- Fix for #519 — committed to knex/knex by tgriesser 10 years ago
+1 for
??@SeanCannon, in my codebase I saw this working just fine over a year ago after it was fixed. Chances are very good that if you’re seeing a similar issue, it is a new problem, not a continuation of this problem.
I suggest you create a new Issue, complete with the version of knex you’re using, example code that demonstrates the problem, and output of the error and/or expected vs actual results.