I have a table with two columns name and value.

There are many rows with the same name, but with different values.

I want to create a function that returns a table with 2 columns. The first column is the name and the second is a character, either yes or no, which refers to whether a name has a specific value or not.

If a name has the specific value at least at one of its values to return yes and if it does not have the specific value to return no.

With the answers so far, I return the same name many times with the corresponding values like

('BOB'-'NO','BOB'-'NO','BOB'-'NO','BOB'-'NO','BOB'-'YES','JAME'-'NO','JAME'-'NO','JAME'-'NO','JAME'-'NO','JAME'-'NO') for example.

I want to return a table like that:


because bob has a specific value that I want at least one time at his values and JAME has not the specific value in a row.

  • Can you post a minimal example? Apr 27, 2018 at 16:15
  • Table definition, sample values, desired result, Postgres version would make this more instructive. Apr 28, 2018 at 3:45

2 Answers 2


Here are three simple methods to do it,

  1. Returning bool: Just use a simple CASE statement.

    SELECT name, CASE WHEN value = 'foo' THEN true ELSE false END
    FROM table;
  2. Returning bool compact: You can actually make it more terse, so long as value is NOT NULL. That CASE statement is essentially equal to

    SELECT name, value = 'foo'
    FROM table;

    If value is ever null though you'll get null rather than false.

  3. Returning text: If you insist on yes and no instead (I wouldn't do that), you can use strings instead of the special-made boolean type (there is no more compact syntax to write this).

    SELECT name, CASE WHEN value = 'foo' THEN 'yes' ELSE 'no' END
    FROM table;

If a name has the specific value at least at one of its values to return yes and if it does not have the specific value to return no.

You left room for interpretation, but this seems to be what you are after:

SELECT name, bool_or(value = 'specific_value') AS has_specific_value
FROM   tbl
GROUP  BY name;

The manual:

bool_or(expression) ... true if at least one input value is true, otherwise false.

You get one row per distinct name and a flag indicating whether the name has the given value in at least one of its rows.

You might want to add count(*) to also get the number of rows per name.
count(*) FILTER (WHERE value = 'specific_value') to get the count of matching rows would only make sense where there can be multiple matches, i.e. no UNIQUE constraint on (name, value) or similar - "but with different values" seems to indicate as much.
And maybe append ORDER BY ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.