"Michael Miyabara-McCaskey" <mykarz@miyabara.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