Regex to find complete words at Postgresql

Question

I want to only get the records that have some words at one column, I have tried using WHERE ... IN (...) but Postgres is case sensitive in this where clause. This is why I tried regex and ~* operator.

The following is a SQL snippet that returns all the columns and tables from the DB, I want to restrict the rows to bring only the tables in the regex expresion.

SELECT ordinal_position as COLUMN_ID, TABLE_NAME, COLUMN_NAME
                        FROM information_schema.columns
                        WHERE table_schema = 'public' and  table_name ~* 'PRODUCTS|BALANCES|BALANCESBARCODEFORMATS|BALANCESEXPORTCATEGORIES|BALANCESEXPORTCATEGORIESSUB'
                        order by TABLE_NAME, COLUMN_ID

This regex will bring all the columns of BALANCES and the columns of the tables that contain the 'BALANCES' keyword.

I want to restrict the result to complete names only.

Answer 1

Using regexes, the common solution is using word boundaries before and after the current expression.

See effect without: http://regexr.com?35ecl

See effect with word boundaries: http://regexr.com?35eci

In PostgreSQL, the word boundaries are denoted by \y ^{(other popular regex engines, such as PCRE, C# and Java, use \b instead - thus its use in the regex demo above - thanks @IgorRomanchenko)}.

Thus, for your case, the expression below could be used ^{(the matches are the same as the example regexes in the links above)}:

'\y(PRODUCTS|BALANCES|BALANCESBARCODEFORMATS|BALANCESEXPORTCATEGORIES|BALANCESEXPORTCATEGORIESSUB)\y'

See demo of this expression in use here: http://sqlfiddle.com/#!12/9f597/1

Answer 2

If you want to match only whole table_name use something like

'^(PRODUCTS|BALANCES|BALANCESBARCODEFORMATS|BALANCESEXPORTCATEGORIES|BALANCESEXPORTCATEGORIESSUB)$'

^ matches at the beginning of the string.

$ matches at the end of the string.

Details here.

Alternatively you can use something like:

 upper(table_name) IN ('PRODUCTS','BALANCES','BALANCESBARCODEFORMATS','BALANCESEXPORTCATEGORIES', ...)

to make IN case insensitive.