By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table definition
(
job varchar(256),
bat varchar(256),
descr varchar(256)
);
INSERT INTO definition (job,bat,descr)
VALUES
('JOBA','SCRIPTA','caracoles'),
('JOBA','SCRIPTA','navajas'),
('JOBB','SCRIPTB','navajas'),
('JOBA','SCRIPTC','casa'),
('JOBC','SCRIPTC','asombrado'),
('JOBC','SCRIPTC','casa'),
('JOBC','SCRIPTKKK','asombrado'),
('JOBC','SCRIPTCDSFDF','pepelu'),
('JOBD',NULL,'casa'),
('JOBD','DFDSFDSFDSF','caracoles'),
('JOBA','SCRIPTC','casa'),
('JOBA','SCRIPTC','casa'),
('JOBJ','SCRIPTC','casa'),
('JOBB','SCRIPTB','love');
14 rows affected
SELECT DISTINCT d.job,d.bat
FROM definition d
JOIN
(
SELECT job
FROM definition
GROUP BY job
HAVING COUNT(DISTINCT(case when bat is null then '' else bat end)) > 1
) x
ON d.job = x.job
order by 1
| job | bat |
|---|---|
| JOBA | SCRIPTA |
| JOBA | SCRIPTC |
| JOBC | SCRIPTC |
| JOBC | SCRIPTCDSFDF |
| JOBC | SCRIPTKKK |
| JOBD | DFDSFDSFDSF |
| JOBD | null |