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 : 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));
Statement completed successfully
INSERT INTO myTable values("a81bc81b-dead-4e5d-abff-90865d1e13b1","test1");
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 : 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.
sql-> INSERT INTO myTable values(random_uuid(),"test2");
{"NumRowsInserted":1}
1 row returned
Statement completed successfully
sql-> select * from myTable;
{"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 : 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));
Statement completed successfully
INSERT INTO myTable VALUES(default,"test1") returning id;
{"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.