2013/3/7 Graham Leggett <minfrin@sharp.fm>:
(...)
>> Which psql version are you using, and what is the table definition?
>
> Version as below, from RHEL6:
>
> psql (PostgreSQL) 8.4.13
Aha, there is your problem:
testdb=# SELECT version();
version
-----------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.16 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(SUSE Linux) 4.7.1 20120723 [gcc-4_7-branch revision 189773], 64-bit
(1 row)
testdb=# \set content `cat /tmp/hello.txt`
testdb=# CREATE TABLE interpolation (value TEXT);
CREATE TABLE
testdb=# INSERT INTO interpolation VALUES (:'content');
ERROR: syntax error at or near ":"
LINE 1: INSERT INTO interpolation VALUES (:'content');
I.e. that syntax is not supported in 8.4. You'll need to do this:
\set content ''''`cat /tmp/certificates.txt`''''
patricia=# update property set value = :content where key =
'patricia.home.security.cacerts';
See:
http://www.postgresql.org/docs/8.4/interactive/app-psql.html#AEN71586
(...)
>
>> Does the same error occur if you attempt to insert data from a
>> different text file?
>
> I haven't tried. This is a long blob of PEM encoded certificates, so trying to narrow down a troublesome character
willbe hard.
I was thinking more along the lines of using a small text file to
identify whether the problem is with the data, or something else preventing
you use this syntax (which as it turns out is the PostreSQL version).
> Is there some kind of restriction on character data that can be imported into psql?
AFAIK only NUL bytes can't be imported this way, see:
http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-INTERPOLATION
Regards
Ian Barwick
