Postgres Pro
Downloads
Home   >   mailing lists

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

BUG #1136: Error finding columns with mixed case names

From
"PostgreSQL Bugs List"
Date:
The following bug has been logged online:

Bug reference:      1136
Logged by:          David Martin

Email address:      d.m.a.martin@dundee.ac.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

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

From
Stephan Szabo
Date:
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.