Date: Sat, 20 Jul 2002 10:15:35 +0300 (EEST)
From: stefan@extum.com
To: pgsql-sql@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: id and ID in CREATE TABLE
Message-ID: <Pine.LNX.4.44.0207201002310.3355-100000@extum.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


Hello all,

It might be not a correct place to post this. I am creating a table from 
psql. Everything is fine except I got some troubles when trying to create 
the same table but in a different way and with pgaccess.

If I have  this sql:


CREATE TABLE ttt (

	ID int2,
	name text );

from psql the ID comes into id in the table. The SQL statements work fine 
then. If I create the same table using pgaccess the table looks like:

         Table "ttt2"
 Column |   Type   | Modifiers 
--------+----------+-----------
 ID     | smallint | 
 name   | text     | 


After this if I INSERT and SELECT something the results are not the same 
anymore:

TEST1=# INSERT INTO ttt2 VALUES (1,'ttttl');
INSERT 17001 1
TEST1=# select * from ttt2;
 ID | name  
----+-------
  1 | ttttl
(1 row)

TEST1=# select ID from ttt2;
ERROR:  Attribute 'id' not found

Can somebody explain me a bit about:

1. As far as I know column names in Tables are not case sensitive. Correct ?
So I know if I pickup ID is not a clever idea but for this example it is ok.
As well if I have name and Name it should not matter for SQL.

2. Why psql converts from upper case in lower case column name ID ?
Like in the first case.

3. How comes that first using psql I cannot create the column name ID but 
using pgaccess I can ?  Is this a bug ?


Some ideas ?
stefan