How to use SQL LIKE condition with multiple values in PostgreSQL?
SIMILAR TO would work ?
SELECT * from table WHERE column SIMILAR TO '(AAA|BBB|CCC)%';
SQL - Combining multiple like queries
You can use
SIMILAR TO and separate the tags with | pipe
WHERE number SIMILAR TO '555123%|555321%|555987%'
SQL select items grouped by multiple values
The typical approach here is to aggregate the rows per instance and use conditional aggregation in the
HAVING clause to get only those instances that match your criteria:
group by instance
having count(*) filter (where type = 'A') > 0
and count(*) filter (where type = 'B') > 0
order by instance;
What I hadn't thought of myself is what Isolated suggests in the request comments: Use
INTERSECT, which leads to this very simple query:
select instance from mytable where type = 'A'
select instance from mytable where type = 'B'
order by instance;
I like both approaches just the same here. My first approach is more versatile, though, as you can easily have various conditions in that query without changing it much. For example if you wanted to limit this to those instances that have types A and B and no other type. You'd just add a condition that the type count must be two or that the count for types other than A and B must be zero.
SQL query that leverages the postgres ILIKE function on multiple values from a table field given a list of IDS
I think something like this should work:
WHERE model_id IN (SELECT id
WHERE name ILIKE ANY (SELECT name||'%'
WHERE id IN (<array of model IDs>)
Disclosure: I am an EnterpriseDB (EDB) employee
SQL: Multiple Column in a Table has same a condition: WHERE COL1 LIKE 'VALUE' OR COL2 LIKE 'VALUE'
To answer your direct question, you could concatenate the columns and use an array if there's multiple values you want to search for.
create table t1 (
insert into t1 (col1,col2,col3)
('foobar', 'bar', 'alpaca')
WHERE (col1 ||' '|| col2 ||' '|| col3) ILIKE ANY(ARRAY['%foo%','%bar%', '%cat%'])
Unsure how performant this would be, but I believe it answers your direct question and is probably more readable than a long list of
Postgresql - multiple select condition
You may try the following:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY "type" ORDER BY updated_at DESC) rn
SELECT id, author_id, name, "type", created_at, updated_at
("type" IN ('A', 'B') AND rn = 1) OR
"type" NOT IN ('A', 'B');
This approach uses
ROW_NUMBER to find the latest rows for all types. In the query on the CTE, we select only the most recently updated rows for types
B, but we select all rows for all other types.
Postgres array lookup multiple values in where clause
You can use the overlap operator
&& for arrays:
select * from stud
where array[8,9] && subject_id
How to use ILIKE for multiple values in single column in rails?
Project.where("name ILIKE ANY (array[?])", ["%Arvind Oasis%", "%Prestige Jindal City%", "%XXXX%"])