Add a check for tables with incrementing column names [schemacrawler]

[mfvanek]

See https://www.schemacrawler.com/lint.html

• Linter: schemacrawler.tools.linter.LinterTableWithIncrementingColumns
  Checks for tables with incrementing column names, for example, a table with column names like CONTACT1, CONTACT2 and so on can indicate de-normalization. Additionally, SchemaCrawler Lint will check that the data-types of all incrementing columns are the same, and that no numbers are skipped.
  https://github.com/schemacrawler/SchemaCrawler/blob/v16.24.3/schemacrawler-lint/src/main/java/schemacrawler/tools/linter/LinterProviderTableWithIncrementingColumns.java

[mfvanek]

-- Test queries for sql/tables_with_incrementing_columns.sql
-- Demonstrates detection behavior for regular and partitioned tables.
--
-- Expected results:
--   demo.customers  -> detected (contact1, contact2, contact3)
--   demo.events     -> detected (tag1, tag2) — partitioned parent
--   demo.orders     -> detected (address1, address2, phone1, phone2 — two groups merged)
--   demo.products   -> NOT detected (only one column matches the pattern)
--   demo.categories -> NOT detected (no incrementing columns)
--   demo.events_2024 -> NOT detected (child partition, excluded by relispartition)

-- ============================================================
-- Schema
-- ============================================================

create schema if not exists demo;

-- ============================================================
-- Tables expected to be detected
-- ============================================================

-- Three contact columns: one group, same type, no gaps
create table if not exists demo.customers (
    id         bigint generated always as identity primary key,
    first_name text not null,
    last_name  text not null,
    contact1   text,
    contact2   text,
    contact3   text
);

-- Two independent incrementing groups on the same table:
-- phone1/phone2 and address1/address2 — merged into one row
create table if not exists demo.orders (
    id       bigint generated always as identity primary key,
    phone1   text,
    phone2   text,
    address1 text,
    address2 text
);

-- Partitioned parent: check must detect it via relkind = 'p'
create table if not exists demo.events (
    id         bigint    not null,
    event_date date      not null,
    tag1       text,
    tag2       text
) partition by range (event_date);

-- Child partition: must NOT appear in results (relispartition = true)
create table if not exists demo.events_2024
    partition of demo.events
    for values from ('2024-01-01') to ('2025-01-01');

-- ============================================================
-- Tables NOT expected to be detected
-- ============================================================

-- Only one column matches the pattern — no group of two or more
create table if not exists demo.products (
    id   bigint generated always as identity primary key,
    sku1 text,
    name text not null
);

-- No incrementing columns at all
create table if not exists demo.categories (
    id   bigint generated always as identity primary key,
    name text not null
);

-- ============================================================
-- Run the check
-- ============================================================

with
    column_patterns as (
        select
            pc.oid,
            pc.oid::regclass::text as table_name,
            col.attname,
            col.attnotnull,
            col.attnum as attposition,
            regexp_replace(col.attname, '\d+$', '') as base_name
        from
            pg_catalog.pg_class pc
            inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
            inner join pg_catalog.pg_attribute col on col.attrelid = pc.oid
        where
            pc.relkind in ('r', 'p') and
            not pc.relispartition and
            col.attnum > 0 and
            not col.attisdropped and
            col.attname ~ '^\D+\d+$' and
            nsp.nspname = 'demo'
    ),

    groups_with_two_or_more as (
        select
            oid,
            base_name
        from column_patterns
        group by oid, base_name
        having count(*) >= 2
    )

select
    cp.table_name,
    pg_table_size(cp.oid) as table_size,
    array_agg(quote_ident(cp.attname) || ',' || cp.attnotnull::text order by cp.attposition) as columns
from
    column_patterns cp
    inner join groups_with_two_or_more g on g.oid = cp.oid and g.base_name = cp.base_name
group by cp.oid, cp.table_name
order by cp.table_name;