Search code examples
postgresqlpostgresql-8.4

PostgreSQL: return message after count = 0

I have maybe easy question, but I'm completely stucked.

I have script

SELECT COALESCE(COUNT(id), 0) as MyFiels from table

It works fine and when I have zero value it shows 0.

But I want that instead of 0, I can see one line = "NO RESULTS" for example.

I tried:

SELECT COALESCE(to_char(COUNT(id), 'NO RESULT')) as MyFiels from table

And PostgreSQL shows error message:

ERROR: "E" is not supported
SQL state: 0A000

Where I'm incorrect? Any ideas?

Solution

  • I see what is the error, you are trying to use coalesce to convert 0 to string, and coalesce convert null to something. You need use a CASE

    SELECT  CASE WHEN COUNT(*)  = 0 THEN 'NO RESULT'
             ELSE CAST(COUNT(*) as TEXT)
        END as field
FROM Table