SQL keywords are suddenly case sensitive

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general