Drizzle Team•16mo ago

Recommended approach for generated columns

since generated columns aren't supported yet in Drizzle, i wanted to know what's the recommended approach to declaring one (in PG)

1 Reply

Mario564OP•16mo ago

ok so i tried using the customType function which almost worked but its wrapped in double quotes upon generating migration file

const accuracyGenerated = customType<{ data: number }>({
  dataType: () => {
    return 'real generated always as ((6 * count_300 + 2 * count_100 + count_50) / (6 * (count_300 + count_100 + count_50 + misses))) stored';
  }
});

export const playerScore = pgTable('player_score', {
  id: serial('id').primaryKey(),
  accuracy: accuracyGenerated('accuracy').notNull(),
  c300: smallint('count_300').notNull(),
  c100: smallint('count_100').notNull(),
  c50: smallint('count_50').notNull(),
  misses: smallint('misses').notNull(),
});

const accuracyGenerated = customType<{ data: number }>({
  dataType: () => {
    return 'real generated always as ((6 * count_300 + 2 * count_100 + count_50) / (6 * (count_300 + count_100 + count_50 + misses))) stored';
  }
});

export const playerScore = pgTable('player_score', {
  id: serial('id').primaryKey(),
  accuracy: accuracyGenerated('accuracy').notNull(),
  c300: smallint('count_300').notNull(),
  c100: smallint('count_100').notNull(),
  c50: smallint('count_50').notNull(),
  misses: smallint('misses').notNull(),
});

CREATE TABLE IF NOT EXISTS "player_score" (
    "id" serial PRIMARY KEY NOT NULL,
    "accuracy" "real generated always as ((6 * count_300 + 2 * count_100 + count_50) / (6 * (count_300 + count_100 + count_50 + misses))) stored" NOT NULL,
    "count_300" smallint NOT NULL,
    "count_100" smallint NOT NULL,
    "count_50" smallint NOT NULL,
    "misses" smallint NOT NULL
);

CREATE TABLE IF NOT EXISTS "player_score" (
    "id" serial PRIMARY KEY NOT NULL,
    "accuracy" "real generated always as ((6 * count_300 + 2 * count_100 + count_50) / (6 * (count_300 + count_100 + count_50 + misses))) stored" NOT NULL,
    "count_300" smallint NOT NULL,
    "count_100" smallint NOT NULL,
    "count_50" smallint NOT NULL,
    "misses" smallint NOT NULL
);

Gaming

Programming

Recommended approach for generated columns