Обсуждение: SELECT problem
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=#
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.