Serial To Identity Conversion

---
name: serial-to-identity-conversion
description: "Serial to Identity Column Conversion"
metadata:
  short-description: "Serial to Identity Column Conversion"
  original-index: 17
---

# Serial to Identity Column Conversion

## What

Migrating legacy `serial` / `bigserial` columns (which use implicit sequences
via `nextval()`) to the SQL-standard `GENERATED BY DEFAULT AS IDENTITY` syntax.

## Why

`serial` is a PostgreSQL shorthand that creates a separate sequence and a
`DEFAULT nextval(...)` expression. This has drawbacks:
- The sequence is **not tied** to the column in `pg_depend` -- dropping the
  column may leave orphaned sequences
- `pg_dump` does not always restore the relationship correctly
- It's non-standard SQL (not portable)
- `IDENTITY` columns are the modern PostgreSQL standard (10+)

## How

### Step 1: Check current state
```sql
SELECT column_name, column_default, is_identity
FROM information_schema.columns
WHERE table_name = 'FeatureFlags' AND column_name = 'FlagId';
-- If column_default = nextval('...') and is_identity = 'NO', needs conversion
```

### Step 2: Remove the old default
```sql
ALTER TABLE feature_flags."FeatureFlags"
    ALTER COLUMN "FlagId" DROP DEFAULT;
```

### Step 3: Drop the orphaned sequence
```sql
DROP SEQUENCE IF EXISTS feature_flags."FeatureFlags_FlagId_seq";
```

### Step 4: Add identity
```sql
ALTER TABLE feature_flags."FeatureFlags"
    ALTER COLUMN "FlagId"
    ADD GENERATED BY DEFAULT AS IDENTITY;
```

### Step 5: Sync the sequence to max value
```sql
-- Critical: set the identity sequence to the current max value
SELECT setval(
    pg_get_serial_sequence('feature_flags."FeatureFlags"', 'FlagId'),
    COALESCE((SELECT MAX("FlagId") FROM feature_flags."FeatureFlags"), 1)
);
```

## GENERATED BY DEFAULT vs GENERATED ALWAYS

| Mode | Behavior | Use When |
|------|----------|----------|
| `GENERATED BY DEFAULT` | Auto-generates but allows explicit INSERT | App sometimes provides the ID |
| `GENERATED ALWAYS` | Rejects explicit INSERT (unless `OVERRIDING SYSTEM VALUE`) | ID must always be auto-generated |

Use `BY DEFAULT` for backward compatibility with existing app code.

**Official PG 16 behavior** (sql-createtable.html):
- IDENTITY columns are implicitly `NOT NULL` -- no separate constraint needed
- The implicit sequence is tightly linked via `pg_attribute` (unlike serial
  which only has an `OWNED BY` dependency in `pg_depend`)
- `GENERATED ALWAYS` still allows explicit values with
  `INSERT INTO t OVERRIDING SYSTEM VALUE VALUES (...)`

## When to Use

- Modernizing legacy schemas with `serial` columns
- After audit findings flag orphaned sequences
- When preparing for cross-database portability

## Where We Used It

- ****: Converted `FeatureFlags.FlagId` from serial to IDENTITY

## References

- [PostgreSQL Best Practices](../DOCUMENTS/PostgreSQL_BestPractices_Azure.md)
  -- see Backlog #7 (Replace explicit sequences with IDENTITY)
  and "Schema design and data types" section

## Gotchas

- **Must sync the sequence** after conversion -- if you skip Step 5, the next
  INSERT will get a duplicate key error
- Check for any triggers or procedures that reference the old sequence by name
- `GENERATED ALWAYS` may break app code that does explicit ID inserts
- The old sequence name may differ from the new identity sequence name

---

*Category: DDL | Origin: *