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

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)
  })
});

Answer

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.