Re: BUG #1136: Error finding columns with mixed case names

On Mon, 19 Apr 2004, PostgreSQL Bugs List wrote:

> Description: Error finding columns with mixed case names
>
> I have the following table:
> psql> \d reporter
> Table "public.reporter"
> Column | Type |
> Modifiers
> ---------------+-----------------------------+-----------------------------
> ----------------------------------------
> id | integer | not null default
> nextval('Reporter_id_seq'::text)
> reporterId | character varying(255) | not null default
> ''::character varying
> species | character varying(255) | not null default
> ''::character varying
> clusterId | integer | not null default 0
> geneName | character varying(255) | not null default
> ''::character varying
> geneSymbol | character varying(255) | not null default
> ''::character varying
> lastUpdate | timestamp without time zone | not null default '0001-01-01
> 00:00:00'::timestamp without time zone
> length | integer | default 0
> sequence | text |
> vector | text |
> tissue | text |
> library | text |
> accession | character varying(255) |
> nid | character varying(255) |
> chromosome | character varying(255) |
> cytoBand | character varying(255) |
> markers | character varying(255) |
> antibiotics | character varying(255) |
> locusLink | integer | default 0
> omim | integer | default 0
> startPosition | integer | default 0
> endPosition | integer | default 0
> Indexes:
> "reporter_pkey" primary key, btree (id)
> "reporter_reporterid_index" unique, btree ("reporterId")
> "reporter_genename_index" btree ("geneName")
>
> When I try to retrieve from this table with 'select * from reporter;' it
> works fine.
>
> If I try to retrieve from any column with a mixed case name then I get the
> following:
>
> psql> select reporterId from reporter;
> ERROR: column "reporterid" does not exist

The correct way to specify this name is
select "reporterId" from reporter;

Names that are unquoted are case-folded, so if you create a mixed case
name with double quotes in the create, you need to refer to the column
with double quotes in queries.