Carraige Return issues

On Thu, 2002-07-04 at 16:15, caroline kenny wrote:

I hope that someone outthere can help me. I am trying to insert the text
from a file into a database. The file contains 2 carraige returns in it.
When i go to retrieve this this information from the specific field in the
database, the carraige return seems to have disappeared and the lines just
read one after the other...does anyone know how i can overcome this problem

You need to escape the newline characters.

Here's an example of copying multiline text from a file

junk-# \! cat /tmp/ol
1 line 1 of text\
\
\
line4 of text
\.

junk=# copy tt from '/tmp/ol';
COPY
junk=# select * from tt;
id | t
----+--------------------------------
1 | line 1 of text

line4 of text
(1 row)

Oliver Elphick

Thanks Oliver for your help....but I'm still having a small issue..

What Im really trying to do is to insert/update a field in a particular
table through perl script.
When i run the perl script to insert the specifics into my table...i want
that the file that to be entered into the specific field will be put in with
its carriage returns....can u help further
Cheers!
Caroline

From: Oliver Elphick <olly@lfix.co.uk>
To: caroline kenny <caroline_kenny@hotmail.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Carraige Return issues
Date: 05 Jul 2002 10:48:15 +0100

On Thu, 2002-07-04 at 16:15, caroline kenny wrote:

I hope that someone outthere can help me. I am trying to insert the text
from a file into a database. The file contains 2 carraige returns in it.
When i go to retrieve this this information from the specific field in

the

database, the carraige return seems to have disappeared and the lines

just

read one after the other...does anyone know how i can overcome this

problem

You need to escape the newline characters.

Here's an example of copying multiline text from a file

junk-# \! cat /tmp/ol
1 line 1 of text\
\
\
line4 of text
\.

junk=# copy tt from '/tmp/ol';
COPY
junk=# select * from tt;
id | t
----+--------------------------------
1 | line 1 of text

line4 of text
(1 row)

Oliver Elphick

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

On Fri, 2002-07-05 at 11:51, caroline kenny wrote:

Thanks Oliver for your help....but I'm still having a small issue..

What Im really trying to do is to insert/update a field in a particular
table through perl script.
When i run the perl script to insert the specifics into my table...i want
that the file that to be entered into the specific field will be put in with
its carriage returns....can u help further

So you're using UPDATE or INSERT called through your Perl script?

You need to replace newline with \n in the data being inserted or escape
the linefeeds with \. You also need to escape any real backslash with
an extra backslash and you need to escape single-quotes, either with a
backslash or an extra single-quote.

junk=# insert into tt values (3,'Line 1 of ''some\ntext with\
junk'# linefeeds in it\n and \\s too.');
INSERT 27417478 1
junk=# select t from tt where id = 3;
t
--------------------------------------------------------
Line 1 of 'some
text with
linefeeds in it
and \s too.
(1 row)

If you're using Windows, you may also have to deal with their use of
CRLF as a line ending, as opposed to Unix's LF, but all that can be done
in Perl.

The Perl coding is left as an exercise for the reader - meaning I can't
work it out without looking things up!

Oliver