Add support gen_uuid() as possible DEFAULT value for new fields

[SkliarOleksandr]

Now you can't use internal/system functions as a default value:

alter table MY_TABLE add REC_UID GUID default gen_uuid() not null

As I know, one possible reason is a BACKUP utility problem

but what is the difference between this?

alter table MY_TABLE add REC_DATE timestamp default current_timestamp not null ?

It would be really great to have the ability to use gen_uuid() as a default value, of course there is a workaround
when we create a nullable field, then update it, then set not-null, but for code-first ORMs it's very inconvenient.

[mrotteveel]

The SQL Standard only allows a limited number of expressions for the DEFAULT clause. It does not allow just any expression.

Specifically, it defines (11.5 <default clause>, 6.43 <datetime value function>, and 6.5 <contextually typed value specification>):

<default clause> ::=
    DEFAULT <default option>

<default option> ::=
    <literal>
  | <datetime value function>
  | USER
  | CURRENT_USER
  | CURRENT_ROLE
  | SESSION_USER
  | SYSTEM_USER
  | CURRENT_CATALOG
  | CURRENT_SCHEMA
  | CURRENT_PATH
  | <implicitly typed value specification>

<datetime value function> ::=
    <current date value function>
  | <current time value function>
  | <current timestamp value function>
  | <current local time value function>
  | <current local timestamp value function>

<current date value function> ::=
    CURRENT_DATE

<current time value function> ::=
    CURRENT_TIME [ <left paren> <time precision> <right paren> ]

<current local time value function> ::=
    LOCALTIME [ <left paren> <time precision> <right paren> ]

<current timestamp value function> ::=
    CURRENT_TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]

<current local timestamp value function> ::=
    LOCALTIMESTAMP [ <left paren> <timestamp precision> <right paren> ]

<implicitly typed value specification> ::=
    <null specification>
  | <empty specification>

<null specification> ::=
    NULL

<empty specification> ::=
    ARRAY <left bracket or trigraph> <right bracket or trigraph>
  | MULTISET <left bracket or trigraph> <right bracket or trigraph>

In other words, other types of expressions, like calling a function like gen_uuid() are not defined in the standard.

I think we could make an exception for gen_uuid(), but because this can also be solved by using a trigger, I don't much see a good reason why we should do that, especially as that will probably be followed by a "why can't you do it for function X, or expression Y".

[SkliarOleksandr]

I understand your position, and it makes sense. But again, we are talking about how convenient is FB for a particular task.
In our project, we use code-first ORM that doesn't use triggers at all, and yes, we can add a custom script - but this is a crutch.
Using gen_uuid() as a default value looks logical and obviously will be helpful although it's not a part of SQL standard (for today!)

[aafemt]

UUID identifiers are often generated on the client side. This is one of their advantages over generators.

[asfernandes]

If you try this alter table with current_timestamp, you will see that existing records will have the same timestamp. I can't imagine you want the same with uuids.

[SkliarOleksandr]

...at least that makes some sense since the update is performing quickly (as usual) and I honestly didn't know about this)).
Of course, there is no sense to take one UUID for many records.

[asfernandes]

...at least that makes some sense since the update is performing quickly (as usual) and I honestly didn't know about this)). Of course, there is no sense to take one UUID for many records.

Yes, it's quick because it's stored once in metadata associated with the format version. And it fixed an old bug where a default current_timestamp were evaluated each time the record was selected, acting like a computed value.