Обсуждение: ERROR: syntax error at or near ":"
Hi all, I have a text file, and I need to update the value of an element in a table with the contents of this text file. Followingthe instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-fileI tried this, but get the errorbelow, which I do not understand. 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... ^ Regards, Graham --
Вложения
Graham -- >________________________________ > From: Graham Leggett <minfrin@sharp.fm> >To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> >Sent: Wednesday, March 6, 2013 2:41 PM >Subject: [GENERAL] ERROR: syntax error at or near ":" > >Hi all, > >I have a text file, and I need to update the value of an element in a table with the contents of this text file. Followingthe instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-fileI tried this, but get the errorbelow, which I do not understand. > >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... > ^ > The colon (":") is not needed, just remove it. A pair of colons is used to indicate a cast of a value; off hand I am notcoming up with any use of a colon in basic SQL. Greg W.
2013/3/7 Graham Leggett <minfrin@sharp.fm>: > Hi all, > > I have a text file, and I need to update the value of an element in a table with the contents of this text file. Followingthe instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-fileI tried this, but get the errorbelow, which I do not understand. > > 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... Which psql version are you using, and what is the table definition? Does the same error occur if you attempt to insert data from a different text file? Regards Ian Barwick
On 03/06/2013 02:41 PM, Graham Leggett wrote: > Hi all, > > I have a text file, and I need to update the value of an element in a table with the contents of this text file. Followingthe instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-fileI tried this, but get the errorbelow, which I do not understand. > > 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... > ^ Worked here for me. Is the field you are trying to set really named value? Even though VALUE is marked non-reserved here: http://www.postgresql.org/docs/9.2/interactive/sql-keywords-appendix.html you might want to follow the advice from above link: " As a general rule, if you get spurious parser errors for commands that contain any of the listed key words as an identifier you should try to quote the identifier to see if the problem goes away. " > > Regards, > Graham > -- > -- Adrian Klaver adrian.klaver@gmail.com
Greg,
2013/3/7 Greg Williamson <gwilliamson39@yahoo.com>:
>
>
> Graham --
(...)
> The colon (":") is not needed, just remove it. A pair of colons is used to indicate a cast of a value; off hand I am
notcoming up with any use of a colon in basic SQL.
This is psql-specific syntax; the colon should cause the value of the
psql variable 'content' to be interpreted; without it, the string
'content' would be inserted.
See:
http://www.postgresql.org/docs/current/interactive/app-psql.html#APP-PSQL-INTERPOLATION
Regards
Ian Barwick
On 03/06/2013 03:04 PM, Greg Williamson wrote: > > > Graham -- > >> ________________________________ >> From: Graham Leggett <minfrin@sharp.fm> >> To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> >> Sent: Wednesday, March 6, 2013 2:41 PM >> Subject: [GENERAL] ERROR: syntax error at or near ":" >> >> Hi all, >> >> I have a text file, and I need to update the value of an element in a table with the contents of this text file. Followingthe instructions at http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-fileI tried this, but get the errorbelow, which I do not understand. >> >> 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... >> ^ >> > The colon (":") is not needed, just remove it. A pair of colons is used to indicate a cast of a value; off hand I am notcoming up with any use of a colon in basic SQL. http://www.postgresql.org/docs/9.2/interactive/app-psql.html#APP-PSQL-INTERPOLATION > > Greg W. > > > -- Adrian Klaver adrian.klaver@gmail.com
On 07 Mar 2013, at 1:05 AM, Ian Lawrence Barwick <barwick@gmail.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
errormessage is of no help. Is there a log file or some kind of forensic debugging that I can switch on to coax some
kindof 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
behard.
Is there some kind of restriction on character data that can be imported into psql?
Regards,
Graham
--
Вложения
Thanks for the link / explanation -- hadn't seen this use before. GW ----- Original Message ----- > From: Adrian Klaver <adrian.klaver@gmail.com> > To: Greg Williamson <gwilliamson39@yahoo.com> > Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> > Sent: Wednesday, March 6, 2013 3:13 PM > Subject: Re: [GENERAL] ERROR: syntax error at or near ":" > > On 03/06/2013 03:04 PM, Greg Williamson wrote: >> >> >> Graham -- >> >>> ________________________________ >>> From: Graham Leggett <minfrin@sharp.fm> >>> To: "pgsql-general@postgresql.org" > <pgsql-general@postgresql.org> >>> Sent: Wednesday, March 6, 2013 2:41 PM >>> Subject: [GENERAL] ERROR: syntax error at or near ":" >>> >>> Hi all, >>> >>> I have a text file, and I need to update the value of an element in a > table with the contents of this text file. Following the instructions at > http://stackoverflow.com/questions/10968039/postgresql-inserting-value-of-a-column-from-a-file > I tried this, but get the error below, which I do not understand. >>> >>> 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... >>> ^ >>> >> The colon (":") is not needed, just remove it. A pair of colons > is used to indicate a cast of a value; off hand I am not coming up with any use > of a colon in basic SQL. > > http://www.postgresql.org/docs/9.2/interactive/app-psql.html#APP-PSQL-INTERPOLATION > >> >> Greg W. >> >> >> > > > -- > Adrian Klaver > adrian.klaver@gmail.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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