Re: regcomp failed with error empty (sub)expression?

"Michael Miyabara-McCaskey" <mykarz(at)miyabara(dot)com> writes:
> regcomp failed with error empty (sub)expression
> Anys thoughts as to what this means?

I'd say it means the regexp code doesn't like your pattern. For
example, I can trigger that with

regression=# select 'abc' ~ '|33';
ERROR: regcomp failed with error empty (sub)expression

'|33' is a bogus pattern because one side of the alternative
construct is empty. ('|' is the OR pattern operator.)

> The following SQL statement generated the error.

> INSERT INTO table3(table1.oid,table2.oid)
> SELECT table1.oid, table2.oid
> FROM table1, table2
> WHERE table1.field1 ~* table2.field1
> table1.field2 ~* table2.field2
> table1.field3 = table2.field3
> table1.field4 = table2.field4;

Not very helpful when we don't know what's in the table2.field1 and
table2.field2 columns.

> I'm basically trying to do a simply comparison between table1 and table2 to
> find duplicates, but the case differences are stopping me, and as the 7.0.3
> version of "upper()" has memory leaks, and the Plperl verion of uc() that I
> created takes A LONG time to run (I have a LOT of records)... here I am
> trying to match strings with CASE differences.

If you are just trying to match without regard to case, the thing to do
is something like "upper(col1) = upper(col2)". Using data as a regexp
or LIKE pattern is sheer folly, unless you are *certain* that it can
never contain pattern-operator characters.

regards, tom lane