Search code examples
hsqldb

Insert value referencing another field

After creating a table like this:

CREATE TABLE T1 (
    C1 INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY,
    C2 INTEGER);

I would like to insert in C2 the same value of C1: 

INSERT INTO T1(C1, C2) VALUES (DEFAULT, C1)

I could only do something that resembles this by introducing a sequence, is there a more straightforward way?

Solution

  • If the C2 value is not going to be changed, you can declare it as a GENERATED column.

    CREATE TABLE T1 (
C1 INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1) PRIMARY KEY,
C2 INTEGER GENERATED ALWAYS AS (C1));

    Inserts are done with DEFAULT values.

    INSERT INTO T1 VALUES DEFAULT, DEFAULT