Thread: column name is "LIMIT"
Guys, I am having a problem firing queries on one of the tables which is having "limit" as the column name. If a run an insert/select/update command on that table i get the below error. ERROR: syntax error at or near "limit" at character 71 Any Help would be realyl great to solve the problem. postgresql 7.4.5 and linux OS -- Best, Gourish Singbal
Put "" around the column name, eg: insert into "limit" values (1, 2,3 ); Chris Gourish Singbal wrote: > Guys, > > I am having a problem firing queries on one of the tables which is > having "limit" as the column name. > > If a run an insert/select/update command on that table i get the below error. > > ERROR: syntax error at or near "limit" at character 71 > > Any Help would be realyl great to solve the problem. > > postgresql 7.4.5 and linux OS >
On Mon, 14 Mar 2005 06:14 pm, Gourish Singbal wrote: > Guys, > > I am having a problem firing queries on one of the tables which is > having "limit" as the column name. > > If a run an insert/select/update command on that table i get the below error. > > ERROR: syntax error at or near "limit" at character 71 select "limit" from limit_table WHERE "limit" < 50 LIMIT 2; You need to quote the field name, and make sure the case is correct. > > Any Help would be realyl great to solve the problem. > > postgresql 7.4.5 and linux OS > You should probably upgrade to 7.4.7 Regards Russell Smith.
Thanks a lot, we might be upgrading to 8.0.1 soon.. till than using double quotes should be fine. regards gourish On Mon, 14 Mar 2005 18:25:22 +1100, Russell Smith <mr-russ@pws.com.au> wrote: > On Mon, 14 Mar 2005 06:14 pm, Gourish Singbal wrote: > > Guys, > > > > I am having a problem firing queries on one of the tables which is > > having "limit" as the column name. > > > > If a run an insert/select/update command on that table i get the below error. > > > > ERROR: syntax error at or near "limit" at character 71 > > select "limit" from limit_table WHERE "limit" < 50 LIMIT 2; > > You need to quote the field name, and make sure the case is correct. > > > > Any Help would be realyl great to solve the problem. > > > > postgresql 7.4.5 and linux OS > > > You should probably upgrade to 7.4.7 > > Regards > > Russell Smith. > -- Best, Gourish Singbal
You will still need to use double quotes in 8.0.1... Chris Gourish Singbal wrote: > Thanks a lot, > > we might be upgrading to 8.0.1 soon.. till than using double quotes > should be fine. > > regards > gourish > > On Mon, 14 Mar 2005 18:25:22 +1100, Russell Smith <mr-russ@pws.com.au> wrote: > >>On Mon, 14 Mar 2005 06:14 pm, Gourish Singbal wrote: >> >>>Guys, >>> >>>I am having a problem firing queries on one of the tables which is >>>having "limit" as the column name. >>> >>>If a run an insert/select/update command on that table i get the below error. >>> >>>ERROR: syntax error at or near "limit" at character 71 >> >>select "limit" from limit_table WHERE "limit" < 50 LIMIT 2; >> >>You need to quote the field name, and make sure the case is correct. >> >>>Any Help would be realyl great to solve the problem. >>> >>>postgresql 7.4.5 and linux OS >>> >> >>You should probably upgrade to 7.4.7 >> >>Regards >> >>Russell Smith. >> > > >
Yeah... how come no one told him "don't do that"? LIMIT is an SQL reserved word, so it's likely to cause trouble in any database you try to use it on... I'd strongly recommend renaming that column asap. You can see other reserved words at http://www.postgresql.org/docs/8.0/interactive/sql-keywords-appendix.html Robert Treat On Mon, 2005-03-14 at 03:55, Christopher Kings-Lynne wrote: > You will still need to use double quotes in 8.0.1... > > Chris > > Gourish Singbal wrote: > > Thanks a lot, > > > > we might be upgrading to 8.0.1 soon.. till than using double quotes > > should be fine. > > > > regards > > gourish > > > > On Mon, 14 Mar 2005 18:25:22 +1100, Russell Smith <mr-russ@pws.com.au> wrote: > > > >>On Mon, 14 Mar 2005 06:14 pm, Gourish Singbal wrote: > >> > >>>Guys, > >>> > >>>I am having a problem firing queries on one of the tables which is > >>>having "limit" as the column name. > >>> > >>>If a run an insert/select/update command on that table i get the below error. > >>> > >>>ERROR: syntax error at or near "limit" at character 71 > >> > >>select "limit" from limit_table WHERE "limit" < 50 LIMIT 2; > >> > >>You need to quote the field name, and make sure the case is correct. > >> > >>>Any Help would be realyl great to solve the problem. > >>> > >>>postgresql 7.4.5 and linux OS > >>> > >> > >>You should probably upgrade to 7.4.7 > >> > >>Regards > >> > >>Russell Smith. > >> > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On 3/14/2005 1:28 PM, Robert Treat wrote: > Yeah... how come no one told him "don't do that"? LIMIT is an SQL > reserved word, so it's likely to cause trouble in any database you try > to use it on... I'd strongly recommend renaming that column asap. You > can see other reserved words at > http://www.postgresql.org/docs/8.0/interactive/sql-keywords-appendix.html > > Robert Treat Note also that the Slony-I replication system has problems with column names identical to reserved words. This is rooted in the fact that the quote_ident() function doesn't quote reserved words ... as it IMHO is supposed to do. Jan > > On Mon, 2005-03-14 at 03:55, Christopher Kings-Lynne wrote: >> You will still need to use double quotes in 8.0.1... >> >> Chris >> >> Gourish Singbal wrote: >> > Thanks a lot, >> > >> > we might be upgrading to 8.0.1 soon.. till than using double quotes >> > should be fine. >> > >> > regards >> > gourish >> > >> > On Mon, 14 Mar 2005 18:25:22 +1100, Russell Smith <mr-russ@pws.com.au> wrote: >> > >> >>On Mon, 14 Mar 2005 06:14 pm, Gourish Singbal wrote: >> >> >> >>>Guys, >> >>> >> >>>I am having a problem firing queries on one of the tables which is >> >>>having "limit" as the column name. >> >>> >> >>>If a run an insert/select/update command on that table i get the below error. >> >>> >> >>>ERROR: syntax error at or near "limit" at character 71 >> >> >> >>select "limit" from limit_table WHERE "limit" < 50 LIMIT 2; >> >> >> >>You need to quote the field name, and make sure the case is correct. >> >> >> >>>Any Help would be realyl great to solve the problem. >> >>> >> >>>postgresql 7.4.5 and linux OS >> >>> >> >> >> >>You should probably upgrade to 7.4.7 >> >> >> >>Regards >> >> >> >>Russell Smith. >> >> >> > >> > >> > >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
> Note also that the Slony-I replication system has problems > with column > names identical to reserved words. This is rooted in the fact > that the > quote_ident() function doesn't quote reserved words ... as it IMHO is > supposed to do. > > > Jan > Does this apply to table names as well or just columns? Bryan
Jan Wieck <JanWieck@Yahoo.com> writes:
> quote_ident() function doesn't quote reserved words ... as it IMHO is
> supposed to do.
You're right, it probably should. The equivalent code in pg_dump knows
about this, but quote_ident() doesn't.
One thing that's been on my mind with respect to all this is that it
would be nice not to quote "non-reserved" keywords. Most of the weird
non-SQL-spec keywords that we have are non-reserved, and we could more
easily keep them out of people's faces if we didn't quote them in dumps.
Of course such a policy would raise the ante for any change that makes
an existing keyword reserved when it wasn't before, but that's already
a dangerous kind of change.
regards, tom lane
On 3/14/2005 2:26 PM, Bryan Encina wrote: >> Note also that the Slony-I replication system has problems >> with column >> names identical to reserved words. This is rooted in the fact >> that the >> quote_ident() function doesn't quote reserved words ... as it IMHO is >> supposed to do. >> >> >> Jan >> > > Does this apply to table names as well or just columns? > > Bryan Sure does, don't try to replicate a table named "user". Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
So is it to make SQL parser context-sensitive - say the parser will understand that in statement "SELECT * from LIMIT", LIMIT is just a table name, instead of keyword? There might be some conflicts when using Yacc, but I am not sure how difficult will be ... Cheers, Qingqing "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> > You will still need to use double quotes in 8.0.1... > > Chris > > Gourish Singbal wrote: > > Thanks a lot, > > > > we might be upgrading to 8.0.1 soon.. till than using double quotes > > should be fine. > > > > regards > > gourish > > > > On Mon, 14 Mar 2005 18:25:22 +1100, Russell Smith <mr-russ@pws.com.au> wrote: > > > >>On Mon, 14 Mar 2005 06:14 pm, Gourish Singbal wrote: > >> > >>>Guys, > >>> > >>>I am having a problem firing queries on one of the tables which is > >>>having "limit" as the column name. > >>> > >>>If a run an insert/select/update command on that table i get the below error. > >>> > >>>ERROR: syntax error at or near "limit" at character 71 > >> > >>select "limit" from limit_table WHERE "limit" < 50 LIMIT 2; > >> > >>You need to quote the field name, and make sure the case is correct. > >> > >>>Any Help would be realyl great to solve the problem. > >>> > >>>postgresql 7.4.5 and linux OS > >>> > >> > >>You should probably upgrade to 7.4.7 > >> > >>Regards > >> > >>Russell Smith. > >> > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On 3/14/2005 4:26 AM, Qingqing Zhou wrote:
> So is it to make SQL parser context-sensitive - say the parser will
> understand that in statement "SELECT * from LIMIT", LIMIT is just a table
> name, instead of keyword?
More or less, yes. To use a reserved keyword as an identifier (table or
column name), it must be enclosed in double quotes. Double quotes are
also used to make identifiers case sensitive. So
select someval, "SOMEVAL", "someVAL" from "user";
is a valid query retrieving 3 distinct columns from the table "user".
There is a builtin function quote_ident() in PostgreSQL that is supposed
to return a properly quoted string allowed as an identifier for whatever
name is passed in. But it fails to do so for all lower case names that
are reserved keywords.
The queries Slony executes on the replicas are constructed using that
quoting function, and therefore Slony fails to build valid SQL for
replicated table containing reserved keyword identifiers. One solution
would be to brute-force quote all identifiers in Slony ... not sure what
the side effects performance wise would be.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Jan Wieck <JanWieck@Yahoo.com> writes:
> There is a builtin function quote_ident() in PostgreSQL that is supposed
> to return a properly quoted string allowed as an identifier for whatever
> name is passed in. But it fails to do so for all lower case names that
> are reserved keywords.
Not any more ...
regards, tom lane