Thread: SQL keywords are suddenly case sensitive
List,
SQL seems to be behaving in a case-sensitive manner:
universe=# select 1;
?column?
----------
1
(1 row)
universe=# SELECT 1;
ERROR: syntax error at or near "SELECT 1"
LINE 1: SELECT 1;
^
I cannot figure out how this happened, and Google is not helping because
all I'm getting is information about case-sensitivity in identifiers or
in string comparison. I didn't even think this was possible, as I've
always switched between lower case and upper case keywords, usually
using lower case while testing and upper case to prettify scripts that I
will be saving for reuse.
version() = "PostgreSQL 9.1.8 on x86_64-unknown-linux-gnu, compiled by
gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit"
Regards,
--Lee
--
Lee Hachadoorian
Assistant Professor in Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu
On 04/16/2013 02:46 PM, Lee Hachadoorian wrote: > List, > > SQL seems to be behaving in a case-sensitive manner: > > universe=# select 1; > ?column? > ---------- > 1 > (1 row) > > universe=# SELECT 1; > ERROR: syntax error at or near "SELECT 1" > LINE 1: SELECT 1; > ^ > I cannot figure out how this happened, and Google is not helping because > all I'm getting is information about case-sensitivity in identifiers or > in string comparison. I didn't even think this was possible, as I've > always switched between lower case and upper case keywords, usually > using lower case while testing and upper case to prettify scripts that I > will be saving for reuse. Interesting. Does this behavior survive logging out and then back into a session? Do you have any other client using the database that exhibits this behavior? > Regards, > --Lee > -- Adrian Klaver adrian.klaver@gmail.com
On 04/16/2013 02:46 PM, Lee Hachadoorian wrote: > List, > > SQL seems to be behaving in a case-sensitive manner: > > universe=# select 1; > ?column? > ---------- > 1 > (1 row) > > universe=# SELECT 1; > ERROR: syntax error at or near "SELECT 1" > LINE 1: SELECT 1; > ^ > I cannot figure out how this happened, and Google is not helping because > all I'm getting is information about case-sensitivity in identifiers or > in string comparison. I didn't even think this was possible, as I've > always switched between lower case and upper case keywords, usually > using lower case while testing and upper case to prettify scripts that I > will be saving for reuse. Another question: Are the psql and Postgres versions the same? > Regards, > --Lee > -- Adrian Klaver adrian.klaver@gmail.com
On 04/16/2013 07:31 PM, Adrian Klaver wrote: > On 04/16/2013 02:46 PM, Lee Hachadoorian wrote: >> List, >> >> SQL seems to be behaving in a case-sensitive manner: >> >> universe=# select 1; >> ?column? >> ---------- >> 1 >> (1 row) >> >> universe=# SELECT 1; >> ERROR: syntax error at or near "SELECT 1" >> LINE 1: SELECT 1; >> ^ >> I cannot figure out how this happened, and Google is not helping because >> all I'm getting is information about case-sensitivity in identifiers or >> in string comparison. I didn't even think this was possible, as I've >> always switched between lower case and upper case keywords, usually >> using lower case while testing and upper case to prettify scripts that I >> will be saving for reuse. > > Interesting. > > Does this behavior survive logging out and then back into a session? It survives complete restart. (This is a laptop that I use for development and analysis, not a high-availability server, so the first thing I did when I realized my scripts started failing was reboot.) > > Do you have any other client using the database that exhibits this > behavior? Same behavior in both psql and pgAdmin. Regards, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu
On 04/16/2013 07:34 PM, Adrian Klaver wrote: > On 04/16/2013 02:46 PM, Lee Hachadoorian wrote: >> List, >> >> SQL seems to be behaving in a case-sensitive manner: >> >> universe=# select 1; >> ?column? >> ---------- >> 1 >> (1 row) >> >> universe=# SELECT 1; >> ERROR: syntax error at or near "SELECT 1" >> LINE 1: SELECT 1; >> ^ >> I cannot figure out how this happened, and Google is not helping because >> all I'm getting is information about case-sensitivity in identifiers or >> in string comparison. I didn't even think this was possible, as I've >> always switched between lower case and upper case keywords, usually >> using lower case while testing and upper case to prettify scripts that I >> will be saving for reuse. > > Another question: > > Are the psql and Postgres versions the same? Appears to both be 9.1.8. lee@tycho ~ $ psql -d universe psql (9.1.8) Type "help" for help. universe=# select version(); version ---------- PostgreSQL 9.1.8 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Regards, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu
On 04/16/2013 04:39 PM, Lee Hachadoorian wrote:
>
> On 04/16/2013 07:31 PM, Adrian Klaver wrote:
>> On 04/16/2013 02:46 PM, Lee Hachadoorian wrote:
>>
>> Interesting.
>>
>> Does this behavior survive logging out and then back into a session?
>
> It survives complete restart. (This is a laptop that I use for
> development and analysis, not a high-availability server, so the first
> thing I did when I realized my scripts started failing was reboot.)
>
>>
>> Do you have any other client using the database that exhibits this
>> behavior?
>
> Same behavior in both psql and pgAdmin.
So when did this start?
a) From creation of the database?
b) At some point afterward?
3) If b), did something noteworthy to Postgres, an upgrade possibly?
4) Is there more than one version of Postgres on the machine?
>
> Regards,
> --Lee
>
--
Adrian Klaver
adrian.klaver@gmail.com
Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com> writes:
> SQL seems to be behaving in a case-sensitive manner:
> universe=# select 1;
> ?column?
> ----------
> 1
> (1 row)
> universe=# SELECT 1;
> ERROR: syntax error at or near "SELECT 1"
> LINE 1: SELECT 1;
> ^
That's really bizarre, but I don't think it's a case sensitivity problem
as such. Watch what I get from a syntax error on a normally-functioning
system:
$ psql
psql (9.1.9)
Type "help" for help.
regression=# select 1;
?column?
----------
1
(1 row)
regression=# SELECT 1;
?column?
----------
1
(1 row)
regression=# xELECT 1;
ERROR: syntax error at or near "xELECT"
LINE 1: xELECT 1;
^
See the differences? The error message indicates that your parser saw
"SELECT 1" as all one token. Also, if you transcribed this accurately,
it looks like the error cursor is pointing to the second character of
the "SELECT" not the first (could you confirm that?). Which is even
more bizarre. I'm not sure what's going on, but I think it's more
likely to be something to do with whitespace not being really whitespace
than it is with case as such. Consider the possibility that you're
somehow typing a non-breaking space or some such character. One thing
that might be useful is to examine the error report in the postmaster
log using an editor that will show you any non-printing characters.
regards, tom lane
On 04/16/2013 08:23 PM, Tom Lane wrote: > "SELECT 1" as all one token. Also, if you transcribed this accurately, > it looks like the error cursor is pointing to the second character of > the "SELECT" not the first (could you confirm that?). Which is even > more bizarre. No, that must have been an email formatting thing. In psql, the caret is under the S. Looking at the other issues you raised, but just wanted to provide a quick answer to that. Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu
On Tue, Apr 16, 2013 at 08:57:02PM -0400, Lee Hachadoorian wrote: > > On 04/16/2013 08:23 PM, Tom Lane wrote: > >"SELECT 1" as all one token. Also, if you transcribed this accurately, > >it looks like the error cursor is pointing to the second character of > >the "SELECT" not the first (could you confirm that?). Which is even > >more bizarre. > > No, that must have been an email formatting thing. In psql, the > caret is under the S. > > Looking at the other issues you raised, but just wanted to provide a > quick answer to that. Use SET log_statment='all' and look in the server logs for the query. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On 04/16/2013 08:23 PM, Tom Lane wrote: > Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com> writes: >> SQL seems to be behaving in a case-sensitive manner: >> universe=# select 1; >> ?column? >> ---------- >> 1 >> (1 row) >> universe=# SELECT 1; >> ERROR: syntax error at or near "SELECT 1" >> LINE 1: SELECT 1; >> ^ > That's really bizarre, but I don't think it's a case sensitivity problem > as such. Watch what I get from a syntax error on a normally-functioning > system: > > $ psql > psql (9.1.9) > Type "help" for help. > > regression=# select 1; > ?column? > ---------- > 1 > (1 row) > > regression=# SELECT 1; > ?column? > ---------- > 1 > (1 row) > > regression=# xELECT 1; > ERROR: syntax error at or near "xELECT" > LINE 1: xELECT 1; > ^ > > See the differences? The error message indicates that your parser saw > "SELECT 1" as all one token. Also, if you transcribed this accurately, > it looks like the error cursor is pointing to the second character of > the "SELECT" not the first (could you confirm that?). Which is even > more bizarre. I'm not sure what's going on, but I think it's more > likely to be something to do with whitespace not being really whitespace > than it is with case as such. Consider the possibility that you're > somehow typing a non-breaking space or some such character. One thing > that might be useful is to examine the error report in the postmaster > log using an editor that will show you any non-printing characters. > > regards, tom lane Tom, Yes, nonbreaking spaces was the problem. Sorry for the red herring re: case sensitivity, and thanks for figuring it out. Adrian, thanks for your input as well. Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu
I've seen this in the ticketing system Front Range where it sticks in nbsp into the text windows and if you copy and paste it won't work. Frustrating as all hell. On Tue, Apr 16, 2013 at 7:18 PM, Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com> wrote: > > On 04/16/2013 08:23 PM, Tom Lane wrote: >> >> Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com> writes: >>> >>> SQL seems to be behaving in a case-sensitive manner: >>> universe=# select 1; >>> ?column? >>> ---------- >>> 1 >>> (1 row) >>> universe=# SELECT 1; >>> ERROR: syntax error at or near "SELECT 1" >>> LINE 1: SELECT 1; >>> ^ >> >> That's really bizarre, but I don't think it's a case sensitivity problem >> as such. Watch what I get from a syntax error on a normally-functioning >> system: >> >> $ psql >> psql (9.1.9) >> Type "help" for help. >> >> regression=# select 1; >> ?column? >> ---------- >> 1 >> (1 row) >> >> regression=# SELECT 1; >> ?column? >> ---------- >> 1 >> (1 row) >> >> regression=# xELECT 1; >> ERROR: syntax error at or near "xELECT" >> LINE 1: xELECT 1; >> ^ >> >> See the differences? The error message indicates that your parser saw >> "SELECT 1" as all one token. Also, if you transcribed this accurately, >> it looks like the error cursor is pointing to the second character of >> the "SELECT" not the first (could you confirm that?). Which is even >> more bizarre. I'm not sure what's going on, but I think it's more >> likely to be something to do with whitespace not being really whitespace >> than it is with case as such. Consider the possibility that you're >> somehow typing a non-breaking space or some such character. One thing >> that might be useful is to examine the error report in the postmaster >> log using an editor that will show you any non-printing characters. >> >> regards, tom lane > > > Tom, > > Yes, nonbreaking spaces was the problem. Sorry for the red herring re: case > sensitivity, and thanks for figuring it out. Adrian, thanks for your input > as well. > > Best, > > --Lee > > -- > Lee Hachadoorian > Assistant Professor in Geography, Dartmouth College > http://freecity.commons.gc.cuny.edu > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- To understand recursion, one must first understand recursion.