Upper case in HSQL

Question

Is there any way we can create default, value in UPPER case for an element in create table for HSQL

CREATE TABLE IF NOT EXISTS  "job" (
   "id" INTEGER IDENTITY,
   "jobId" BIGINT ,
   "jobName" VARCHAR(100)  ,
   PRIMARY KEY ("jobName")
);

Now my need is to store the jobName in Upper case every time.

Answer 1

For HSQLDB, the solution with generated column (similar to the MySQL solution) is:

CREATE TABLE IF NOT EXISTS  "job" (
 "id" INTEGER IDENTITY,
 "jobId" BIGINT ,
 "jobName" VARCHAR(100)  ,
 "jobNameU" VARCHAR(100) GENERATED ALWAYS AS (UPPER("jobName")),   
 PRIMARY KEY ("jobNameU")
);

There is a better solution with a BEFORE INSERT trigger which works without the extra generated column:

CREATE TABLE IF NOT EXISTS  "job" (
   "id" INTEGER IDENTITY,
   "jobId" BIGINT ,
   "jobName" VARCHAR(100)  ,
   PRIMARY KEY ("jobName")
);

CREATE TRIGGER makeUpper BEFORE INSERT ON "job" REFERENCING NEW ROW AS NEW
  FOR EACH ROW
  SET NEW."jobName" = UPPER(NEW."jobName")