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