Oracle and DB2 treat all object names in a case-insensitive manner, and to that end store all object names in upper case in the data dictionary. As such, field names, unless an alias is provided using AS, come back in all upper case for those databases. MS SQL Server returns the field name as you've used it in the query, so no aliasing is necessary there. Dunno about MySQL and Postgres though, but regardless, there doesn't seem to be much in the way of consistency unless an alias is provided using AS.
-Tom > -----Original Message----- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Monday, April 25, 2005 9:37 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Trouble with column names > > On Mon, 2005-04-25 at 15:12 +0300, George Ionescu wrote: > > sqlite3 test.db > > create table test(Field1 text(20)); > > insert into test values ('a value'); > > select field1 from test; > > > > Please tell me what *you* think the column name should be in > the following cases. Also, please tell me what PostgreSQL and > MySQL and perhaps Oracle do. The decision of how to name > result columns is complex and I am unwilling to go about > making random changes here and there in whack-a-mole fashion. > Until there is a specification on what column names should > actually be, I will decline to change anything. > > select field1 from test; > select "field1" from test; > select FIELD1 from test; > select "FIELD1" from test; > select [field1] FROM test; > select test.field1 from test; > select a.field1 from test as a; > select "a".field1 from test as a; > select "a"."field1" from test as a; > select main.test1.field1 from test; > select [main]."a".[field1] from test as a; > > This problem is related to the naming of columns in views. > Please tell me what the column names for the following views > should be: > > create view v1 as select field1 from test; > create view v2 as select FIELD1 from test; > create view v3 as select a.field1 from test as a; > create view v4 as select test1.field1 from test; > create view v5 as select "FIELD1" from test; > create view v6 as select "A"."FIELD1" from test as a; > create view v7 as select "a".field1 + 5 FROM test as a; > create view v8 as select "a".field1+5 from test as a; > > Another place where naming is an issue is on CREATE TABLE AS > statements. What is the name of the column in each of the > following tables: > > create table t2 as select field1 from test; > create table t3 as select FIELD1 from test; > create table t4 as SELECT a.field1 from test as a; > -- and so forth > > For the above, what is the column name reported out when > I do: > > select * from t2; > select * from t3; -- and so forth > > or > > select * from v1; > select * from v2; -- and so forth > > If someone will come up with a coherent set of column naming > rules - rules which other popular SQL database engines follow - > then I will be happy to implement them in SQLite. Doing so will > probably break a lot of existing code. But as the column naming > rules have never been specified or documented before, I'd be > willing to do it since any code that breaks would have been > depending on undefined behavior to begin with. > > Does the SQL standard have anything to say about this? > Does anybody know? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > >
