Avoiding certain strings from a table dynamically

Question

I have a table contains customer details and i need to exclude certain emails from specific domains(identified from email string) from my table. How can i do that?

Tried with below but is not working any idea or suggestions ?

PostgreSQL Version 11

WITH CTE AS
(
select 'gmail.com' as name
union
select 'yahoo.com'
) 
,base as
(
    SELECT 'email ILIKE ''%'||name||'%''OR' as emails FROM CTE
)
,finals as
(
    SELECT string_agg(emails, '  ') as con FROM base
)
SELECT * FROm customers
WHERE  (SELECT con FROM finals )

Error i'm getting,

ERROR:  argument of WHERE must be type boolean, not type text
LINE 16: WHERE  (SELECT con FROM finals )
                ^
SQL state: 42804
Character: 242

Please provide input sample data and expected output. What exactly is not working? — S-Man
– S-Man, Commented Mar 2, 2021 at 8:34
@S-ManI have updated my question and included the error message,Any suggestions here please. — Linu
– Linu, Commented Mar 2, 2021 at 8:38

Laurenz Albe · Accepted Answer · 2021-03-02 08:57:12Z

1

You cannot construct an SQL string and run it in a single SQL statement.

But I think this could be done much simpler:

WITH cte AS (
   SELECT 'gmail.com' AS name
   UNION ALL
   SELECT 'yahoo.com'
) 
SELECT * FROM customers
WHERE email ILIKE ANY (SELECT '%' || name || '%' FROM cte);

answered Mar 2, 2021 at 8:57

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Avoiding certain strings from a table dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related