Thread: inserting/updating a field with the contents of a text file

Greetings,
I've googled a bit on this, and didn't find any clear answers.  I've
got a table with several columns, one of which I'd like to hold the
contents of text files.  What I tried doing was using psql to do
something like:
psql -q -d database0 -h server -c "UPDATE table set info='`cat
/tmp/file.txt`' where id=3;"

and this almost works.  The problem is that whenever there are
carriage returns in file.txt, the rest of the file contents never get
inserted (i only get the first line).

It seems like i'd need to somehow escape out the carriage returns, but
I"m not sure how to do this without altering the contents of file.txt.

thanks for any pointers.

> psql -q -d database0 -h server -c "UPDATE table set info='`cat
> /tmp/file.txt`' where id=3;"
>
> and this almost works.  The problem is that whenever there are
> carriage returns in file.txt, the rest of the file contents never get
> inserted (i only get the first line).

Not sure what's inside your file, but the CRs are not your problem. What
you do have to be concerned about escaping are any single quotes. You
can do that with whatever your favorite search and replace utility is
(e.g. ...-c"insert into table (info) values('`sed "s/'/''/g"
file.txt`')" )

Just so you know I am not making it up on the CRs here's an example:

% echo "abc
dquote> def
dquote> ghi" > x.txt
% cat x.txt
abc
def
ghi
% psql -dfoo -c"create table test (a text)"
CREATE TABLE
% psql -dfoo -c"insert into test values('`cat x.txt`')"
INSERT 0 1
% psql -dfoo -c"select * from test"
  a
-----
 abc
def
ghi
(1 row)
% unix2dos x.txt
unix2dos: converting file x.txt to DOS format ...
% psql -dfoo -c"insert into test values('`cat x.txt`')"
INSERT 0 1
% psql -dfoo -c"select * from test"
  a
-----
 abc
def
ghi
 abc
def
ghi
(2 rows)

On 9/24/07, George Pavlov <gpavlov@mynewplace.com> wrote:
> > psql -q -d database0 -h server -c "UPDATE table set info='`cat
> > /tmp/file.txt`' where id=3;"
> >
> > and this almost works.  The problem is that whenever there are
> > carriage returns in file.txt, the rest of the file contents never get
> > inserted (i only get the first line).
>
> Not sure what's inside your file, but the CRs are not your problem. What
> you do have to be concerned about escaping are any single quotes. You
> can do that with whatever your favorite search and replace utility is
> (e.g. ...-c"insert into table (info) values('`sed "s/'/''/g"
> file.txt`')" )
>
> Just so you know I am not making it up on the CRs here's an example:

Thanks, that appears to have been the issue.


--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama@gmail.com
LlamaLand                       https://netllama.linux-sxs.org

--- Lonni J Friedman <netllama@gmail.com> wrote:

> > (e.g. ...-c"insert into table (info) values('`sed "s/'/''/g"
> > file.txt`')" )
> >

if single quotes were your problem, then maybe you could use Postgresql's dollar sign quoting.

you could replace the ' with $myquote$.

On 9/24/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
> --- Lonni J Friedman <netllama@gmail.com> wrote:
>
> > > (e.g. ...-c"insert into table (info) values('`sed "s/'/''/g"
> > > file.txt`')" )
> > >
>
> if single quotes were your problem, then maybe you could use Postgresql's dollar sign quoting.
>
> you could replace the ' with $myquote$.

Actually the issue here is that i'm inserting build log snippets, so
I'm never going to be 100% what will appear.  Single quotes were
definitely the issue the first time I hit this.  Are there any other
characters that are verboten, or just single quotes?


--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama@gmail.com
LlamaLand                       https://netllama.linux-sxs.org

> Actually the issue here is that i'm inserting build log snippets, so
> I'm never going to be 100% what will appear.  Single quotes were
> definitely the issue the first time I hit this.  Are there any other
> characters that are verboten, or just single quotes?

Backslash ('\') will also cause problems. Replace them with double-backslash
('\\')


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

--- Lonni J Friedman <netllama@gmail.com> wrote:

> Actually the issue here is that i'm inserting build log snippets, so
> I'm never going to be 100% what will appear.  Single quotes were
> definitely the issue the first time I hit this.  Are there any other
> characters that are verboten, or just single quotes?

Actually, dollar sign quoting lets you create you own custom quoting character.

for example:

$my_quote$This is where the text goes$my_quote$

in this example (') is equal to ($my_quote$)
http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

Regards,
Richard Broersma Jr.

--- Phillip Smith <phillip.smith@weatherbeeta.com.au> wrote:
> Backslash ('\') will also cause problems. Replace them with double-backslash
> ('\\')

You can turn alter postgresql behavior towards backslash using standard_conforming_strings.

SET standard_conforming_strings = on;

http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION

This way backslashes are treated as any other character.

when you are done:

SET standard_conforming_strings = off;

Regards,
Richard Broersma Jr.

--- Richard Broersma Jr <rabroersma@yahoo.com> wrote:

Here is a test case:

postgres=# SELECT 'This\nis my\ntext';
WARNING:  nonstandard use of escape in a string literal
LINE 1: SELECT 'This\nis my\ntext';
               ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
 ?column?
----------
 This
 is my
 text
(1 row)

postgres=# SELECT $my_quote$This\nis my\ntext$my_quote$;
     ?column?
-------------------
 This\nis my\ntext
(1 row)

postgres=# set standard_conforming_strings = on;
SET
postgres=# SELECT 'This\nis my\ntext';
     ?column?
-------------------
 This\nis my\ntext
(1 row)

postgres=# SELECT $my_quote$This\nis\tmy\ntext$my_quote$;
      ?column?
--------------------
 This\nis\tmy\ntext
(1 row)

postgres=#