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

The following bug has been logged online:

Bug reference: 1136
Logged by: David Martin

Email address: d(dot)m(dot)a(dot)martin(at)dundee(dot)ac(dot)uk

PostgreSQL version: 7.4

Operating system: Debian Linux

Description: Error finding columns with mixed case names

Details:

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

It doesn't work if I use all lower case for the column name

psql> select reporterid from reporter;
ERROR: column "reporterid" does not exist

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.