Knex + Postgresql + node - how to call postgresql functions for selected columns

Question

I want to check record exists or not in postgresql and need result in boolean.

NOTE : I know there are simple ways with if else check but what I want is the representation in knex for the query I listed. I also know this is complex but just wants to learn.

I know the query, You can see here below,

select exists(select 1 from test where id = XYZ);

How can I write this postgresql query with knex?

I tried this way but it is not working.

knex.raw(`SELECT exists(${knex(TABLE_NAME).where(CONDITIONS).select('1').toString()})`)

Mikael Lepistö · Accepted Answer · 2018-04-30 07:34:25Z

3

You don't need to do the boolean conversion in DB side (there won't be any performance difference):

knex('test').select('id').where('id', 'XYZ').then(results => results.length > 0);

this also works if you really prefer to select 1 instead of id:

knex('test').select(knex.raw('1')).where('id', 'XYZ').then(results => results.length > 0);

Tim's answer is also good and gives more exactly the thing you were trying to do, but it is a bit more complicated.

answered Apr 30, 2018 at 7:34

Mikael Lepistö

19.8k4 gold badges73 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Biegeleisen Over a year ago

This is probably the best answer. The code from the GitHub link has to do a lot of dancing to simulate an exists query.

Satyam Koyani Over a year ago

I know this all simple ways but I want to know the way in knex for the listed query.

Mikael Lepistö Over a year ago

@SatyamKoyani I'll edit the question to match the thing you wanted to ask

Tim Biegeleisen · Accepted Answer · 2018-04-30 07:00:58Z

2

I adapted the code below from this useful GitHub Knex post:

var inner = knex('test').where('id', 'XYZ').limit(1);
var clause = knex.raw(inner).wrap('exists (', ') as result');
knex('test').select(clause).limit(1).then(...);

I think this should correspond to the following SQL code:

select exists (select * from test where id = 'XYZ' limit 1) as result
from test
limit 1;

The outer query on test is superfluous, and not needed, but I don't know of a way, other than perhaps a raw query, to not run a query against a table in Knex. The LIMIT statement just means it will return a single value.

answered Apr 30, 2018 at 7:00

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

Knex + Postgresql + node - how to call postgresql functions for selected columns

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related