postgres: parameterized query with pattern matching

Question

in node, i'm creating the following query:

let sql_query = "\
   SELECT \
      ... \
   WHERE \
      FACILITY_ID = $1 \
      AND (EVENT_START_TIME_UNIX_MS < $2) \
      AND (EVENT_END_TIME_UNIX_MS > $3) \
      AND (LOWER(DESCRIPTION_TEXT) ~* '\yden\y')"

that fourth line is doing a word boundary regex for the term "den" but i need it to be parameterized like the preceding lines. i tried the below but it doesn't work.

      AND (LOWER(DESCRIPTION_TEXT) ~* '\y$4\y')

i also tried this:

      AND (LOWER(DESCRIPTION_TEXT) ~* $4)

where $4 is set to '\yden\y'. but that didn't work either.

thanks for any tips.

Use ~* $4 instead and pass the full pattern as the parameter value. Parameterized queries don't use string interpolation. The parameter values never become part of the query text. The database compiles the query into a parameterized execution plan that's executed using the parameter values. That's why SQL injection and formatting errors are avoided. The parameter values remain strongly typed, outside the query itself, even when the execution plan gets executed. Unless of course you use the parameter values to construct and execute dynamic SQL inside the query text — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 5 at 12:11
PS: ~* is case-insensitive, there's no reason to use LOWER(...). This could prevent the database from using trigram indexes to accelerate the regex search — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 5 at 12:18

Bending Rodriguez · Accepted Answer · 2025-11-19 09:10:40Z

5

You're not properly using postgresql concatenation syntax. In postgresql, || is used to concatenate strings. Try:

AND (DESCRIPTION_TEXT) ~* ('\y' || $4 || '\y'))

Also, if you use ~* you don't need to use lower(), since ~* performs a case-insensitive matching.

As a bonus, if $4 is null, the sequence of || nullifies the whole string. With concat(), it'd end up as a '\y\y' pattern leading to false positives since that matches even a single [:word:] class character.

edited 20 hours ago

answered Sep 5 at 12:07

Bending Rodriguez

1,3923 gold badges27 silver badges70 bronze badges

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

3 Comments

benard344444 Sep 9 at 18:27

The issue is with your regex pattern syntax for PostgreSQL parameterized queries. You need to use word boundaries correctly with the parameter. Try this approach: ```sql AND (LOWER(DESCRIPTION_TEXT) ~ ('\\y' || $4 || '\\y'))

benard344444 Sep 9 at 18:27

The key points: Use \\y for word boundaries in PostgreSQL (not \b) Concatenate the parameter with the word boundary markers The parameter $4 should contain just the word (e.g., 'den'), not the full pattern If you want to test and debug your regex patterns before using them in PostgreSQL, I find orbit2x.com/regex-tester helpful for validating the pattern logic. You can test patterns like \yden\y to see exactly what they match. Alternative approach using SIMILAR TO:

Zegarek Sep 9 at 20:06

Both OP and the author of this answer use the right word boundary constraint escape syntax. '\y' is correct. '\\y' is just a y preceded by a literal backslash: dbfiddle.uk/TUD0Ti7U Nobody's suggesting '\b' which I think would be just a backspace.

Collectives™ on Stack Overflow

postgres: parameterized query with pattern matching

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related