How to look for a specific thing within a table with postgres/nodejs

Question

I'm currently using postgres, accessing it through a node server.

I'm feeling stuck on how I would search for a specific term within a table in order to make a change. I know I can SELECT certain items, but I don't know how to search within the items.

say my table is:

animal	cuddly	scary
Medium Dog	yes	no
Small Dog	yes	no
Fluffy Dog	yes	no
Big Dog	yes	yes
Big Cat	no	yes
Small Fish	no	no

Suddenly, I get bit by a dog, and now I want to change everything containing 'dog|Dog' to scary: yes.

I can't really find any good resources to create the function. Perhaps I've overlooked something.

Right now I'm using simple SELECT's like this:

app.get('/update/:type', (req, res) => {
  pool.query("SELECT animal FROM petlist WHERE scary = 'no' AND animal = $1",[req.params.client],
   (error, results) => {
    if (error) {
      console.log("I selected dog, but I can't see the specific types of dog!")
      throw error
      }
    console.log(results.rows);
    res.status(200).json(results.rows)
  })
});

Thanks for the edit Henry , apologies for putting it in a snippet box! — SGPascoe
– SGPascoe, Commented Nov 24, 2021 at 19:54

Belayer · Accepted Answer · 2021-11-24 23:06:34Z

2

I do not know your obscurification language (nodejs) but the Postgres predicate you are looking for would be either ilike or case conversion of the column animal. So

select animal 
  from petlist 
 where scary = 'no' 
   and animal ilike '%dog'; 

-- or if nodejs complains about ilike then 
select animal 
  from petlist 
 where scary = 'no' 
   and lower(animal) like '%dog';

so perhaps something like:

app.get('/update/:type', (req, res) => {
  pool.query("SELECT animal FROM petlist WHERE scary = 'no' AND animal ILIKE $1,[req.params.client],
   (error, results) => {
    if (error) {
      console.log("I selected dog, but I can't see the specific types of dog!")
      throw error
      }
    console.log(results.rows);
    res.status(200).json(results.rows)
  })
});

with req.params.client is set to the string %dog.

answered Nov 24, 2021 at 23:06

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

1 Comment

SGPascoe Over a year ago

This is fantastic! Thanks so much, I didn't know about ILIKE. It was a bit of a pain to get nodejs to pass the correct term with the apostrophes and percentage marks, but I got there eventually and it works perfectly. Thank you again!

Collectives™ on Stack Overflow

How to look for a specific thing within a table with postgres/nodejs

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related