SELECT problem

On Fri, Jun 21, 2002 at 11:38:39AM +0100, David Goodenough wrote:

landn=# select * from sites;
CUSTNAME | AREA | SITE | NAME | BUILDING | TOWN | COUNTY | POSTCODE |
GRIDREF | LATITUDE | LONGITUDE
----------+------+------+------+----------+------+--------+----------+---------+----------+-----------
(0 rows)

landn=# select area from sites;
ERROR: Attribute 'area' not found
landn=#

select "AREA" from sites;

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

On Fri, 2002-06-21 at 11:38, David Goodenough wrote:

I am obviously doing something very stupid, but I get a problem using
specific columns in a SELECT statement. I am running 7.2 on a Debian
system.

The problem is that when I reference a specific column, it says attribute
not found, but the column is there, at least according to \d. Here is the
\d for the table (called sites) the result from a SELECT * and the offending
SELECT AREA. Now I did check that AREA was not a keyword, and it would appear
not to be. I also checked after this log with some data in the table, but
it made no difference.

Any ideas:

Here is the console output:-

landn=# \d sites
Table "sites"
Column | Type | Modifiers
-----------+-----------------------+-----------
CUSTNAME | character varying(8) |
AREA | character varying(8) |
SITE | character varying(24) |
NAME | character varying(24) |
BUILDING | character varying(50) |
TOWN | character varying(50) |
COUNTY | character varying(50) |
POSTCODE | character varying(15) |
GRIDREF | character varying(12) |
LATITUDE | character varying(12) |
LONGITUDE | character varying(12) |

landn=# select * from sites;
CUSTNAME | AREA | SITE | NAME | BUILDING | TOWN | COUNTY | POSTCODE |
GRIDREF | LATITUDE | LONGITUDE
----------+------+------+------+----------+------+--------+----------+---------+----------+-----------
(0 rows)

landn=# select area from sites;
ERROR: Attribute 'area' not found
landn=#

The problem is that you have created column names which are explicitly
uppercase. In this case you need to surround them with double quotes.
You should find that
select "AREA" from sites;
works as expected.

It is often recommended not to create mixed-case column names to avoid
having to quote them. However, certain front ends (ISTR MS Access is
one) do generate mixed case names. (PostgreSQL defaults to lowercasing
unquoted names in CREATE, SELECT etc., which makes things simpler).

Hope this helps

John

--
John Gray
Azuli IT
www.azuli.co.uk

On Friday 21 June 2002 11:46, Karel Zak wrote:

On Fri, Jun 21, 2002 at 11:38:39AM +0100, David Goodenough wrote:

landn=# select * from sites;
CUSTNAME | AREA | SITE | NAME | BUILDING | TOWN | COUNTY | POSTCODE |
GRIDREF | LATITUDE | LONGITUDE
----------+------+------+------+----------+------+--------+----------+---
------+----------+----------- (0 rows)

landn=# select area from sites;
ERROR: Attribute 'area' not found
landn=#

select "AREA" from sites;

Karel

Well yes that works, but why? In every book I have ever read on SQL
the column names are not in quotes, and the other DB I use regularly
(IBM DB/2) does not require the column names in quotes, and actually
does not recognise the column names if they are in quotes?

I thought SQL was supposed to be standardised these days? Or are we
in the "standards are a good thing, lets have lots" mode with conflicting
standards or incomplete standards?

David

On Fri, Jun 21, 2002 at 12:24:02PM +0100, David Goodenough wrote:

Well yes that works, but why? In every book I have ever read on SQL
the column names are not in quotes, and the other DB I use regularly
(IBM DB/2) does not require the column names in quotes, and actually
does not recognise the column names if they are in quotes?

PostgreSQL is actually contrary to the standard here, in that it
makes everyting lower-case. If you want an uppercase column name,
you have to double-quote it. Otherwise, leave everything unquoted
and use lower case.

A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110

On Fri, 21 Jun 2002 08:25:54 -0400
"Andrew Sullivan" <andrew@libertyrms.info> wrote:

