Listing missing records

From: Ertan Küçükoğlu <ertan(dot)kucukoglu(at)1nar(dot)com(dot)tr>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Listing missing records
Date: 2017-02-19 10:26:36
Message-ID: 000001d28a9a$a6bebb40$f43c31c0$@1nar.com.tr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

Using PostgreSQL 9.6.1 on a Windows 10 64bit, EnterpriseDB binaries.

I have following table:
CREATE TABLE report
(
id uuid NOT NULL,
no smallint NOT NULL,
serial character varying(15) NOT NULL,
branchcode character varying(10) NOT NULL,
date timestamp without time zone NOT NULL,
recordtime timestamp without time zone DEFAULT
date_trunc('second'::text, now()),
CONSTRAINT reportdata_pkey PRIMARY KEY (no, branchcode, serial, date)
)

Normally, I should have one record for each "serial, branchcode" set every
day. Unfortunately, for some reason beyond us, we are not getting these
records inserted. I am asked to report missing records in the table so that
we can provide a list to people who are responsible to enter data.

Some details about data:
- serial and branchcode values represents different devices. They are always
same within themselves.
- there may be more than one record in a day for a given serial, branchcode

What I am looking for is a list of serial, branchcode , date columns just
for the missing days.

Some data from table is as follows:
'76522985-14d9-40cb-a34f-8d57793ba83c',146,'JI20049362','50009','2017-02-04
23:21:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',145,'JI20049362','50009','2017-02-04
22:50:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',231,'JH20064415','50010','2017-02-04
23:59:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',265,'JI20033331','50010','2017-02-04
23:58:00','2017-02-13 13:13:58'
'76522985-14d9-40cb-a34f-8d57793ba83c',232,'JH20065321','50010','2017-02-04
23:50:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',233,'JH20064415','50010','2017-02-06
23:59:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',267,'JI20033331','50010','2017-02-06
23:58:00','2017-02-13 13:13:58'
'9a07616d-ace5-462e-af59-35f3e0ba23a9',234,'JH20065321','50010','2017-02-06
23:50:00','2017-02-13 13:13:58'
'909e90f1-177f-4a8a-9108-a1b7697e660f',148,'JI20049362','50009','2017-02-06
23:58:00','2017-02-13 13:13:58'

When looked in detail you can see that there is no record for date
'2017-02-05' above. As a query result I am looking for something like below:
JH20065321, 50010, 2017-02-05, Sunday
JI20033331, 50010, 2017-02-05, Sunday
JH20064415, 50010, 2017-02-05, Sunday
JI20049362, 50009, 2017-02-05, Sunday
JI20049362, 50009, 2017-02-05, Sunday

Thanks & regards,
Ertan Küçükoğlu

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2017-02-19 10:43:47 Re: Listing missing records
Previous Message Sachin Srivastava 2017-02-19 06:19:00 Re: No space left on device