Обсуждение: BUG #2598: Columns named R are not accessible - although R is not a keyword

The following bug has been logged online:

Bug reference:      2598
Logged by:          Andreas Langegger
Email address:      al@jku.at
PostgreSQL version: 8.1
Operating system:   Gentoo Linux 3.3.6, Kernel 2.6.16
Description:        Columns named R are not accessible - although R is not a
keyword
Details:

It seems that the column name "R" or "r" is reserved. If I want to insert
tuples I get the error message:

ERROR:  column "r" of relation "xyz" does not exist

And if I alter the column name to "a" it works. Again, renaming to "R" or
"r", same error. But it's not announced to be a name conflict / reserved
keyword...

Regards,
Andy

"Andreas Langegger" <al@jku.at> writes:
> It seems that the column name "R" or "r" is reserved.

Hardly.

> If I want to insert tuples I get the error message:

> ERROR:  column "r" of relation "xyz" does not exist

Worksforme:

regression=# create table xyz(r int);
CREATE TABLE
regression=# insert into xyz(r) values(42);
INSERT 0 1

Perhaps you could provide a self-contained test case?

            regards, tom lane

Andreas Langegger <al@jku.at> writes:
> Well, I also tried your simple regression test and it worked. The more
> I'm wondering why this does not:

> CREATE TABLE sunspots
> ...
>   "R" int2,

"R" with double quotes represents capital R, but when you write R
without quotes in a query, it's implicitly downcased.  See
http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

If you spell it like that in the table definition then you'll need
to use quotes every time you refer to the column, too.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, I also tried your simple regression test and it worked. The more
I'm wondering why this does not:

CREATE TABLE sunspots
(
  sunspots_key int4 NOT NULL,
- -- DEFAULT nextval('sunspot_seq'::regclass)
  date date NOT NULL,
  "time" time NOT NULL,
  groups int2,
  spots int2,
  seeing int2,
  ruhe int2,
  tinygrps int2,
  remarks varchar(85),
  "R" int2,
  groups_s int2,
  spots_s int2,
  scientist_id int4
- --  ,CONSTRAINT sunspot_pkey PRIMARY KEY (sunspots_key),
- --  CONSTRAINT fk_sunspot_scientist FOREIGN KEY (scientist_id)
- -- REFERENCES scientist (scientist_key) ON UPDATE NO ACTION ON
- -- DELETE NO ACTION,
- -- CONSTRAINT sunspot_seeing_fkey FOREIGN KEY (seeing) REFERENCES
- -- seeing_quality (seeing10) ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

insert into sunspots (sunspots_key, date, time, remarks, R) values
(99999, '2006-08-30', '12:00:00', 'no comments', 30);

causes:
ERROR:  column "r" of relation "sunspots" does not exist

regards,
Andy

Tom Lane wrote:
> "Andreas Langegger" <al@jku.at> writes:
>> It seems that the column name "R" or "r" is reserved.
>
> Hardly.
>
>> If I want to insert tuples I get the error message:
>
>> ERROR:  column "r" of relation "xyz" does not exist
>
> Worksforme:
>
> regression=# create table xyz(r int);
> CREATE TABLE
> regression=# insert into xyz(r) values(42);
> INSERT 0 1
>
> Perhaps you could provide a self-contained test case?
>
>             regards, tom lane

- --
- ----------------------------------------------------------------------
Dipl.-Ing.(FH) Andreas Langegger
Institute of Applied Knowledge Processing
Johannes Kepler University Linz
A-4040 Linz, Altenberger Straße 69
> http://www.faw.at
> http://www.langegger.at
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)

iD8DBQFE9bBQKk9SuaNc5+IRAt0IAKCo1h9uGjqChF2LE/4ab3U9/gnOkACgycdZ
3TRNTKNtsfI1B5iB5Lym5hA=
=ahUA
-----END PGP SIGNATURE-----