Re: SELECT problem

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