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
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 > > >
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.
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
Thanks all for comments. In Oracle and DB2 as far as I know the upper case column names are used when you have no columns quoted.. Indeed it is recommended to use lower cases to avoid mistakes and confusion during porting. Is that right ? But anyway this is not so important, but why upper cases are bad ? Why then Oracle , IBM is using them and why the SQL standard is not changed ? stefan On Fri, 19 Jul 2002, Martijn van Oosterhout wrote: > 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 ); >
> 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
stefan@extum.com writes:
> But anyway this is not so important, but why upper cases are bad ?
It's well established that all-lower-case text is more readable than
all-upper-case ... at least in English; but I think the same would be
true of any language using an approximately Roman alphabet. The problem
with upper case is there's less variation in the overall letter shape.
If you don't care to dig in the academic literature about it, here's a
simple experiment: which of the following paragraphs do you find more
readable?
it's well established that all-lower-case text is more readable
than all-upper-case ... at least in english; but i think the
same would be true of any language using an approximately roman
alphabet. the problem with upper case is there's less variation
in the overall letter shape. if you don't care to dig in the
academic literature about it, here's a simple experiment: which
of the following paragraphs do you find more readable?
IT'S WELL ESTABLISHED THAT ALL-LOWER-CASE TEXT IS MORE READABLE
THAN ALL-UPPER-CASE ... AT LEAST IN ENGLISH; BUT I THINK THE
SAME WOULD BE TRUE OF ANY LANGUAGE USING AN APPROXIMATELY ROMAN
ALPHABET. THE PROBLEM WITH UPPER CASE IS THERE'S LESS VARIATION
IN THE OVERALL LETTER SHAPE. IF YOU DON'T CARE TO DIG IN THE
ACADEMIC LITERATURE ABOUT IT, HERE'S A SIMPLE EXPERIMENT: WHICH
OF THE FOLLOWING PARAGRAPHS DO YOU FIND MORE READABLE?
For me, at least, the second version takes noticeably more time to read
and is certainly less pleasant. (I suppose that for a non-native
speaker of English, mental translation might slow you down to the point
where you don't notice a difference. If so, try it on a random
paragraph in your own language.)
For PostgreSQL there is also a backwards compatibility issue: if we
change this decision now, we'd cause all kinds of problems for existing
code and databases.
> Why then Oracle , IBM is using them and why the SQL standard is not
> changed ?
The SQL standard's choice in this matter is prehistoric; undoubtedly
it falls out of the days when computer printers only had one type case.
IBM probably still has a residual fondness for those days ;-). But
the rest of the industry figured out that lower case was better
somewhere around 1960, cf Algol-60 which was the first language to spell
its keywords preferentially in lower case.
regards, tom lane
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
On Fri, 19 Jul 2002, Tom Lane wrote: > stefan@extum.com writes: > > But anyway this is not so important, but why upper cases are bad ? > > It's well established that all-lower-case text is more readable than > all-upper-case ... Agreed. Absolutely. But, since the SQL standard says upper case, wouldn't it be useful to at least have a switch (run time, initdb time, or ./configure time???) called something like FOLDTOUPPER (in upper case of course :-) If it's an easy win I'd be willing to do it. I'm not the world's greatest C hacker, but I still remember enough of it to be competant.
scott.marlowe wrote: > On Fri, 19 Jul 2002, Tom Lane wrote: > > > stefan@extum.com writes: > > > But anyway this is not so important, but why upper cases are bad ? > > > > It's well established that all-lower-case text is more readable than > > all-upper-case ... > > Agreed. Absolutely. But, since the SQL standard says upper case, > wouldn't it be useful to at least have a switch (run time, initdb time, > or ./configure time???) called something like FOLDTOUPPER (in upper case > of course :-) > > If it's an easy win I'd be willing to do it. I'm not the world's greatest > C hacker, but I still remember enough of it to be competant. Yea, I guess we could. I think the consensus is that the uppercase default is so weird, we don't know anyone who would want to use it. Would you? -- 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
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Agreed. Absolutely. But, since the SQL standard says upper case,
> wouldn't it be useful to at least have a switch (run time, initdb time,
> or ./configure time???) called something like FOLDTOUPPER (in upper case
> of course :-)
The main problem with this is what do you do with the system catalogs?
If we start folding to upper case then existing clients will break
unless we also rename pg_class to PG_CLASS, etc; and that will break
them anyway if they wrote "pg_class".
I don't believe that conforming to this particular small aspect of the
spec is worth the pain it would cause.
regards, tom lane
On Fri, 19 Jul 2002, Bruce Momjian wrote: > scott.marlowe wrote: > > On Fri, 19 Jul 2002, Tom Lane wrote: > > > > > stefan@extum.com writes: > > > > But anyway this is not so important, but why upper cases are bad ? > > > > > > It's well established that all-lower-case text is more readable than > > > all-upper-case ... > > > > Agreed. Absolutely. But, since the SQL standard says upper case, > > wouldn't it be useful to at least have a switch (run time, initdb time, > > or ./configure time???) called something like FOLDTOUPPER (in upper case > > of course :-) > > > > If it's an easy win I'd be willing to do it. I'm not the world's greatest > > C hacker, but I still remember enough of it to be competant. > > Yea, I guess we could. I think the consensus is that the uppercase > default is so weird, we don't know anyone who would want to use it. > Would you? Not if I had a choice. But it isn't about what I want or what you want, it's about doing what makes postgresql the most useful for the most people. I can see many situations where this would make postgresql a better choice than it is right now for certain projects, like: writing an app to be as portable as possible (i.e. the reason we have standards...) -or- migrating from some other database that follows the standard (oracle does I believe) and don't want to go in and re-write SQL queries from all the front end apps that hit the database. -or- making closed source database mining tools more likely to get along with postgresql I can think of a lot of reasons people could desire this, even though I myself would prefer to NOT have upper case. My other thought on it was whether we could make it case insensitive. Again, settable by some switch. It could attribute names in mixed case, but all comparisons would be done in upper or lower case. But I don't know how much that would cost us in processing power, especially if it had to fold case based on locale where we can't just use a simple bit flip to make everything lower or upper case when parsing it. By the way, there have been messages about the problems with case folding in this mailing list for the last six months about once a month, so if there was a simple switch to make it behave the way people need it to behave instead of the way it currently behaves, answering those questions would be as easy as pointing to the right place in the docs. So, I would propose a run time setting that has the four possible settings: case_handling: fold_to_lower (default) fold_to_upper (i.e. SQL92/99 compatibility mode) case_insensitive (this one may be more work than it's worth) case_sensitive (i.e. unix mode) If case insensitive were implemented it I would recommend we do in a way that allows the database to store the tables in mixed case as put in, but parse internally on lower or upper case only. Is local an issue, or does postgresql expect you to do everything in ascii 8 bit with no funny chars in attribute names?
Well I think I have got the point. True indeed if I would read also in my language, romanian, then I would choose lower-case. I bet there are more interesting things what you guys are working on and I was just curious to ask. I understood replication would be a major benefit to have in 7.3 and as well, maybe, ALTER TABLE fully implemented as SQL92 ? stefan On Fri, 19 Jul 2002, Bruce Momjian wrote: > scott.marlowe wrote: > > On Fri, 19 Jul 2002, Tom Lane wrote: > > > > > stefan@extum.com writes: > > > > But anyway this is not so important, but why upper cases are bad ? > > > > > > It's well established that all-lower-case text is more readable than > > > all-upper-case ... > > > > Agreed. Absolutely. But, since the SQL standard says upper case, > > wouldn't it be useful to at least have a switch (run time, initdb time, > > or ./configure time???) called something like FOLDTOUPPER (in upper case > > of course :-) > > > > If it's an easy win I'd be willing to do it. I'm not the world's greatest > > C hacker, but I still remember enough of it to be competant. > > Yea, I guess we could. I think the consensus is that the uppercase > default is so weird, we don't know anyone who would want to use it. > Would you? > >
On Fri, 19 Jul 2002, Tom Lane wrote: > "scott.marlowe" <scott.marlowe@ihs.com> writes: > > Agreed. Absolutely. But, since the SQL standard says upper case, > > wouldn't it be useful to at least have a switch (run time, initdb time, > > or ./configure time???) called something like FOLDTOUPPER (in upper case > > of course :-) > > The main problem with this is what do you do with the system catalogs? > If we start folding to upper case then existing clients will break > unless we also rename pg_class to PG_CLASS, etc; and that will break > them anyway if they wrote "pg_class". > > I don't believe that conforming to this particular small aspect of the > spec is worth the pain it would cause. But it's not a small aspect if it means someone either can't use an app with postgresql because it was written to spec, or someone has to spend a bunch of time rewriting all their queries to work with postgresql. I'm not talking about something I'd want on all my tables or all my databases, but there are many instances (look over this list's archive and you'll see them) where following spec would help migration issues. Plus the pg_class stuff is kind of a blind ally. If we're looking at foldtoupper as a setting, then we're already admitting that we're doing it to be interchangable with other dbmses. If that's the case, no one is gonna be accessing the pg_* tables, because you wouldn't do that in an app you're writing to be portable. And if you're migrating to postgresql, you won't have anything there that would access pg_* as well. Leave the system catalogs in lower case, and don't fold calls to anything that's a system catalog. Or wrap them all to upper if the fold to upper is set. I don't see this being a setting that many people would change, but then again, most people don't change sql_inheritance or transform_nulls_equals but their both there for the same reason as what I'm talking about. Some other databases do things a little differently, and if we want to be a drop in replacement, we can either accomodate them or thumb our noses at them and tell them it's not our problem. In all honesty, this change is more "right" than transform_nulls_equals since transform_nulls_equals makes postgresql BREAK the SQL standard, and this one would make it follow it.
On Fri, 19 Jul 2002, scott.marlowe wrote: > On Fri, 19 Jul 2002, Tom Lane wrote: > > > "scott.marlowe" <scott.marlowe@ihs.com> writes: > > > Agreed. Absolutely. But, since the SQL standard says upper case, > > > wouldn't it be useful to at least have a switch (run time, initdb time, > > > or ./configure time???) called something like FOLDTOUPPER (in upper case > > > of course :-) > > > > The main problem with this is what do you do with the system catalogs? > > If we start folding to upper case then existing clients will break > > unless we also rename pg_class to PG_CLASS, etc; and that will break > > them anyway if they wrote "pg_class". > > > > I don't believe that conforming to this particular small aspect of the > > spec is worth the pain it would cause. > > But it's not a small aspect if it means someone either can't use an app > with postgresql because it was written to spec, or someone has to spend > a bunch of time rewriting all their queries to work with postgresql. Presumably that's only true if they're mixing quoted and unquoted identifiers, which is a bad idea in any case (since what happens to "foo" and foo - do you trust somebody who's mixing them to get it right everywhere :) ?) or their application is using the names returned elsewhere (which is a problem with doing it as lower). If create table FOO(col1 int, Col2 int, cOL3 int); select COL1 from foo; failed, I think people would be more interested in changing it.
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Plus the pg_class stuff is kind of a blind ally. If we're looking at
> foldtoupper as a setting, then we're already admitting that we're doing it
> to be interchangable with other dbmses. If that's the case, no one is
> gonna be accessing the pg_* tables, because you wouldn't do that in an app
> you're writing to be portable.
<< thinks about that for awhile >>
Hmm. If we are willing to stipulate that the system catalogs stay named
as they are (lower case always), then we could indeed have a runtime
parameter (GUC setting) that controls how individual sessions fold case.
People who need to access the system catalogs regardless can just
double-quote their names.
One fly in the ointment is that we are planning to add the standard's
INFORMATION_SCHEMA views someday, and neither choice of case is going to
be good for them if we have an option like this floating around. Not
sure what to do about that, although I suppose one way out is to provide
two duplicate sets of those views (one named all-upper-case, the other
all-lower).
I think your four-way proposal is gilding a dead lily, though. Let's
just do the historical (downcase) and spec-compatible (upcase) options.
Anything else will just create more confusion, IMHO. The
case-insensitive option is a particularly *bad* idea.
Note it would be a real good idea to fix psql and pg_dump to
double-quote their references to system catalogs, so they don't go
belly-up if invoked on a database where FOLDTOUPPER is true.
(pg_dump could alternatively do SET FOLDTOUPPER = false, but this won't
fly for psql.)
> Leave the system catalogs in lower case, and don't fold calls to anything
> that's a system catalog.
And you will determine that how, exactly, when you don't know what the
identifier (that you haven't parsed yet) refers to?
Keep it simple, or you'll make things far worse than they are now.
regards, tom lane