Re: [PERFORM] column name is LIMIT
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] column name is LIMIT
Jan Wieck [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] column name is LIMIT
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] column name is LIMIT
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] column name is LIMIT
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED])
Re: [PERFORM] column name is LIMIT
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED])
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] column name is LIMIT
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED]) -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] column name is LIMIT
Jan Wieck [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] column name is LIMIT
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] column name is LIMIT
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] column name is LIMIT
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 [EMAIL PROTECTED])
Re: [PERFORM] column name is LIMIT
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
