postgres - using SIMILAR TO in a CASE clause

postgres case when multiple conditions
select statement inside case in postgresql
postgres case when null
postgresql if statement in where clause
postgres if statement
postgresql case when multiple columns
postgres update case
postgresql nested case

I have a column which contains the string of a scanned barcode. I want to find a specific match and return in a new column the string 'match' and 'noMatch'

The regex for the barcode is


e.g. 13412432%10000%

My query is

report."barcode" SIMILAR TO '[0-9]{5,8}\%[0-9]*\%',
(CASE report."barcode" WHEN (report."barcode" SIMILAR TO '[0-9]{5,8}\%[0-9]*\%') THEN 'match'
ELSE 'noMatch'
END) AS matchColumn
FROM report

However I'm always getting this error

ERROR: operator does not exist: text = boolean LINE 3: (CASE report."barcode" WHEN (report."barcode...

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. SQL state: 42883 Character: 106

I'm fairly new to SQL so when the docs says that it expects a true/false statement after WHEN I figured I could use the SIMIAR TO pattern matching since it returns boolean.

I have it on good authority ( that there is no good reason to use "similar to." From the referenced link:

SIMILAR TO is part of the SQL standard, but it is very odd syntax, and the only reason PostgreSQL supports it, is to stay standard compliant. Internally, every SIMILAR TO expression is rewritten with a regular expression. Therefore, for any given SIMILAR TO expression, there is at least one regexp doing the same job faster.

On that note, if you change this to a normal regular expression it should even be as simple as this:

    when r.barcode ~ '\d{5,8}%\d+%' then 'match'
    else 'noMatch'
  end as matchcolumn
from report r

You don't need to escape the % character with a regex.

If you aren't okay with that pattern occuring in the middle of a string, you may want to anchor the beginning and/or end of the regex:


postgres - using SIMILAR TO in a CASE clause, I have it on good authority ( that there is no good reason to use "similar to." From the referenced  The PostgreSQL CASE expression is the same as IF/ELSE statement in other programming languages. PostgreSQL provides two forms of the CASE expressions. The following illustrates the general form of the CASE statement:

You have an extra reference to the column after the case. The expression you want is:

SELECT report."barcode" SIMILAR TO '[0-9]{5,8}\%[0-9]*\%',
       (CASE WHEN report."barcode" SIMILAR TO '[0-9]{5,8}\%[0-9]*\%' 
             THEN 'match'
             ELSE 'noMatch'
        END) AS matchColumn
FROM report;

CASE Statements in PostgreSQL, in PostgreSQL using the PostgreSQL CASE conditional expression. in PostgreSQL using WHEN-THEN case which is very similar to if-else  But when you are using CASE in pair with a where clause, you need not to follow this (more on this later). The data-type of the results has to be uniform. You cannot return a string in result_1 and an integer in result_2 and so on. Let's dig into some examples now. Writing PostgreSQL CASEs. Let's start simple. You will now write a CASE to group the

you just need to omit report."barcode" after case

          Case --omit this report."barcode
              when  ......

PostgreSQL CASE, The PostgreSQL CASE expression is the same as IF/ELSE statement in other programming languages. postgres=# SELECT country_name postgres-# FROM countries postgres-# WHERE country_name SIMILAR TO 'Italy'='t'; country_name ----- Italy (1 row) Example: If we want to extract those rows which contain the country_name ending with the letter 'y' or 'l' from countries table, the following statement can be used.

Documentation: 9.1: Control Structures, first evaluates the expression and then compares the result with each value( value_1 , value_2 , …) in the WHEN clauses sequentially until it finds the match. The searched CASE statement executes statements based on the result of Boolean expressions in each WHEN clause. PostgreSQL evaluates the Boolean expressions sequentially from top to bottom until one expression is true. Then the evaluation stops and the corresponding statement are executed.

PostgreSQL COALESCE, function evaluates arguments from left to right until it finds the first non-null argument. Searches using SIMILAR TO patterns have the same security hazards, since SIMILAR TO provides many of the same capabilities as POSIX-style regular expressions. LIKE searches, being much simpler than the other two options, are safer to use with possibly-hostile pattern sources.

PL/pgSQL CASE Statements, Besides the IF statement, PostgreSQL provides you with CASE statements Notice that the searched case statement is similar to the IF ELSIF ELSE statement​. The PostgreSQL LIKE operator helps us to match text values against patterns using wildcards. It is possible to match the search expression to the pattern expression. If a match occurs, the LIKE operator returns true. With the help of LIKE operator, it is possible to use wildcards in the WHERE clause of SELECT, UPDATE, INSERT or DELETE statements.