Thread: COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
Hello. System: PostgreSQL v9.0 Windows XP SP3 SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary) ERROR: syntax error at or near "binary" LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary) ^ ********** Error ********** ERROR: syntax error at or near "binary" SQL state: 42601 Character: 55 But if I use 'FORMAT text' or 'FORMAT csv' all is OK. Suppose this happens because BINARY is not listed in "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but listed in "type_func_name_keyword" instead. -- With best wishes,Pavel mailto:pavel@gf.microolap.com
Re: COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
From
Alvaro Herrera
Date:
Excerpts from Pavel Golub's message of mar jul 05 10:52:06 -0400 2011: > Hello. > > System: PostgreSQL v9.0 Windows XP SP3 > SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary) > ERROR: syntax error at or near "binary" > LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary) > ^ > > ********** Error ********** > > ERROR: syntax error at or near "binary" > SQL state: 42601 > Character: 55 > > But if I use 'FORMAT text' or 'FORMAT csv' all is OK. > > Suppose this happens because BINARY is not listed in > "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but > listed in "type_func_name_keyword" instead. That seems pretty unfortunate. Of course, it works if you quote it: COPY "tablename" TO STDOUT WITH (FORMAT "binary") I assume it's not in unreserved_keyword because it would cause a shift/reduce conflict elsewhere. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hello, Alvaro. You wrote: AH> Excerpts from Pavel Golub's message of mar jul 05 10:52:06 -0400 2011: >> Hello. >> >> System: PostgreSQL v9.0 Windows XP SP3 >> SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary) >> ERROR: syntax error at or near "binary" >> LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary) >> ^ >> >> ********** Error ********** >> >> ERROR: syntax error at or near "binary" >> SQL state: 42601 >> Character: 55 >> >> But if I use 'FORMAT text' or 'FORMAT csv' all is OK. >> >> Suppose this happens because BINARY is not listed in >> "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but >> listed in "type_func_name_keyword" instead. AH> That seems pretty unfortunate. Of course, it works if you quote it: AH> COPY "tablename" TO STDOUT WITH (FORMAT "binary") AH> I assume it's not in unreserved_keyword because it would cause a AH> shift/reduce conflict elsewhere. Well, there are two ways: 1. Change documentation, so quoted or double quoted values are accepted 2. Fix parser -- With best wishes,Pavel mailto:pavel@gf.microolap.com
Re: [BUGS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
From
Robert Haas
Date:
On Tue, Jul 5, 2011 at 11:06 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Excerpts from Pavel Golub's message of mar jul 05 10:52:06 -0400 2011: >> Hello. >> >> System: PostgreSQL v9.0 Windows XP SP3 >> SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary) >> ERROR: syntax error at or near "binary" >> LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary) >> ^ >> >> ********** Error ********** >> >> ERROR: syntax error at or near "binary" >> SQL state: 42601 >> Character: 55 >> >> But if I use 'FORMAT text' or 'FORMAT csv' all is OK. >> >> Suppose this happens because BINARY is not listed in >> "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but >> listed in "type_func_name_keyword" instead. > > That seems pretty unfortunate. Of course, it works if you quote it: > > COPY "tablename" TO STDOUT WITH (FORMAT "binary") > > I assume it's not in unreserved_keyword because it would cause a > shift/reduce conflict elsewhere. Yeah. In particular, it conflicts with the ancient copy syntax which we still support for backwards compatibility with versions < 7.3. We can fix the immediate problem with something like the attached. (a) Should we do that? (b) Should we back-patch it to 9.1 and 9.0? (c) Should we consider removing compatibility with the ancient copy syntax in 9.2, and de-reserving that keyword? (Given that the workaround is this simple, I'm inclined to say "no", but could be persuaded otherwise.) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Attachment
Re: [BUGS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Jul 5, 2011 at 11:06 AM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
>> I assume it's not in unreserved_keyword because it would cause a
>> shift/reduce conflict elsewhere.
> Yeah. In particular, it conflicts with the ancient copy syntax which
> we still support for backwards compatibility with versions < 7.3. We
> can fix the immediate problem with something like the attached.
> (a) Should we do that?
That seems like a horrid crock ...
regards, tom lane
Re: [BUGS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
From
Andrew Dunstan
Date:
On 07/05/2011 11:23 AM, Robert Haas wrote: > > Yeah. In particular, it conflicts with the ancient copy syntax which > we still support for backwards compatibility with versions< 7.3. We > can fix the immediate problem with something like the attached. > > (a) Should we do that? yes. > (b) Should we back-patch it to 9.1 and 9.0? yes. > (c) Should we consider removing compatibility with the ancient copy > syntax in 9.2, and de-reserving that keyword? (Given that the > workaround is this simple, I'm inclined to say "no", but could be > persuaded otherwise.) > I'm inclined to say yes, but mainly because it's just old cruft. I don't expect to be able,say, to load a pre-7.3 dump into a modern Postgres. cheers andrew
Re: [BUGS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
From
Robert Haas
Date:
On Tue, Jul 5, 2011 at 11:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Tue, Jul 5, 2011 at 11:06 AM, Alvaro Herrera >> <alvherre@commandprompt.com> wrote: >>> I assume it's not in unreserved_keyword because it would cause a >>> shift/reduce conflict elsewhere. > >> Yeah. In particular, it conflicts with the ancient copy syntax which >> we still support for backwards compatibility with versions < 7.3. We >> can fix the immediate problem with something like the attached. > >> (a) Should we do that? > > That seems like a horrid crock ... Do you have something else to propose? It's a crock we have used elsewhere, so there is some precedent. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [BUGS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
From
Pavel Golub
Date:
Hello, Robert. You wrote: RH> On Tue, Jul 5, 2011 at 11:06 AM, Alvaro Herrera RH> <alvherre@commandprompt.com> wrote: >> Excerpts from Pavel Golub's message of mar jul 05 10:52:06 -0400 2011: >>> Hello. >>> >>> System: PostgreSQL v9.0 Windows XP SP3 >>> SQL: COPY "tablename" TO STDOUT WITH (FORMAT binary) >>> ERROR: syntax error at or near "binary" >>> LINE 1: ...OPY "tablename" TO STDOUT WITH (FORMAT binary) >>> ^ >>> >>> ********** Error ********** >>> >>> ERROR: syntax error at or near "binary" >>> SQL state: 42601 >>> Character: 55 >>> >>> But if I use 'FORMAT text' or 'FORMAT csv' all is OK. >>> >>> Suppose this happens because BINARY is not listed in >>> "unreserved_keyword" neither in "col_name_keyword parser" parser rules, but >>> listed in "type_func_name_keyword" instead. >> >> That seems pretty unfortunate. Of course, it works if you quote it: >> >> COPY "tablename" TO STDOUT WITH (FORMAT "binary") >> >> I assume it's not in unreserved_keyword because it would cause a >> shift/reduce conflict elsewhere. RH> Yeah. In particular, it conflicts with the ancient copy syntax which RH> we still support for backwards compatibility with versions < 7.3. We RH> can fix the immediate problem with something like the attached. This patch is ugly. Sorry, Robert, but it's true. RH> (a) Should we do that? RH> (b) Should we back-patch it to 9.1 and 9.0? RH> (c) Should we consider removing compatibility with the ancient copy RH> syntax in 9.2, and de-reserving that keyword? (Given that the RH> workaround is this simple, I'm inclined to say "no", but could be RH> persuaded otherwise.) +1 for this. Pre-7.3 syntax is dead in fact for many years. -- With best wishes,Pavel mailto:pavel@gf.microolap.com
Re: [BUGS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
From
Robert Haas
Date:
On Tue, Jul 5, 2011 at 11:37 AM, Pavel Golub <pavel@microolap.com> wrote: > RH> Yeah. In particular, it conflicts with the ancient copy syntax which > RH> we still support for backwards compatibility with versions < 7.3. We > RH> can fix the immediate problem with something like the attached. > > This patch is ugly. Sorry, Robert, but it's true. No hard feelings here. If you, as the reporter of the problem, don't feel that it's serious enough to warrant back-patching a fix, then I'm not going to insist. However, if we don't do what I've proposed here, then I think 8.4 and 9.0 and probably 9.1 are going to need to stay as they are, because... > RH> (c) Should we consider removing compatibility with the ancient copy > RH> syntax in 9.2, and de-reserving that keyword? (Given that the > RH> workaround is this simple, I'm inclined to say "no", but could be > RH> persuaded otherwise.) > > +1 for this. Pre-7.3 syntax is dead in fact for many years. ...this is not something we're going to back-patch. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [BUGS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> ... However, if we don't do what I've proposed here,
> then I think 8.4 and 9.0 and probably 9.1 are going to need to stay as
> they are, because...
>> RH> (c) Should we consider removing compatibility with the ancient copy
>> RH> syntax in 9.2, and de-reserving that keyword? �(Given that the
>> RH> workaround is this simple, I'm inclined to say "no", but could be
>> RH> persuaded otherwise.)
>>
>> +1 for this. Pre-7.3 syntax is dead in fact for many years.
> ...this is not something we're going to back-patch.
Given the lack of prior complaints, and the simplicity of the
double-quote workaround, I feel little need to have a back-patchable
fix.
regards, tom lane
Re: [BUGS] COPY .... WITH (FORMAT binary) causes syntax error at or near "binary"
From
Pavel Golub
Date:
Hello, Robert. You wrote: RH> On Tue, Jul 5, 2011 at 11:37 AM, Pavel Golub <pavel@microolap.com> wrote: >> RH> Yeah. In particular, it conflicts with the ancient copy syntax which >> RH> we still support for backwards compatibility with versions < 7.3. We >> RH> can fix the immediate problem with something like the attached. >> >> This patch is ugly. Sorry, Robert, but it's true. RH> No hard feelings here. If you, as the reporter of the problem, don't RH> feel that it's serious enough to warrant back-patching a fix, then I'm RH> not going to insist. However, if we don't do what I've proposed here, RH> then I think 8.4 and 9.0 and probably 9.1 are going to need to stay as RH> they are, because... >> RH> (c) Should we consider removing compatibility with the ancient copy >> RH> syntax in 9.2, and de-reserving that keyword? (Given that the >> RH> workaround is this simple, I'm inclined to say "no", but could be >> RH> persuaded otherwise.) >> >> +1 for this. Pre-7.3 syntax is dead in fact for many years. RH> ...this is not something we're going to back-patch. Patches needed for 9.0 and 9.1 only, because this is new format comparing with 8.x -- With best wishes,Pavel mailto:pavel@gf.microolap.com