Appendix C: PostgreSQL Database Special Characters and Reserved Words

The PostgreSQL implementation utilizes Full-Text Search (FTS), which is a linguistic search function. This function identifies search terms based on their linguistic roots (stems) rather than simple character matching. For example, a search for "requirements" will naturally find records containing "requirement" or "required."

The Override Advanced Shopper Search Syntax (SHOP_OVERRIDE_ADV_SEARCH_SYNTAX) parameter on the Application Parameters (APPCTRL) page, when set to True, is used to define the logic used on the Shopper (SHOP) page for the ‘Search For’ search string. For a PostgreSQL database, the system automatically handles special characters used in search safely. Additionally, the system looks at the Shop Search Characters to Escape (SHOP_SEARCH_ESCAPE_CHARS) parameter to identify specific characters that should be escaped with a backslash (\) to ensure they are processed correctly as part of the search string.

Special Characters:

Character

Logical Function

Expanded Information

"

Exact Phrase Search

Enclosing search terms in double quotes, such as "OFFICE SUPPLIES", directs the system to look for that exact sequence of characters.

()

Evaluation Grouping

Used to group terms and operators together to control the order in which they are evaluated.

Searching (PRINTER | PAPER) & OFFICE finds records containing the word "OFFICE" that also contain either the word "PRINTER" or the word "PAPER."

*

Suffix Wildcard

PostgreSQL FTS supports the asterisk as a suffix to find words beginning with a specific prefix. For example, OFFICE* finds "OFFICE," "OFFICER," and "OFFICIAL." 

Note: Leading wildcards (for example, *PAPER) are not currently supported by the FTS function.

\

Single Escape

Used to escape a single character or symbol such that it is treated as literal text.

Searching TEST\-DOC treats the hyphen as part of the text rather than a delimiter.

Boolean Operators

Operator

Logical Function

Expanded Information

AND

And

By default, the system searches for occurrences of each query term provided.

Searching OFFICE SUPPLIES (or OFFICE & SUPPLIES) requires both words to be present.

OR

Or

Used to search for transactions that contain at least one occurrence of any of the query items.

Searching PRINTER | PAPER finds records containing either the word "PRINTER" or the word "PAPER" (or both).

NOT

And Not

Used to search for transactions that contain one query term but specifically exclude those containing a second term.

Searching PAPER & !OFFICE finds paper records but excludes those mentioning office.

NEAR

Proximity

Returns a search score based on how close two or more query items are physically located to each other.

Searching DOC <-> COMM finds records where "DOC" and "COMM" are immediately adjacent.

Linguistic Functions

Functionality

PostgreSQL behavior

Expanded Information

Inflectional Search

Automatic (Stemming)

The system automatically searches for different tenses, singular forms, and plural forms of a word (for example, "mouse" and "mice").

Searching RUN matches records with "running," "runs," and "ran."

Weighting

Term Scoring

The system calculates a relevancy score (rank) for each record found based on how well the search terms match the content.

Searching PAPER returns "White Paper" as a higher rank than a description containing "paperclip."

Precedence Order

Search operators follow a standard precedence order, which can be altered using the grouping characters ( ) and [ ].

  • NOT (!)

  • Proximity / NEAR (<->)

  • AND (&)

  • OR (|)