Support GENERATED BY DEFAULT AS IDENTITY for a PostgreSQL identity column

[kenn]

Now that we support PostgreSQL Identity Columns GENERATED ALWAYS AS IDENTITY via generatedAlwaysAsIdentity, why don't we add GENERATED BY DEFAULT AS IDENTITY, too?

#2564

GENERATED ALWAYS AS IDENTITY does not allow manual updates on the ID column, but sometimes we want to renumber those IDs.

In my case, I realized that serial IDs get a huge gap between them when onConflictDoUpdate is used. If you increment access counters on a column with onConflictDoUpdate, the ID number could easily be 1,000~100,000x larger than the number of rows, depending on the update frequency.

Now, I have mitigated the issue by changing the code "First, try to SELECT. If record found, UPDATE. If not found, INSERT ON CONFLICT DO UPDATE to deal with pathological case of race condition" — then after this, I wanted to compact the ID space by renumbering the bloated IDs.

So I would run the following script — but this kind of manual modification on ID column is NOT allowed if GENERATED ALWAYS AS IDENTITY is specified. What I need is the best of both world — GENERATED BY DEFAULT AS IDENTITY.

// Renumber the ID column sequentially, minimizing the gap between IDs
import { sql } from 'drizzle-orm'
import { db } from '~/db.server'

export async function renumberID(table_name) {
  // Get the table name
  let table = sql.raw(table_name)

  await db.transaction(async (tx) => {
    // Renumber the IDs
    await tx.execute(
      sql`
        WITH updated AS (
          SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS new_id
          FROM ${table}
        )
        UPDATE ${table}
        SET id = updated.new_id
        FROM updated
        WHERE ${table}.id = updated.id`,
    )

    // Get  max ID + 1 as next_id
    let result = await tx.execute(
      sql`SELECT MAX(id) + 1 as next_id FROM ${table}`,
    )
    let next_id = (result.rows[0].next_id as number).toString()
    console.log('next_id', next_id)

    // Restart the sequence from the new max ID + 1
    await tx.execute(
      sql`ALTER SEQUENCE ${table}_id_seq RESTART WITH ${sql.raw(next_id)}`,
    )
  })

  // Clean up the table
  await db.execute(sql`VACUUM FULL ${table}`)
  await db.execute(sql`ANALYZE ${table}`)
}

// Specify table name
await renumberID('metrics')

[bhuynhdev]

As of today, happy to say that drizzle-orm 0.33.0 do have support for generatedByDefaultAsIdentity

[rifkyputra]

does it support OVERRIDING SYSTEM VALUE ?
PostgreSQL issued the following error:

[Err] ERROR: cannot insert into column "color_id"
DETAIL: Column "color_id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override.

[mirlindmurati]

Any luck with this? @rifkyputra

[mrmattrc]

@bhuynhdev, it doesn't look like this is in the documentation or available in my editor as an option. I'm running "drizzle-orm": "0.41.0".

[yisibl]

does it support OVERRIDING SYSTEM VALUE ? PostgreSQL issued the following error:

[Err] ERROR: cannot insert into column "color_id" DETAIL: Column "color_id" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override.

Please try overridingSystemValue().
https://github.com/drizzle-team/drizzle-orm/blob/main/changelogs/drizzle-orm/0.36.4.md#added-overriding-system-value-api-to-dbinsert

[kenn]

This feature should be mentioned somewhere in https://orm.drizzle.team/docs/column-types/pg#identity-columns ?

[yisibl]

@bhuynhdev As far as I know, this feature is supported since 0.32.0, not 0.33.0

• release notes: https://github.com/drizzle-team/drizzle-orm/releases/tag/0.32.0
• docs for sequences: https://orm.drizzle.team/docs/sequences
• docs for identity columns: https://orm.drizzle.team/docs/column-types/pg#identity-columns