Inserting rows into a table with a UUID column

The system generates a UUID column value when the keyword DEFAULT is used as the insert_clause for the UUID column.

Here are a few examples that show INSERT statements for both flavors of the UUID column – GENERATED BY DEFAULT and when no DEFAULT CLAUSE is specified in a CREATE TABLE statement. The keyword DEFAULT in the INSERT statement applies only when the UUID column is declared as GENERATED BY DEFAULT.

Example 7-8 Inserting rows into a table with a UUID column without GENERATED BY DEFAULT clause

CREATE TABLE myTable (id STRING AS UUID, name STRING, PRIMARY KEY (id))

Output:

Statement completed successfully

INSERT INTO myTable values("a81bc81b-dead-4e5d-abff-90865d1e13b1","test1")

Output:

Statement completed successfully

In the above example, the id column in the table myTable has no "GENERATED BY DEFAULT" defined. Therefore, whenever you insert a new row, you need to explicitly specify the value for the id column.

Example 7-9 Inserting rows into a table with a UUID column using the random_uuid function

The value for a UUID column can also be generated using the random_uuid function. See Function to generate a UUID string.

INSERT INTO myTable values(random_uuid(),"test2")

Output:

{"NumRowsInserted":1}
1 row returned
Statement completed successfully

select * from myTable

Output:

{"id":"d576ab3b-8a36-4dff-b50c-9d9d4ca6072c","name":"test2"}
{"id":"a81bc81b-dead-4e5d-abff-90865d1e13b1","name":"test1"}
2 rows returned
Statement completed successfully

In this example, a randomly generated UUID is fetched using the random_uuid() function. This value is used in the INSERT statement.

Example 7-10 Inserting rows into a table with a UUID column with GENERATED BY DEFAULT clause

CREATE TABLE myTable (id STRING AS UUID GENERATED BY DEFAULT,name STRING, PRIMARY KEY (id))

Output:

Statement completed successfully

INSERT INTO myTable VALUES(default,"test1") returning id

Output:

{"id":"e7fbab63-7730-4ec9-be73-a62e33ea73c3"}
Statement completed successfully

In the above example, the id column in myTable has "GENERATED BY DEFAULT" defined. The system generates a UUID column value when the keyword DEFAULT is used in the insert_clause for the UUID column. The system generated UUID value is fetched using the returning clause.