Return default value if no record

Question

I have the following sql command of select:

SELECT 
    public.controleimpressoes.codigo, 
    public.impressoras.patrimonio, 
    public.impressoramodelo.modelo,
    public.impressoralocal.descricao,
   (select SUBSTR((to_char(data - interval '1 month', 'TMMonth')),1,3))dataMes,
    diferecacontador, 
    diferecacontadordigital, 
    diferencacontadorcolor,
    public.empresa.nome, 
    datainicial, 
    datafinal
FROM 
    public.controleimpressoes, public.impressoras, public.impressoramodelo,
    public.impressoralocal, public.empresa
where 
    public.controleimpressoes.codigoempresa = public.empresa.codigo and
    public.controleimpressoes.codigoimpressora = public.impressoras.codigo and
    public.impressoras.codigomodelo = public.impressoramodelo.codigo and 
    public.controleimpressoes.codigoimpressoralocal = public.impressoralocal.codigo  and
    data between '2017-01-01' and  '2017-12-31'  and
    public.impressoras.patrimonio between '1000' and '1882' and
    public.empresa.nome between 'A' and 'B' and 
    public.impressoramodelo.modelo between 'C748' and 'C748' 
order by 
    public.empresa.nome, public.impressoras.patrimonio, data

And the same returns to me the following:

It is possible to adjust this query so that when there is no record in the month it is returned to me 0 as standard value?

Something like that:

Where February would have no record.

Answer 1

You can use the function generate_series to generate the numbers from 1 to 12 (months) and from there, search your records. See:

select
    g,
    to_char( to_timestamp(to_char(g, '999'), 'MM'), 'TMMon') as mes, 
    count(r.id) as qtd
from generate_series(1,12) g
left outer join registros r on extract(month from r.data) = g
group by g
order by g

Test Data:

  create table registros
  (
  id int,
  data timestamp
  );

  insert into registros values (1,'2018/01/01');
  insert into registros values (2,'2018/01/01');
  insert into registros values (3,'2018/02/01');
  insert into registros values (4,'2018/02/01');
  insert into registros values (5,'2018/02/01');
  insert into registros values (6,'2018/04/01');
  insert into registros values (7,'2018/05/01');
  insert into registros values (8,'2018/06/01');

Upshot:

I put in the Sqlfiddle

You don’t need the SUBSTR to shorten the month, just change TMMonth for TMMon

---

In the other answer, the COALESCE(NULLIF(seuCampo,'') , '0' ). The two functions do the same thing: COALESCE(seuCampo , '0')

---

The function generate_series also accepted TIMESTAMP as a parameter, which in the case of months, facilitates =]

Reference: https://www.postgresql.org/docs/9.1/static/functions-srf.html

---

Edit:

For your code, I believe it can look like this (I could not test for not having the structure of the tables):

Basically, saves the data you need in a temporary table, then makes a left Join with the function:

There were more requests via chat, and were implemented in the code:

with dados as ( 
SELECT 
    public.controleimpressoes.codigo, 
    public.impressoras.patrimonio, 
    public.impressoramodelo.modelo, 
    public.impressoralocal.descricao, 
    data, 
    diferecacontador, 
    diferecacontadordigital, 
    diferencacontadorcolor, 
    public.empresa.nome, 
    datainicial, 
    datafinal 
FROM 
public.controleimpressoes, public.impressoras, public.impressoramodelo, 
public.impressoralocal, public.empresa 
where 
public.controleimpressoes.codigoempresa = public.empresa.codigo and 
public.controleimpressoes.codigoimpressora = public.impressoras.codigo and 
public.impressoras.codigomodelo = public.impressoramodelo.codigo and 
public.controleimpressoes.codigoimpressoralocal = public.impressoralocal.codigo and 
data between '2017-01-01' and '2017-12-31' and 
public.impressoras.patrimonio between '1000' and '1882' and 
public.empresa.nome between 'A' and 'B' and 
public.impressoramodelo.modelo between 'C748' and 'C748' 
), meses as (

Select distinct
    intervalo,
    d.patrimonio,
    d.modelo,
    d.descricao,
    d.nome
FROM generate_series('2017-01-01'::timestamp, '2017-12-31'::timestamp, '1 Month') as intervalo 
CROSS JOIN dados d)


SELECT DISTINCT
    to_char(intervalo , 'TMMon') dataMes, 
    m.*,
    d.diferecacontador, 
    d.diferecacontadordigital, 
    d.diferencacontadorcolor, 
    d.datainicial,
    d.datafinal
FROM meses m
LEFT OUTER JOIN DADOS d on date_trunc('month',d.data) = m.intervalo and m.patrimonio = d.patrimonio
order by m.nome, m.patrimonio, d.data

Upshot:

Answer 2

If you refer to the column, try to do so:

COALESCE( NULLIF(seuCampo,'') , '0' )

NULLIF compares whether the field passed in the first parameter is equal to empty, and for that it puts 0 in place of the value. You replace these empty quotes with any value, in your case I think it would be '0.0'.