Thread: 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
> 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. I think your examples have proved that column names are in fact very much case sensitive. However, you will need to double quote mixed case names: eg. SELECT "ID" FROM ttt2; > As well if I have name and Name it should not matter for SQL. Well it does in Postgresql. I highly recommend you just use lowercase field names. > 2. Why psql converts from upper case in lower case column name ID ? > Like in the first case. Because you didn't double quote it. > 3. How comes that first using psql I cannot create the column name ID but > using pgaccess I can ? Is this a bug ? Because pgaccess is doing the double quoting for you. Chris
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: > > 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 > > >
Joshua b. Jore ; http://www.greentechnologist.org On Sat, 20 Jul 2002 stefan@extum.com wrote: > Why is like this ? Why not letting them upper case if they are not quoted > ? > > stefan Well.. this is because pgadmin created the attribute as "ID" originally so for you - it's always been double-quoted to force the case. I never quote my identifier literals just to avoid all the strange problems (like this) that happen when you do it. It's just a bad idea. Josh
On Sat, Jul 20, 2002 at 10:39:52AM +0300, stefan@extum.com wrote: > > 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 ... [snip] > Why is like this ? Why not letting them upper case if they are not quoted > ? I think it's because many people think that uppercase column names suck. And I agree with them. If you follow the given advice (either always quote column names or never) then not only will your program work, it's will be completely portable. So, if you'd used your CREATE TABLE statememnt below, you wouldn't have had this problem. > > CREATE TABLE ttt ( > > > > ID int2, > > name text ); -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
stefan@extum.com wrote: > > 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 > ? Well, historically, we always did lower case, and all-upper case looks ugly. That's about it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026