Find data not registered in postgres

Question

I have field records that have fields with years and months like this:

id  mes  ano
----------
1    1   2005
----------
2    2   2005
----------
3    4   2005
----------
4    5   2006
----------

My doubt is: It is possible to find in the period from 2005 to 2015 all months that have not been registered?

Answer 1

Assuming your table has the following structure:

CREATE TABLE tb_ano_mes
(
    id bigserial,
    ano int,
    mes int,
    CONSTRAINT pk_ano_mes PRIMARY KEY ( id )
);

Containing the following data:

INSERT INTO tb_ano_mes ( id, mes, ano ) VALUES ( 1, 1, 2005 );
INSERT INTO tb_ano_mes ( id, mes, ano ) VALUES ( 2, 2, 2005 );
INSERT INTO tb_ano_mes ( id, mes, ano ) VALUES ( 3, 4, 2005 );
INSERT INTO tb_ano_mes ( id, mes, ano ) VALUES ( 4, 5, 2006 );

Your problem can be solved with the function generate_series(), supported since version 9.1 postgres:

SELECT
     EXTRACT( MONTH FROM s)::int AS mes,
     EXTRACT( YEAR FROM s )::int AS ano
FROM
    generate_series( '01/01/2005', '31/12/2015', '1 month'::interval ) AS s
LEFT JOIN
    tb_ano_mes AS tbl ON ( EXTRACT(MONTH FROM s) = tbl.mes AND EXTRACT(YEAR FROM s) = tbl.ano )
WHERE
    tbl.id IS NULL
ORDER BY 
    ano,
    mes;

I hope I’ve helped!

Answer 2

A small modification in the solution only, the pattern of the date, which should be YYYY-MM-DD and for the most current versions of Postgresql, type conversion should be explicit in most cases:

SELECT

     EXTRACT( MONTH FROM s)::int AS mes,
     EXTRACT( YEAR FROM s )::int AS ano
FROM
    generate_series( '2005-01-01'::date, '2015-12-31'::date, '1 month'::interval ) AS s

LEFT JOIN
    tb_ano_mes AS tbl ON ( EXTRACT(MONTH FROM s) = tbl.mes AND EXTRACT(YEAR FROM s) = tbl.ano )

WHERE
    tbl.id IS NULL
ORDER BY 
    ano,
    mes;