> 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)