On Fri, Jun 21, 2002 at 12:24:02PM +0100, David Goodenough wrote:

Well yes that works, but why? In every book I have ever read on SQL
the column names are not in quotes, and the other DB I use regularly
(IBM DB/2) does not require the column names in quotes, and actually
does not recognise the column names if they are in quotes?

PostgreSQL is actually contrary to the standard here, in that it
makes everyting lower-case.

Which part of which standard is PostgreSQL contrary to?

(You may well be right -- it's just that I couldn't see anything
in SQL99 after a cursory look.)

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

PostgreSQL is actually contrary to the standard here, in that it
makes everyting lower-case.

Which part of which standard is PostgreSQL contrary to?

From my SQL99 draft document (SQL92 has something similar):

20.1 Introduction to Information Schema and Definition Schema

The representation of an <identifier> in the base tables and views
of the Information Schema is by a character string corresponding
to its <identifier body> (in the case of a <regular identifier>)
or its <delimited identifier body> (in the case of a <delimited
identifier>). Within this character string, any lower-case letter
appearing in a <regular identifier> is replaced by the equivalent
upper-case letter, and any <doublequote symbol> appearing in a
<delimited identifier body> is replaced by a <double quote>. Where
an <actual identifier> has multiple forms that are equal according
to the rules of Subclause 8.2, "<comparison predicate>", the form
stored is that encountered at definition time.

The mention of a transformation to upper-case is what Andrew is
referring to; PostgreSQL historically has transformed to lower case and
so far we are too stubborn to change it just because it has become a
standard ;)

- Thomas

Thomas Lockhart <lockhart@fourpalms.org> writes:

The mention of a transformation to upper-case is what Andrew is
referring to; PostgreSQL historically has transformed to lower case and
so far we are too stubborn to change it just because it has become a
standard ;)

See also the example and footnote at the end of section 1.1.1 of the
user's guide,
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

The fact that Postgres deviates from the spec is only visible if you
sometimes use a name with quotes and sometimes without: foo with no
quotes (and any casing) is equivalent to "FOO" per spec but "foo" per
Postgres. Expecting a mixed-case quoted identifier to be equivalent
to an unquoted identifier is wrong either way.

(There are reasons other than stubbornness for not wanting to conform
exactly to the spec on this point. In particular, it's well established
that lower-case text is more readable than upper-case, and so we'd take
a nontrivial usability hit if the system started storing and reporting
names in upper case.)

If the original report is correct then SQL Server matches names
case-insensitively (at least when they're not quoted), which seems
a much larger departure from the spec behavior to me. For example,
I'd think they'd have to reject table definitions that contain
columns named both "foo" and "FOO", else they'd have
effectively-duplicate column names. Can anyone verify their behavior?

regards, tom lane

Tom Lane wrote:

If the original report is correct then SQL Server matches names
case-insensitively (at least when they're not quoted), which seems
a much larger departure from the spec behavior to me. For example,
I'd think they'd have to reject table definitions that contain
columns named both "foo" and "FOO", else they'd have
effectively-duplicate column names. Can anyone verify their behavior?

MSSQL Server lets you choose *on installation* whether you want
case-sensitive or case-insensitive behavior (at least through MSSQL 7,
not sure about MSSQL 2000). If you pick the latter, both identifiers and
data are case-insensitive. Even with MSSQL Server, if a case-sensitive
install is picked, I believe it would break this app. (which is clearly
flawed anyway).

JOe

I have worked with SQL server. It is a little weird as far as case. If
the table name is really upper case, you must type it upper case (it is
case sensitive, and does not default to upper case) I have been using
it lately in some classes for PeopleSoft, which puts the table names
upper case as per the standard (even if the standard is stupid). We use
Oracle at work, and it defaults to upper case, which is standard, but
ugly. As far as I can tell, the ODBC driver is case insensitive, but
the other tools that access the DB directly are case sensitive, whether
quoted or not. To force case within the ODBC, quote using [] rather
than "". I wish PeopleSoft supported Postgres. It is so much easier to
use.