MeridianMERIDIAN

Increment / Counter

representation.identifier.increment

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

Casts to
BIGINT
Scope
broad_numbers

Shape

Pattern
^[0-9]+$
ValueLengthShape
11matches
21matches
31matches
41matches
51matches
61matches
0rejected: one character short

Told apart

1 of the 3 other identifier types accept every one of these samples on shape alone.

Going the other way, this type's own rule accepts 6 of 6 samples of Numeric Code.

Difficult to distinguish from regular integers without column context.

Background

Origin

FineType defines the increment type itself, as a counter value that increases by fixed steps, often 1.[1] FineType's registry says such counters may appear as row numbers, sequence IDs or generation numbers.[1] FineType moved the type from representation.numeric.increment to representation.identifier.increment in version 0.5.1.[1] FineType's registry gives the reason for the move as: increment is an identifier type, not a numeric computation type.[1]

The SQL:2003 revision of the SQL standard introduced the sequence generator, which allows standardised sequences.[2] SQL:2003 also introduced identity columns.[2]

Use

PostgreSQL's CREATE SEQUENCE command creates a new sequence number generator.[3] In PostgreSQL, a sequence's default increment is 1.[3] A PostgreSQL identity column is generated automatically from an implicit sequence and can be used to generate key values.[4] MySQL's AUTO_INCREMENT attribute can be used to generate a unique identity for new rows.[5] In SQLite, a column of type INTEGER PRIMARY KEY becomes an alias for the table's ROWID.[6] 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] PostgreSQL's row_number() window function returns the number of the current row within its partition, counting from 1.[7]

PostgreSQL sequences cannot be used where gapless numbering is needed, because nextval and setval calls are never rolled back.[3] 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] 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] SQLite's AUTOINCREMENT keyword makes the new ROWID at least one larger than the largest ROWID that has ever existed in the table.[6] 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] 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. [1]finetype/labels/definitions_representation.yaml at main · meridian-online/finetypeFineType
  2. [2]SQL:2003secondary source
  3. [3]PostgreSQL: Documentation: 18: CREATE SEQUENCEdocumentation
  4. [4]PostgreSQL: Documentation: 18: 5.3. Identity Columnsdocumentation
  5. [5]MySQL :: MySQL 8.4 Reference Manual :: 5.6.9 Using AUTO_INCREMENTdocumentation
  6. [6]SQLite Autoincrementdocumentation
  7. [7]PostgreSQL: Documentation: 18: 9.22. Window Functionsdocumentation
  8. [8]MySQL :: MySQL 8.4 Reference Manual :: 17.6.1.6 AUTO_INCREMENT Handling in InnoDBdocumentation

Detection notes

Moved from representation.numeric.increment in v0.5.1. Often appears as auto-incrementing primary keys or row numbers. Difficult to distinguish from regular integers without column context.

Use it

CLI · DuckDB
$ finetype infer -i "1" --mode column
→ representation.identifier.increment

SELECT ft_infer('1');
-- → 'representation.identifier.increment'
Cast expression
CAST({col} AS BIGINT)

Schema

https://meridian.online/schemas/representation.identifier.increment · Frictionless type integer

finetype taxonomy representation.identifier.increment -o json-schema

Aliases

countersequenceid

Type Registry