Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBM66m667049 for ; Fri, 22 Dec 2000 01:06:48 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id eBM66SC04057; Fri, 22 Dec 2000 01:06:28 -0500 (EST) To: mykarz@miyabara.com cc: Pgsql-sql@postgresql.org Subject: Re: regcomp failed with error empty (sub)expression? In-reply-to: <005b01c06bd9$50c7a3b0$c700a8c0@ncc1701e> References: <005b01c06bd9$50c7a3b0$c700a8c0@ncc1701e> Comments: In-reply-to "Michael Miyabara-McCaskey" message dated "Thu, 21 Dec 2000 21:37:46 -0800" Date: Fri, 22 Dec 2000 01:06:27 -0500 Message-ID: <4054.977465187@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200012/259 X-Sequence-Number: 596 "Michael Miyabara-McCaskey" 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