sql - Postgres SELECT 查询中的模式匹配

Question

我在数据库中有一个表，该表的 name 字段具有非空且唯一的约束。

在前端，用户可以克隆某些实体。在这种情况下，name 字段以版本号作为后缀。

例如，如果记录 A 的名称为 TEST_NAME，则克隆此记录将导致创建的记录 B 的名称为 TEST_NAME [2]。再次克隆记录 A 将生成名为 TEST_NAME [3] 的记录 C。

为了确定版本号，我对表运行了一个count(*)，返回与根名称匹配的记录数，在本例中为:'TEST_NAME'.

这里是查询:

SELECT COUNT(*)
FROM my_table
WHERE name LIKE 'TEST_NAME%'

这里的问题是，如果用户将记录 C 的名称更改为 TEST_NAME [3]abc，则上述查询仍会选择它并创建名称为 的记录 D >TEST_NAME [4]，而不是 TEST_NAME [3]abc [2]。

我怎样才能避免这种情况？我只想匹配遵循格式 ^TEST_NAME [x]$ 的 name 值，其中 x 是任何整数。

Answer 1

PostgreSQL 支持正则表达式。

比如，我猜你需要的是

SELECT COUNT(*)
FROM my_table
WHERE name ~ '^TEST_NAME \[[0-9]+\]$'

为了计算下一个版本，我提出以下建议:

SELECT
  version,
  COALESCE(
    matches[1] || ' [' || matches[2]::int + 1 || ']',
    matches[3] || ' [2]'
  ) AS nextVersion
FROM versions
CROSS JOIN LATERAL (
  SELECT regexp_matches(version, '^(.*) \[([0-9]+)\]$|^(.*)$') matches
) t

这是正在发生的事情:

对于每个版本，我们匹配正则表达式 ^(.)[([0-9]+)]$|^(.)$。如果版本以版本号结尾，将填充第 1 组和第 2 组。第 3 组始终包含全名。我们把这个结果放在侧表t(matches)中。

如果第 1 组和第 2 组有值，则“matches[1] || ' [' || matches[2]::int + 1 || ']'”是下一个版本，否则选择 matches[3]并向其添加 [2]。

作为奖励，以下查询将为每个根名称提供最新版本，以及可用的下一个版本。

SELECT rootname, MAX(t2.version) AS lastVersion, MAX(t2.version) + 1 AS nextVersion
FROM versions
CROSS JOIN LATERAL (
  SELECT regexp_matches(version, '^(.*) \[([0-9]+)\]$|^(.*)$') matches
) t1
CROSS JOIN LATERAL (
  SELECT
    COALESCE(matches[1], matches[3]) AS rootname,
    COALESCE(matches[2]::int, 1) AS version
) t2
GROUP BY rootname;

如果您只有一个根名称(比如 TEST_NAME)，并且假设您的表中只有一列版本，称为版本，您可以使用以下方法克隆记录:

INSERT INTO versions
SELECT rootname || ' [' || MAX(t2.version) + 1 || ']'
FROM versions
CROSS JOIN LATERAL (
  SELECT regexp_matches(version, '^(.*) \[([0-9]+)\]$|^(.*)$') matches
) t1
CROSS JOIN LATERAL (
  SELECT
    COALESCE(matches[1], matches[3]) AS rootname,
    COALESCE(matches[2]::int, 1) AS version
) t2
WHERE rootname = 'TEST_NAME';