PostgreSQL / NodeJS: Query Result Empty

Question

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

It looks your code returns response early, whereas the actual response from Database comes late. May be you need to put callback function in there, that execute after the result returned from DB. — Nah
– Nah, Commented Mar 15, 2018 at 9:32
Try to write your query without nesting. Does it work that way? — coockoo
– coockoo, Commented Mar 16, 2018 at 6:27

coockoo · Accepted Answer · 2018-03-16 09:17:45Z

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).

Fazal Rasel · Accepted Answer · 2018-03-15 19:02:55Z

1

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.

answered Mar 15, 2018 at 19:02

Fazal Rasel

4,5262 gold badges23 silver badges32 bronze badges

Collectives™ on Stack Overflow

PostgreSQL / NodeJS: Query Result Empty

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

Queries

Update

not working

working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

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

Queries

Update

not working

working

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related