Thread: Text data type doesn't accept newlines?
I have a logging database that logs errors. The error messages contain newlines. Pgsql doesn't accept them on insert in a text data field. MySQL has the 'blob' data type which does accept newlines. Do I have to convert all newlines to '\n' to use them in Pgsql? -- Randy Perry sysTame Mac Consulting/Sales
> I have a logging database that logs errors. The error messages contain > newlines. Pgsql doesn't accept them on insert in a text data field. I have never had a problem storing newlines in a text field. What interface are you using? The only ascii character that I have found I have to escape is the single quote character. --------- Bryan White
Randall Perry wrote:
> I have a logging database that logs errors. The error messages contain
> newlines. Pgsql doesn't accept them on insert in a text data field.
>
> MySQL has the 'blob' data type which does accept newlines.
>
> Do I have to convert all newlines to '\n' to use them in Pgsql?
I don't see a reason why it shouldn't accept them, and the
last time I stuffed html pages into via libpgtcl it worked
IIRC.
What interface does your client use?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
on 6/5/01 12:00 PM, Bryan White at bryan@arcamax.com wrote:
>> I have a logging database that logs errors. The error messages contain
>> newlines. Pgsql doesn't accept them on insert in a text data field.
>
> I have never had a problem storing newlines in a text field. What interface
> are you using?
> The only ascii character that I have found I have to escape is the single
> quote character.
> ---------
> Bryan White
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
I'm using the Pg perl interface. But, think my problem was that I had
unescaped single quotes in the string. Added the following to my code to
escape them and it works now:
$self->{errors} =~ s"'"\\'"g; # escape single quotes
--
Randy Perry
sysTame
Mac Consulting/Sales
Randall Perry writes:
> I have a logging database that logs errors. The error messages contain
> newlines. Pgsql doesn't accept them on insert in a text data field.
I don't think so.
peter=# create table test1 (a text);
CREATE
peter=# insert into test1 values ('with
peter'# newline');
INSERT 145809 1
peter=# select * from test1;
a
--------------
with
newline
(1 row)
Please make a more detailed report if you have a problem.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Are you using the "quote" function? You have to use it if you are to
guarantee that the data will be acceptable as "input".
$myVar = $myDB -> quote ($myVar)
> I'm using the Pg perl interface. But, think my problem was that I
had
> unescaped single quotes in the string. Added the following to my
code to
> escape them and it works now:
>
> $self->{errors} =~ s"'"\\'"g; # escape single quotes
Just checked the Pg docs, don't see a quote function. What is it part of?
> Are you using the "quote" function? You have to use it if you are to
> guarantee that the data will be acceptable as "input".
>
> $myVar = $myDB -> quote ($myVar)
>
>
>> I'm using the Pg perl interface. But, think my problem was that I
> had
>> unescaped single quotes in the string. Added the following to my
> code to
>> escape them and it works now:
>>
>> $self->{errors} =~ s"'"\\'"g; # escape single quotes
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Randy Perry
sysTame
Mac Consulting/Sales
phn 561.589.6449
mobile email help@systame.com
Not sure, but the syntax is as I described below. Try checking the
perl DBD::Pg documentation. I think that's where I read about it
originally, many moons ago.
> Just checked the Pg docs, don't see a quote function. What is it
part of?
>
>
> > Are you using the "quote" function? You have to use it if you are
to
> > guarantee that the data will be acceptable as "input".
> >
> > $myVar = $myDB -> quote ($myVar)
> >
> >
> >> I'm using the Pg perl interface. But, think my problem was that I
> > had
> >> unescaped single quotes in the string. Added the following to my
> > code to
> >> escape them and it works now:
> >>
> >> $self->{errors} =~ s"'"\\'"g; # escape single quotes
> >
> >
> >
> >
> > ---------------------------(end of
broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
> --
> Randy Perry
> sysTame
> Mac Consulting/Sales
>
> phn 561.589.6449
> mobile email help@systame.com
>
>
>
>
>
On Wed, Jun 06, 2001 at 09:24:19AM -0400, Randall Perry wrote: > Just checked the Pg docs, don't see a quote function. What is it part of? It's part of DBI, a system for connecting Perl to databases. You can use DBI with Postgres -- but apparently you're not doing this, since you mentioned you're using Pg.pm (the plain interface to libpq). The Postgres driver for DBI is called DBD::Pg, BTW -- make sure you don't get them confused. So the advice below WRT using 'quote' doesn't apply. BTW, you might want to checkout DBI -- I find it to be more pleasant to use than Pg.pm Cheers, Neil > > Are you using the "quote" function? You have to use it if you are to > > guarantee that the data will be acceptable as "input". > > > > $myVar = $myDB -> quote ($myVar)
Gordan Bobic wrote: > > Not sure, but the syntax is as I described below. Try checking the > perl DBD::Pg documentation. I think that's where I read about it > originally, many moons ago. > > > Just checked the Pg docs, don't see a quote function. What is it > part of? > > For the sake of future generations, it's in 'man DBI', which makes sense, because it's useful for those other databases as well. :-)