id and ID in CREATE TABLE
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
I forgot about "" Sorry. So if I would use names quoted then my questions
are obsolete. Except one:
So actually the only strange part would be PostgreSQL is folding to lower
cases a column name ...
From docs:
The folding of unquoted names to lower case in PostgreSQL is
incompatible with the SQL standard, which says that unquoted names should
be folded to upper case. Thus, foo should be equivalent to "FOO" not
"foo" according to the standard. If you want to write portable
applications you are advised to always quote a particular name or never
quote it.
Why is like this ? Why not letting them upper case if they are not quoted
?
stefan
On Sat, 20 Jul 2002 stefan@extum.com wrote:
Show quoted text
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 foundCan 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