String matching in PostgreSQL

Question

I need to implement a regular expression (as I understand) matching in PostgreSQL 8.4. It seems regular expression matching are only available in 9.0+.

My need is:

When I give an input 14.1 I need to get these results:

14.1.1
14.1.2
14.1.Z
...

But exclude:

14.1.1.1
14.1.1.K
14.1.Z.3.A
...

The pattern is not limited to a single character. There is always a possibility that a pattern like this will be presented: 14.1.1.2K, 14.1.Z.13.A2 etc., because the pattern is provided the user. The application has no control over the pattern (it's not a version number).

Any idea how to implement this in Postgres 8.4?

After one more question my issue was solved:

Escaping a LIKE pattern or regexp string in Postgres 8.4 inside a stored procedure

8.4 (which is no longer maintained) has regular expressions as well: postgresql.org/docs/8.4/static/… — user330315
– user330315, Commented Apr 2, 2015 at 7:31
As always: the exact data type would help to clarify. Better yet, the table definition (\d tbl in psql). — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 2, 2015 at 8:44

Erwin Brandstetter · Accepted Answer · 2022-07-25 06:49:08Z

5

Regular expression matching has been in Postgres practically for ever, at least since version 7.1. Use the these operators:

~ !~ ~* !~*

For an overview, see:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

The point in your case seems to be to disallow more dots:

SELECT *
FROM   tbl
WHERE  version LIKE '14.1.%'        -- for performance
AND    version ~ '^14\.1\.[^.]+$';  -- for correct result

db<>fiddle here
_{Old sqlfiddle}

The LIKE expression is redundant, but it is going to improve performance dramatically, even without index. You should have an index, of course.

The LIKE expression can use a basic text_pattern_ops index, while the regular expression cannot, at least in Postgres 8.4.
Or with COLLATE "C" since Postgres 9.1. See:

[^.] in the regex pattern is a character class that excludes the dot (.). So more characters are allowed, just no more dots.

Performance

To squeeze out top performance for this particular query you could add a specialized index:

CREATE INDEX tbl_special_idx ON tbl
((length(version) - length(replace(version, '.', ''))), version text_pattern_ops);

And use a matching query, the same as above, just replace the last line with:

AND   length(version) - length(replace(version, '.', '')) = 2

db<>fiddle here
_{Old sqlfiddle}

edited Jul 25, 2022 at 6:49

answered Apr 2, 2015 at 7:55

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

7 Comments

user330315 Over a year ago

What about using arrays? array_length(string_to_array(col, '.'), 1) = 3 and version like '14.1.%'

Erwin Brandstetter Over a year ago

@a_horse_with_no_name: Works just fine: sqlfiddle.com/#!15/9eecb/286 Slightly more expensive I'd expect.

user330315 Over a year ago

I wasn't sure if the array handling or the regex would be more expensive

Erwin Brandstetter Over a year ago

@a_horse_with_no_name: Handling alone might beat a regex in pg 8.4, but if we need to convert the string first, I am not so sure. There is a cheaper way, similar to your idea. I added a chapter.

Nandakumar V Over a year ago

Thanks for the input, that definitely solved the problem. But I was going to write it in a stored procedure, but dont know how to escape the string within the stored procedure 14.1. => 14\.1\.

|

Yule · Accepted Answer · 2015-04-02 07:35:50Z

0

You can't do regex matching, but I believe you can do like operators so:

SELECT * FROM table WHERE version LIKE '14.1._';

Will match any row with a version of '14.1.' followed by a single character. This should match your examples. Note that this will not match just '14.1', if you needed this as well. You could do this with an OR.

SELECT * FROM table WHERE version LIKE '14.1._' OR version = '14.1';

answered Apr 2, 2015 at 7:35

Yule

9,8143 gold badges55 silver badges75 bronze badges

3 Comments

Nandakumar V Over a year ago

The characters is not limited to a single character, code like this 14.1.23 can also come.

Nandakumar V Over a year ago

Also the underscore thing is it a patterm matching mechanism or something. I didnt understand how LIKE '14.1._' will match 14.1.2

Yule Over a year ago

_ matches any single character. so would match all your examples in your question. It wont match 14.1.23.

plaes · Accepted Answer · 2015-04-02 07:37:39Z

0

Regex matching should be possible with Postgresql-8.4 like this:

SELECT * FROM table WHERE version ~ '^14\.1\..$';

answered Apr 2, 2015 at 7:37

plaes

32.9k11 gold badges95 silver badges89 bronze badges

1 Comment

Nandakumar V Over a year ago

the backslashes are those escape sequence?

Collectives™ on Stack Overflow

String matching in PostgreSQL

3 Answers 3

Performance

7 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Performance

7 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related