How to use custom sequence name with GENERATED BY DEFAULT AS IDENTITY

[MarvinKlein1508]

Hi

when running this script in Firebird 4 it generated a new sequence for the table.

CREATE TABLE USERS (
  user_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
  username VARCHAR(50)
);

The sequence is always named RDB$(number of tables created by GENERATED BY DEFAULT AS IDENTITY)

However I want to be able to see which sequence is for what in my database so I want to use a custom name for this for example GEN_USERS_USER_ID

Is there any way to achieve this or to rename the generated sequence?

[livius2]

Why do you need to know that, if you cannot manipulate it, or do anything with it? It is system object and IDENTITY implementation detail

[mrotteveel]

Please ask support questions on firebird-support. The tracker is intended for bug reports.

But to answer your question: this is not possible, as Karol says, the backing generator of an identity column and its name are an implementation detail. If you want to use a custom sequence name, you cannot use an identity column, and instead you need to use a custom trigger (the way of defining auto-increment columns before the introduction of identity columns).

Maybe you should file an improvement request with the maker of the tool (it looks like DBeaver) to hide these system sequences (just like it does for "normal" system sequences with RDB$SYSTEM_FLAG = 1). Sequences backing an identity column have RDB$SYSTEM_FLAG = 6.

It looks like this tool is currently excluding sequences with RDB$SYSTEM_FLAG = 1, while it should only include sequences with RDB$SYSTEM_FLAG = 0, or possibly coalesce(RDB$SYSTEM_FLAG, 0) = 0 (all non-zero system flag values indicate it is a system object), because in older versions the system flag was sometimes set to NULL instead of 0.