Re: [GENERAL] ERROR: syntax error at or near ":"
2013/3/7 Graham Leggett : (...) >> 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 will be 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near ":"
Thanks for the link / explanation -- hadn't seen this use before.
GW
- Original Message -
> From: Adrian Klaver
> To: Greg Williamson
> Cc: "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
>>> To: "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.kla...@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
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near ":"
On 07 Mar 2013, at 1:05 AM, Ian Lawrence Barwick 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
--
smime.p7s
Description: S/MIME cryptographic signature
Re: [GENERAL] ERROR: syntax error at or near ":"
On 03/06/2013 03:04 PM, Greg Williamson wrote:
Graham --
From: Graham Leggett
To: "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.kla...@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
Re: [GENERAL] ERROR: syntax error at or near ":"
Greg,
2013/3/7 Greg Williamson :
>
>
> 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 not coming 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near ":"
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. 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... ^ 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.kla...@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
Re: [GENERAL] ERROR: syntax error at or near ":"
2013/3/7 Graham Leggett : > 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... 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near ":"
Graham --
>
> From: Graham Leggett
>To: "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.
Greg W.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near "IF"... why?
On Wed, Apr 29, 2009 at 07:54:20AM -0700, DaNieL wrote: > ERROR: syntax error at or near "IF" > > Where am i mistaken? > > p.s: dont focus on the example functionality, its just a trial for me > to understand the transactions.. and now, the IF clause... As others have said, IF statements are only valid in plpgsql functions, not in plain SQL. Probably not relevant but in this example can work around it by doing something like: CREATE FUNCTION failif(BOOL,TEXT) RETURNS VOID AS $$ BEGIN IF $1 THEN RAISE EXCEPTION '%', $2; END IF; END $$ LANGUAGE plpgsql; BEGIN; INSERT INTO ... UPDATE ... UPDATE ... SELECT failif((SELECT credit FROM users WHERE name = 'mary') < 0, 'error, credit can't be less than zero'); COMMIT; In general, you're probably better off writing the whole thing in plpgsql. Hope that helps. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near "IF"... why?
Could if be referencing the second IF..the one in your "END IF" that doesn't
have a semicolon after it...?
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of DaNieL
Sent: Wednesday, April 29, 2009 9:54 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] ERROR: syntax error at or near "IF"... why?
Hi guys, im new with postgresql, and already got my first problem..
Well, I wroted some code for understend how the transaction works,
following step by step the manual.
TO make it short, i've created 2 tables, user and movements: in the
firs one there are the name, email and credit colons, in the second
the colons from, to, import.
So, i was triyng that way:
BEGIN;
INSERT INTO movements (from, to, import) VALUES ('mary', 'steve',
600);
UPDATE users SET credit = credit - 600 WHERE name = 'mary';
UPDATE users SET credit = credit + 600 WHERE name = 'steve';
--here comes the problem!
IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN
ROLLBACK;
END IF
COMMIT;
i always get the error
ERROR: syntax error at or near "IF"
Where am i mistaken?
p.s: dont focus on the example functionality, its just a trial for me
to understand the transactions.. and now, the IF clause...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near "IF"... why?
Daniel, IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN ROLLBACK; END IF COMMIT; i always get the error ERROR: syntax error at or near "IF" Where am i mistaken? SELECT returns in essence a record or setof records. DECLARE _credit int; ... SELECT credit FROM users WHERE name = 'mary' INTO _credit; IF _credit < 0 THEN ROLLBACK; END IF; If there is a chance that the select returns more than one record you can do something similar to: DECLARE rec record; ... FOR rec IN (SELECT credit FROM users WHERE name = 'mary' LOOP IF rec.credit < 0 THEN ... ELSE ... END IF; END LOOP; HTH, Johan Nel Pretoria, South Africa. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near "IF"... why?
On Thu, Apr 30, 2009 at 1:45 AM, DaNieL..! wrote: >> >> > IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN >> > ROLLBACK; >> > END IF >> > COMMIT; >> >> > i always get the error >> > ERROR: syntax error at or near "IF" >> if you're inside a server-side function then you cannot use COMMIT nor ROLLBACK; if you aren't inside a server-side function then you cannot use IF -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near "IF"... why?
DaNieL..! wrote: I tryed the declare, before and after the BEGIN;, but allways returns me the error: --- ERROR: syntax error at or near "int"; LINE 1: DECLARE _mycredit int; ^ --- For the if statement, i've tryed that IF 2 = 2 THEN ROLLBACK; END IF but still the error near the "IF".. dunno.. in mysql the if can be used in that way.. in postgres no? Sorry was thinking FUNCTION way of doing it. DECLARE can only be used in a stored procedure/function. Johan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near "IF"... why?
On 30/04/2009 07:45, DaNieL..! wrote: > I tryed the declare, before and after the BEGIN;, but allways returns > me the error: > --- > ERROR: syntax error at or near "int"; > LINE 1: DECLARE _mycredit int; >^ > --- I missed what came before in this thread, but in plpgsql functions the DECLARE comes before BEGIN: create or replace function my_function() returns as $$ declare begin return end; $$ language plpgsql; Can you show us the full function code again please? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: syntax error at or near "IF"... why?
On 30 Apr, 07:30, Johan Nel wrote: > Daniel, > > > IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN > > ROLLBACK; > > END IF > > COMMIT; > > > i always get the error > > ERROR: syntax error at or near "IF" > > > Where am i mistaken? > > SELECT returns in essence a record or setof records. > > DECLARE _credit int; > ... > SELECT credit FROM users WHERE name = 'mary' INTO _credit; > IF _credit < 0 THEN > ROLLBACK; > END IF; > > If there is a chance that the select returns more than one record you > can do something similar to: > DECLARE rec record; > ... > FOR rec IN (SELECT credit FROM users WHERE name = 'mary' > LOOP > IF rec.credit < 0 THEN > ... > ELSE > ... > END IF; > END LOOP; > > HTH, > > Johan Nel > Pretoria, South Africa. I tryed the declare, before and after the BEGIN;, but allways returns me the error: --- ERROR: syntax error at or near "int"; LINE 1: DECLARE _mycredit int; ^ --- For the if statement, i've tryed that IF 2 = 2 THEN ROLLBACK; END IF but still the error near the "IF".. dunno.. in mysql the if can be used in that way.. in postgres no? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] error : syntax error at or near $1 for over select rows
On Dec 27, 2004, at 11:36 AM, vinita bansal wrote: FOR for1 in select qi.tril_gid as vQuotaInstanceGID,qi.startdate as vQIStartDate,qi.enddate as vQIEndDate from cm_quotainstance as qi, cm_quota as q, fs_agr as a where a.fs_unid = AGR_UNID and a.fs_model = q.model and qi.quota = q.tril_gid LOOP if (vQIStartDate > M_COMM_CLOSE_DATE OR vQIEndDate <= M_COMM_CLOSE_DATE) then update cm_quotainstance set changedate = DEFAULT_LOWEST_DATE where tril_gid = vQuotaInstanceGID; end if; END LOOP; for1 is a record type from which you can access the other columns. So with something like FOR for1 in select * from cm_quotainstance where ... loop then you can access the columns in your subsequent if statements like: if (for1.startdate > > M_COMM_CLOSE_DATE OR for1.enddate <= M_COMM_CLOSE_DATE) ... Look in the plpgsql section of the documentation under "Looping Through Query Results" -- this is section 36.7.4 in the 8.0 documentation. Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
