BUG #2598: Columns named R are not accessible - although R is not a keyword

"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

-----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-----

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