Get rows with missing id in Redshift

Question

I have something like

id | name
---|-----
1  | Sarah
3  | Pat
4  | Lea

I'm looking for missing rows. I've tried to use generate_series and a left join but this is something you can't do in Redshift because generate_series is not supported.

Is it possible to do it without temporary table?

EDIT

Finally did with a temporary table (0 to 1_000_000) see answer.

You need temporary table or a subquery. Because you need something to tell you what is the full set, to know what is missing. So the answer is ***NO***. — Juan Carlos Oropeza, Dec 15 '16 at 13:54
this may help http://stackoverflow.com/questions/17282276/using-sql-function-generate-series-in-redshift — Vamsi Prabhala, Dec 15 '16 at 13:55
Why do you care? Generated IDs are not supposed to be gapless (*especially* if they are generated by a sequence). — , Dec 15 '16 at 16:23

score 0 · Answer 1 · answered Dec 15 '16 at 17:07

That's probably not optimal. But this is how I did

-- create temporary table
CREATE TABLE series (id INT) SORTKEY(id);

-- insert 0 to 1_000_000
INSERT INTO series WITH seq_0_9 AS
(SELECT 0 AS num
UNION ALL SELECT 1 AS num
UNION ALL SELECT 2 AS num
UNION ALL SELECT 3 AS num
UNION ALL SELECT 4 AS num
UNION ALL SELECT 5 AS num
UNION ALL SELECT 6 AS num
UNION ALL SELECT 7 AS num
UNION ALL SELECT 8 AS num
UNION ALL SELECT 9 AS num),
                    seq_0_999 AS
(SELECT a.num + b.num * 10 + c.num * 100 AS num
FROM seq_0_9 a,
    seq_0_9 b,
    seq_0_9 c)
SELECT a.num + b.num * 1000 AS num
FROM seq_0_999 a,
 seq_0_999 b
ORDER BY num;

-- Why not
VACUUM series;

-- LEFT OUTER JOIN with table inverted and with the interval
SELECT *
FROM series
LEFT OUTER JOIN other_table ON series.id = other_table.id
WHERE series.id BETWEEN 0 AND 4
ORDER BY series.id;

Get rows with missing id in Redshift

1 Answers1