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