CODEWITHSUNDEEP
postgresql
Nasia Ntalla
Nasia Ntalla

Reputation: 1789

ERROR: function coalerse(bigint, integer) does not exist

I have this query, where I want to return zero values instead of null ones. 

    create view ct as
    select userid, coalerse(count(tweets), 0) as nooftweets, coalerse(count(distinct mention), 0) as mention 
from (
    select t.user_id as userid, t.id as tweets, m.mentionedusers_id as mention, row_number() over (partition by m.tweet_id order by m.mentionedusers_id
      ) rn 
    from "tweet_mentUsers" m right join tweet t on m.tweet_id = t.id where text like '@%') a where rn <= 2 group by 1

However I get this error message:

ERROR:  function coalerse(bigint, integer) does not exist
LINE 2: select userid, coalerse(nooftweets, 0), coalerse(mention, 0)...
                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Do you have any idea?

Upvotes: 1

Views: 9264

Answers (1)

messanjah
messanjah

Reputation: 9278

I think the COALESCE function will do what you want.

create view ct as
select userid, coalesce(count(tweets), 0) as nooftweets, coalesce(count(distinct mention), 0) as mention 
from (
select t.user_id as userid, t.id as tweets, m.mentionedusers_id as mention, row_number() over (partition by m.tweet_id order by m.mentionedusers_id
  ) rn 
from "tweet_mentUsers" m right join tweet t on m.tweet_id = t.id where text like '@%') a where rn <= 2 group by 1

Upvotes: 6

Related Questions