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

On 07 Mar 2013, at 1:05 AM, Ian Lawrence Barwick <barwick(at)gmail(dot)com> wrote:

>> Can anyone explain what might be going wrong, and what I should do instead?
>>
>> patricia=# \set content `cat /tmp/certificates.txt`
>> patricia=# update property set value = :'content' where key = 'patricia.home.security.cacerts';
>> ERROR: syntax error at or near ":"
>> LINE 1: update property set value = :'content' where key = 'patricia...
>
> That should work…

I have used this before the last time I needed to do this, and it worked then. No idea why it doesn't work now, and the error message is of no help. Is there a log file or some kind of forensic debugging that I can switch on to coax some kind of useful out from psql?

> Which psql version are you using, and what is the table definition?

Version as below, from RHEL6:

psql (PostgreSQL) 8.4.13
contains support for command-line editing

patricia=# \d property
Table "public.property"
Column | Type | Modifiers
-------------+-------------------+------------------------------------------------------------
property_id | integer | not null default nextval(('property_SEQ'::text)::regclass)
key | character varying | not null
value | character varying |
Indexes:
"property_pkey" PRIMARY KEY, btree (property_id)
"property_index" btree (key)

> 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 will be hard.

Is there some kind of restriction on character data that can be imported into psql?

Regards,
Graham
--