id and ID in CREATE TABLE

From: stefan(at)extum(dot)com
To: pgsql-sql(at)postgresql(dot)org
Cc: pgsql-general(at)postgresql(dot)org
Subject: id and ID in CREATE TABLE
Date: 2002-07-20 07:15:35
Message-ID: Pine.LNX.4.44.0207201002310.3355-100000@extum.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2002-07-20 07:37:14 Re: Linux max on shared buffers?
Previous Message Tim 2002-07-20 05:54:14 Re: RAD web development with PostgreSQL?

Browse pgsql-sql by date

  From Date Subject
Next Message stefan 2002-07-20 07:39:52 Re: id and ID in CREATE TABLE
Previous Message Tom Lane 2002-07-19 20:16:53 Re: [SQL] id and ID in CREATE TABLE