Thread: PostgreSQL help

Hey!!

I have a problem and i hope this is the correct section to post it!!!

When i use the COPY  Table Name FROM Location command to insert values to a 
table using a txt file, the programme gives me errors when he finds letter 
as "ò, è, à" inside the txt file.
But when i use the insert command and I write some word with any of the 
letters it seems to work. Why is that ?? What can i do to resolve it??

i use the programme PgAdmin 3 to write my commands...

Thank you. 

Check your database encoding, client encoding, and the encoding you use in  
your file. If your database is UNICODE, pgadmin will convert accordingly,  
but your file has to be in the right encoding.


On Mon, 29 Aug 2005 12:27:41 +0200, Shavonne Marietta Wijesinghe  
<shavonne.marietta@studioform.it> wrote:

> Hey!!
>
> I have a problem and i hope this is the correct section to post it!!!
>
> When i use the COPY  Table Name FROM Location command to insert values  
> to a table using a txt file, the programme gives me errors when he finds  
> letter as "ò, è, à" inside the txt file.
> But when i use the insert command and I write some word with any of the  
> letters it seems to work. Why is that ?? What can i do to resolve it??
>
> i use the programme PgAdmin 3 to write my commands...
>
> Thank you.  
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

I'd like a regex that matches 'CD' but not 'ABCD' in any part of the regex.

In Perl I'd use a negative lookbehind assertion (?<!AB)CD to do the job:

$ cat lb.pl
print "CD: ",   'CD'   =~ /(?<!AB)CD/, "\n";
print "XYCD: ", 'XYCD' =~ /(?<!AB)CD/, "\n";
print "ABCD: ", 'ABCD' =~ /(?<!AB)CD/, "\n";

$ perl lb.pl
CD: 1
XYCD: 1
ABCD:

But Postgresql (7.4) doesn't seem to like that:

# select 'ABCD' ~ '(?<!AB)CD';
ERROR:  invalid regular expression: quantifier operand invalid

Is there a workaround that allows me to do this as a single regex?

I know I could and together a ~ and !~ like this

# select ('CD' ~ 'CD') and ('CD' !~ 'ABCD');?column?
----------t
(1 row)

# select ('ABCD' ~ 'CD') and ('ABCD' !~ 'ABCD');?column?
----------f
(1 row)

but changing the SQL would break the existing paradigm.

TIA

Julian 

On Mon, Aug 29, 2005 at 14:11:37 +0100, Julian Scarfe <julian@avbrief.com> wrote:
> I'd like a regex that matches 'CD' but not 'ABCD' in any part of the regex.
> 
> Is there a workaround that allows me to do this as a single regex?
> 
> I know I could and together a ~ and !~ like this
> 
> # select ('CD' ~ 'CD') and ('CD' !~ 'ABCD');
> ?column?
> ----------
> t
> (1 row)
> 
> # select ('ABCD' ~ 'CD') and ('ABCD' !~ 'ABCD');
> ?column?
> ----------
> f
> (1 row)

The above code won't work because there could be both CD and ABCD in the
string. What you want to do is match all of the valid possibilities.
Something like:
(^.?CD)|([^B]CD)|([^A]BCD)

>> I'd like a regex that matches 'CD' but not 'ABCD' in any part of the 
>> regex.

From: "Bruno Wolff III" <bruno@wolff.to>

> Something like:
> (^.?CD)|([^B]CD)|([^A]BCD)

Thanks to Bruno, and to Dawid who replied offline.  The above does the job 
nicely.

Any plans for a Perl Compatible Regular Expression operator? 
http://www.pcre.org/
Or are two regex operators enough?

Julian

On Sat, Sep 03, 2005 at 03:59:18PM +0100, Julian Scarfe wrote:

> Any plans for a Perl Compatible Regular Expression operator? 
> http://www.pcre.org/

Why have Perl-Compatible things when you can have Perl itself?  Just use
plperl.

> Or are two regex operators enough?

Only two?  We have not only our own code, but also Perl's engine,
Python's, Tcl's, and PHP's, available through the PLs.  That's more than
enough, I'd say.

-- 
Alvaro Herrera -- Valdivia, Chile         Architect, www.EnterpriseDB.com
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)