img

Embedding a date into an Identity

part 3: handling generated always as identity

This article uses PostgreSQL as the primary database. It is probably possible to use the same technique in other database systems too.


After the decision has been made to employ the trick of embedding a date into a sequence (or bigserial, which is more or less the same) as explained here, and we have explored how to apply it wherever there is a SERIAL, the question was raised: can we apply this to a generated always as identity field?
The short answer is: yes, we can indeed!


Note: The rest of these steps are assuming that the test-setup as it was created in part 2 is present, so including the sha1 function used for data generation.

Just one little trick

The trick that is by far the most simple way to get this to work is leaning on the simple fact that although the syntax for a generated always as identity field is quite different then the proprietary SERIAL definition, under the hood it still makes use of a sequence; and even better: we have control over this name.
The only downside is that the name must be specified at the time of creation, so adding a date-in-identity column to an existing table may be challenging.

DROP TABLE if exists test;
CREATE TABLE test
( id bigint primary key generated always as identity (SEQUENCE NAME test_id_seq),
  something text,
  tstamp timestamp default now() );

Now that we know the name of the sequence, we can simply use that in the trigger.
Any of the triggers from part 2 works, except the on-the-fly version.
I chose the Method 2 trigger.
CREATE OR REPLACE FUNCTION test__BI() RETURNS trigger AS $$
BEGIN
IF currval('public.test_id_seq'::regclass)::bigint <
          ( (EXTRACT(year from CURRENT_DATE)-2000)::bigint*10000::bigint
              + EXTRACT(month from CURRENT_DATE)::bigint*100::bigint
              + EXTRACT(day from CURRENT_DATE)::bigint
          )::bigint * 10000000000000::bigint
THEN
    PERFORM setval('public.test_id_seq'::regclass,
                (currval('public.test2_id_seq'::regclass)::bigint % 10000000000000::bigint
                    + ( (EXTRACT(year from CURRENT_DATE)-2000)::bigint*10000::bigint
                         + EXTRACT(month from CURRENT_DATE)::bigint*100::bigint
                         + EXTRACT(day from CURRENT_DATE)::bigint
                      )::bigint * 10000000000000::bigint
                )::bigint,
                true);
    NEW.id = currval('public.test_id_seq'::regclass)::bigint;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Performance considerations:
The speed of 1M inserts using the Method 2 trigger takes exactly the same amount of time as it does using the Method 2 trigger on a traditional BIGSERIAL.
As the underlying mechanism is the exact same this is not too surprising, still good to measure and confirm.


Ellert van Koperen, February 2023.