# Increment / Counter

Key: `representation.identifier.increment`

Counter value that increases by fixed steps (often 1). May appear as row numbers, sequence IDs, or generation numbers. Broad categorization.

Page: <https://meridian.online/docs/projects/finetype/types/representation/identifier/increment>

Samples: `1`, `2`, `3`, `4`, `5`, `6`

## Background

### Origin

FineType defines the increment type itself, as a counter value that increases by fixed steps, often 1.[[1]](https://github.com/meridian-online/finetype/blob/main/labels/definitions_representation.yaml) FineType's registry says such counters may appear as row numbers, sequence IDs or generation numbers.[[1]](https://github.com/meridian-online/finetype/blob/main/labels/definitions_representation.yaml) FineType moved the type from representation.numeric.increment to representation.identifier.increment in version 0.5.1.[[1]](https://github.com/meridian-online/finetype/blob/main/labels/definitions_representation.yaml) FineType's registry gives the reason for the move as: increment is an identifier type, not a numeric computation type.[[1]](https://github.com/meridian-online/finetype/blob/main/labels/definitions_representation.yaml)

The SQL:2003 revision of the SQL standard introduced the sequence generator, which allows standardised sequences.[[2]](https://en.wikipedia.org/wiki/SQL:2003) SQL:2003 also introduced identity columns.[[2]](https://en.wikipedia.org/wiki/SQL:2003)

### Use

PostgreSQL's CREATE SEQUENCE command creates a new sequence number generator.[[3]](https://www.postgresql.org/docs/current/sql-createsequence.html) In PostgreSQL, a sequence's default increment is 1.[[3]](https://www.postgresql.org/docs/current/sql-createsequence.html) A PostgreSQL identity column is generated automatically from an implicit sequence and can be used to generate key values.[[4]](https://www.postgresql.org/docs/current/ddl-identity-columns.html) MySQL's AUTO\_INCREMENT attribute can be used to generate a unique identity for new rows.[[5]](https://dev.mysql.com/doc/refman/8.4/en/example-auto-increment.html) In SQLite, a column of type INTEGER PRIMARY KEY becomes an alias for the table's ROWID.[[6]](https://www.sqlite.org/autoinc.html) SQLite's usual algorithm gives a new row a ROWID one larger than the largest ROWID already in the table, and 1 if the table is empty.[[6]](https://www.sqlite.org/autoinc.html) PostgreSQL's row\_number() window function returns the number of the current row within its partition, counting from 1.[[7]](https://www.postgresql.org/docs/current/functions-window.html)

PostgreSQL sequences cannot be used where gapless numbering is needed, because nextval and setval calls are never rolled back.[[3]](https://www.postgresql.org/docs/current/sql-createsequence.html) In MySQL's InnoDB engine, auto-increment values generated by a transaction that rolls back are lost and not reused, so gaps may appear in an AUTO\_INCREMENT column.[[8]](https://dev.mysql.com/doc/refman/8.4/en/innodb-auto-increment-handling.html) SQLite warns that if rows are deleted, ROWIDs from deleted rows might be reused and new ROWIDs might not be in strictly ascending order.[[6]](https://www.sqlite.org/autoinc.html) SQLite's AUTOINCREMENT keyword makes the new ROWID at least one larger than the largest ROWID that has ever existed in the table.[[6]](https://www.sqlite.org/autoinc.html) Treat a gap in a counter column as normal rather than as evidence of missing records, unless the source system promises gapless numbering. (Meridian's view) Do not assume a counter's order matches the order in which rows were created; a timestamp column, where there is one, is the safer guide. (Meridian's view)

FineType's registry notes that an increment is difficult to distinguish from a regular integer without column context.[[1]](https://github.com/meridian-online/finetype/blob/main/labels/definitions_representation.yaml) Look at the column name and at whether the values are unique and close to consecutive before deciding a column of whole numbers is a counter. (Meridian's view) A counter identifies a row; summing or averaging one rarely means anything. (Meridian's view)

## Sources

1. [finetype/labels/definitions\_representation.yaml at main · meridian-online/finetype](https://github.com/meridian-online/finetype/blob/main/labels/definitions_representation.yaml) — FineType
2. [SQL:2003](https://en.wikipedia.org/wiki/SQL:2003) — secondary source
3. [PostgreSQL: Documentation: 18: CREATE SEQUENCE](https://www.postgresql.org/docs/current/sql-createsequence.html) — documentation
4. [PostgreSQL: Documentation: 18: 5.3. Identity Columns](https://www.postgresql.org/docs/current/ddl-identity-columns.html) — documentation
5. [MySQL :: MySQL 8.4 Reference Manual :: 5.6.9 Using AUTO\_INCREMENT](https://dev.mysql.com/doc/refman/8.4/en/example-auto-increment.html) — documentation
6. [SQLite Autoincrement](https://www.sqlite.org/autoinc.html) — documentation
7. [PostgreSQL: Documentation: 18: 9.22. Window Functions](https://www.postgresql.org/docs/current/functions-window.html) — documentation
8. [MySQL :: MySQL 8.4 Reference Manual :: 17.6.1.6 AUTO\_INCREMENT Handling in InnoDB](https://dev.mysql.com/doc/refman/8.4/en/innodb-auto-increment-handling.html) — documentation