CODEWITHSUNDEEP
sqlpostgresql
Mike K
Mike K

Reputation: 6491

Pattern matching in Postgres SELECT query

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.

Upvotes: 2

Views: 207

Answers (1)

Fabian Pijcke
Fabian Pijcke

Reputation: 3210

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

Upvotes: 2

Related Questions