I've been running into problems with some characters that I believe
can be solved using bytea variable type instead of varchar()
I'm picking up data from email and trying to put it into a table.
I'm trying to "merge" two different types of SQL and I'm really not
sure how this can be done...
I had a previous version of my SQL that looked like:
my $sth = $dbh->prepare("insert into quarantine values (?,?)");
$sth->bind_param(1, $idx);
$sth->bind_param(2, $text, { pg_type => DBD::Pg::PG_BYTEA });
$sth->execute();
In this case I was inserting an entire email content into the second
parameter as type bytea. Originally I was doing this as text and
running into problems inserting records when there were weird characers.
I want to be able to use the BYTEA data type for inserting records
into another SQL that looks like:
my $sql=<<SQL;
insert into tokens (token)
select values.token
from (values TOKEN_LIST_STRING ) as values(token)
left outer join tokens t using (token)
where t.token_idx is null
SQL
NOTE: TOKEN_LIST_STRING is replaced with an escaped list of values of
the format:
VALUES ( ('the'), ('quick'), ('brown'), ('fox'), ('jumped')) as values
(token)
use perl regex.
The details are something like:
my $string = "(E'" . join($glue, map{quotemeta } @$tokens) . "')";
Which will return something like (E'that\s') and (E'char\:escaping\(is
\)\"hard\"') in place of the ('the')
And this too is failing to insert on some weird characters.
I'm not sure which ones because when it does fail, it tends to be one
of 100's and I haven't been able to write a script to test each one.
And I'm not convinced that is the correct way to procede.
Can someone help me become a postgres guru?