Postgres CASE with Subquery Multiple Rows

Question

I need to query first name (substring) from a column that has full name. To gather statistics, I need to separate when this fist name have more than one, then, I need to query the next name.

like this:

SELECT 
    CASE
        WHEN (SELECT
            count(substring(nome,0,position(' ' in nome))) as quantidade
            FROM usuarios
            GROUP BY substring(nome,0,position(' ' in nome))
        ) > 1
         THEN substring(t.nome,0,position(' ' in t.nome))||' '||split_part(t.nome,' ',2)
        ELSE substring(t.nome,0,position(' ' in t.nome))
 END AS "Colaborador",

but I can do that as my result is

more than one row returned by a subquery

But yes, it will return more than one row

I don't know how to do it.

Thanks in advance for any help

Answer 1

Use a window function:

SELECT CASE WHEN cnt = 1
            THEN split_part(nome, ' ', 1)
            ELSE split_part(nome, ' ', 1) || ' ' || split_part(nome, ' ', 2)
       END
FROM (SELECT t.nome,
             count(*) OVER (PARTITION BY split_part(t.nome, ' ', 1)) AS cnt
      FROM atable) AS subq;