Support DEFAULT value / auto-UUID column in CREATE (similar to SERIAL)

[sapalli2989]

It would be great, if we could declare within CREATE/DDL, that ids of a UUID column are to be auto-created via gen_random_uuid(). This feature would be analogue to SERIAL for integer sequences.

Currently Kuzu provides gen_random_uuid() for UUIDv4 auto-creation. But the need to invoke this function manually for new nodes with UUID primary key is inconvenient and sometimes not easily possible - as seen with MERGE in #3014. Actually I think proposal here is more useful than linked one.

PostgreSQL provides DEFAULT value keyword within CREATE in combination with equally named gen_random_uuid() for these cases (more examples in 1, 2):

CREATE TABLE table_name (
    unique_id UUID DEFAULT gen_random_uuid(),
    ...
    PRIMARY KEY (unique_id)
);

or with directly prepended PRIMARY KEY:

CREATE TABLE table_name (
    unique_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    ...
);

, requested for Kuzu in #2911.

I think this would be a nice addition to usability of UUIDs.

[prrao87]

I think this is a really good idea, and I'm also wondering if it makes sense to have an alternative option to auto-create a unique integer primary key (something like UINT128, kind of what Neo4j does) instead of a UUID in cases where a simple serial integer works. From a user perspective, this is something that commonly is a source of annoyance: having to create a primary key manually for cases where the raw data doesn't have it.

But I'll leave it to @ray6080 and @andyfengHKU to discuss on any potential implementation challenges for this.

[sapalli2989]

See #3238

[rjurney]

I want this. This is an obvious feature :)

[prrao87]

Hi @rjurney, this should work in the latest release:

import kuzu

def main():
    db = kuzu.Database()
    conn = kuzu.Connection(db)

    # Create schema
    conn.execute("""
    CREATE NODE TABLE X (
        unique_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        name STRING
    );
    """
    )
    conn.execute("MERGE (a:X {name: 'Alice'})")
    conn.execute("MERGE (a:X {name: 'Bob'})")

    # Check
    response = conn.execute(
        """
        MATCH (a:X)
        RETURN a.*
        """
    )
    for row in response:
        print(row)

if __name__ == "__main__":
    main()

[UUID('4bd87d6f-8d42-4905-ada1-7858d50f1621'), 'Alice']
[UUID('44eee4c0-3436-4382-b883-f2198c32efa0'), 'Bob']

[rjurney]

@prrao87 ahhh thanks!