Pattern matching in Postgres SELECT query

Question

I have a table in by database that has a name field with a non-null and unique constraint.

On the frontend, a user can clone certain entities. In this scenario, the name field gets suffixed with a version number.

For instance, if record A exists with the name TEST_NAME, cloning this record would result in record B being created with the name TEST_NAME [2]. Cloning record A again would result in a record C with the name TEST_NAME [3].

To determine the version number, I run a count(*) against the table, returning the number of records that match the root name, in this case: 'TEST_NAME'.

Here is the query:

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

The issue here, is that if a user changes the name of record C to TEST_NAME [3]abc, then the above query would still pick this up and create record D with the name TEST_NAME [4], instead of TEST_NAME [3]abc [2].

How can I avoid this? I'd like to only match name values that follow the format ^TEST_NAME [x]$, where x is any integer.

PostgreSQL has support for Regexes :-) postgresql.org/docs/current/… — Fabian Pijcke
– Fabian Pijcke, Commented Jan 26, 2022 at 13:40

Fabian Pijcke · Accepted Answer · 2022-01-26 14:13:17Z

2

PostgreSQL has support for regexes.

For instance, I guess what you need is

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

For computing the next version, I propose the following :

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

Here is what's going on :

For each version, we match the regexp ^(.)[([0-9]+)]$|^(.)$. Groups 1 and 2 will be populated if the version ends with a version number. Group 3 always contains the whole name. We put this result in the lateral table t(matches).

If group 1 and 2 have values, then "matches[1] || ' [' || matches[2]::int + 1 || ']'" is the next version, otherwise pick matches[3] and add [2] to it.

As a bonus, the following query will give the last version for every rootname, and the next version available.

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;

If you just have a rootname (say TEST_NAME), and assuming you only have one column version in your table which would be called versions, you can clone the record using :

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';

edited Jan 26, 2022 at 14:13

answered Jan 26, 2022 at 13:42

Fabian Pijcke

3,30027 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Philippe Over a year ago

You have to modify the search expression to '^TEST_NAME \[[0-9]{1,}\]$' to get TEST_NAME [99] or TEST_NAME [999] too

Fabian Pijcke Over a year ago

@Philippe, I think this is the same as the "+" I put after [0-9] :-)

Philippe Over a year ago

Yes you're right. Was the "+" there in the first version ? Haven't seen it. Something wrong with my eyes ;) Apologize !!!

Fabian Pijcke Over a year ago

No worries! :-D

Collectives™ on Stack Overflow

Pattern matching in Postgres SELECT query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related