Question
· 4 hr ago

Computed/generated/stored columns

I have a Postgres table which should be migrated to IRIS. The table has a computed column, like:

CREATE TABLE example_table (
    id VARCHAR(10) PRIMARY KEY,
    normalized_id VARCHAR(10) GENERATED ALWAYS AS (LPAD(id, 10, '0')) STORED
);

IRIS also has the LPAD function but I can't figure out how to achieve the same result using pure SQL DDL.

Tried this:

CREATE TABLE example_table (
    id VARCHAR(10) PRIMARY KEY,
    normalized_id VARCHAR(10) COMPUTECODE {
        set {normalized_id} = LPAD({id}, 10, '0')
    }
    COMPUTEONCHANGE(id))

Got the following error:

SQL Error [400] [S1000]: [SQLCODE: <-400>:<Fatal error occurred>]
[Location: <ServerLoop>]
[%msg: <ERROR #5475: Error compiling routine: SQLUser.exampletable.1.  Errors:  ERROR: SQLUser.exampletable.1.int(1003) #1017: Invalid operator : ''0')' : Offset:31 [znormalizedidCompute+3^SQLUser.exampletable.1]
  > ERROR #5030: An error occurred while compiling class 'SQLUser.exampletable'>]

Changing single quotes to double quotes doesn't work

Product version: IRIS 2025.3
Discussion (0)1
Log in or sign up to continue