Re: How to create "auto-increment" field WITHOUT a sequence object?

W dniu 2011-06-30 20:20, Dmitry Koterov pisze:
> And I need as compact uniq_id generation (with minimum "holes") as it possible - this is a VERY
> important requirement (to export these values into external systems which accepts only IDs limited
> from 1 to 100000).
>
> So I cannot use sequences: sequence value is obviously not rolled back, so if I insert
> nextval(...) as uniq_id, I will have large holes (because of often transaction rollbacks) and
> exhaust 100000 uniq_ids very fast. How to deal with all this without sequences?

You may use dense_rank() (or even rank()) window function to map your sequence-with-gaps to a
no-gap-id which will be used for exports.

Consider this:

test=# select uniq_id_with_gaps, dense_rank() over (order by uniq_id_with_gaps) as
uniq_id_without_gaps from (select generate_series(1, 100, 7) as uniq_id_with_gaps) a;
uniq_id_with_gaps | uniq_id_without_gaps
-------------------+----------------------
1 | 1
8 | 2
15 | 3
22 | 4
29 | 5
36 | 6
43 | 7
50 | 8
57 | 9
64 | 10
71 | 11
78 | 12
85 | 13
92 | 14
99 | 15