Thread: Carraige Return issues

Users!
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
Thanks
Caroline


_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx

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