Re: ERROR: syntax error at or near ":"

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