CODEWITHSUNDEEP
node.jspostgresqlknex.js
dknaack
dknaack

Reputation: 60468

PostgreSQL / NodeJS: Query Result Empty

I have a query which returns 0 rows but executing the same query using pgadmin or dbeaver returns a result set with rows.

Ive noticed this because i have a postgresql function which should return rows but didnt. After that i started debugging.

Other queries are not affected. I tried it using knexjs (knex.raw()) and pg (client.query()).

Off cause, i checked the connection a dozen times using different queries and reading the connection string.

This is really strange.

The whole point here is, why does this work in dbeaver and not in my code. Is this a drivers thing?

Queries

select id from (
    select id, started_at from queue 
    where finished_at is null and started_at is not null order by id
) d 
where date_part('minute',age(now(), started_at)) >= 5

I played around a lot and found that the following queries do work. 

select id from queue 
where date_part('minute',age(now(), started_at)) >= 5;

and

select id from (
    select id, started_at from queue 
    where finished_at is null and started_at is not null order by id
) d;

Update

not working

const test = await this.knexInstance.raw(`
    select id from (
        select id, started_at from queue 
        where finished_at is null and started_at is not null order by id
    ) d 
    where date_part('minute',age(now(), started_at)) >= 5
`);
console.log(test.rows); // => []
console.log(test.rows.length); // => 0

working

const test = await this.knexInstance.raw(`
    select id from queue 
    where date_part('minute',age(now(), started_at)) >= 5;
`);
console.log(test.rows); // => Array(48083) [Object, Object, Object, Object, Object, Object, Object, Object, …]
console.log(test.rows.length); // => 48083

Upvotes: 0

Views: 2241

Answers (2)

coockoo
coockoo

Reputation: 2382

Okay, I tried to reproduce this, but got result as expected. I'm using [email protected].

const knex = require('knex')(config)

async function main () {
    await knex.raw('create table queue ( id bigserial primary key, started_at timestamp with time zone not null default current_timestamp, finished_at timestamp with time zone);')
    await knex('queue').insert({ started_at: knex.raw('now() - \'10 minutes\'::interval'), finished_at: null })
    await knex('queue').insert({ started_at: knex.raw('now() - \'11 minutes\'::interval'), finished_at: null })
    await knex('queue').insert({ started_at: knex.raw('now() - \'12 minutes\'::interval'), finished_at: null })
    await knex('queue').insert({ started_at: knex.raw('now() - \'13 minutes\'::interval'), finished_at: null })

    await knex('queue').insert({ started_at: knex.raw('now() - \'4 minutes\'::interval'), finished_at: null })
    const test = await knex.raw(`
    select id from (
        select id, started_at from queue
        where finished_at is null and started_at is not null order by id
    ) d
    where date_part('minute',age(now(), started_at)) >= 5
`);
    console.log(test.rows) // Array(4)
    console.log(test.rows.length) // => 4 
    await knex.raw('drop table queue;') 
    await knex.destroy()
}

main()

All I can recommend is to try to run this example locally and watch the results. And try to upgrade knex to the latest version (if it's not).

Upvotes: 1

Fazal Rasel
Fazal Rasel

Reputation: 4526

Why directly using raw?

const test = await this.knexInstance.select('queue')
             .columns(['id'])
             .whereRaw('date_part('minute',age(now(), started_at)) >= 5');

I'm have less idea about PostgreSQL but it should work.

Upvotes: 1

Related Questions