Thread: column headers all in upper-case letters?
Could you please point me to the fine document where I can information regarding: Is there a way to force PostgreSQL or the jdbc Driver to return column headers all in upper-case letters? e.g. table test with column ID type int: I want that a 'select * from test;' returns 'ID' not 'id' as column header. regards, Reinhard
> SELECT id AS "ID" FROM test > > but why?? sorry, I was not precise. I am looking for a server or driver side configuration option as I don't want to rewrite all my SQL statements (>3500). I am porting an application running on MsSQL/Oracle DBs. >> Could you please point me to the fine document where I can >> information >> regarding: >> >> Is there a way to force PostgreSQL or the jdbc Driver to >> return column >> headers all in upper-case letters? >> >> e.g. table test with column ID type int: >> I want that a 'select * from test;' returns 'ID' not 'id' as >> column header.
By default, Postgresql will convert all table and column names to lower
case; both when you are creating the table and in sql statements that it
processes. To override this, the table and column names must be enclosed
in double quotes, both when the table is created and in all subsequent sql
statements.
fbax=# create table test ("ID" int, id int);
CREATE
fbax=# insert into test values(1,2);
INSERT 6096748 1
fbax=# select * from test;
ID | id
----+----
1 | 2
(1 row)
fbax=# select "ID" from test;
ID
----
1
(1 row)
fbax=# select id from test;
id
----
2
(1 row)
Frank
At 03:32 PM 1/31/02 +0100, Sammer Reinhard J. wrote:
>> SELECT id AS "ID" FROM test
>>
>> but why??
>
>sorry, I was not precise.
>
>I am looking for a server or driver side configuration option
>as I don't want to rewrite all my SQL statements (>3500).
>
>I am porting an application running on MsSQL/Oracle DBs.
>
>>> Could you please point me to the fine document where I can
>>> information
>>> regarding:
>>>
>>> Is there a way to force PostgreSQL or the jdbc Driver to
>>> return column
>>> headers all in upper-case letters?
>>>
>>> e.g. table test with column ID type int:
>>> I want that a 'select * from test;' returns 'ID' not 'id' as
>>> column header.
Sorry, I made a type in my previous example; so that it does not
demonstrate my point properly/clearly. It's subtle, look for it the
'create' statement and last select example.
By default, Postgresql will convert all table and column names to lower
case; both when you are creating the table and in sql statements that it
processes. To override this, the table and column names must be enclosed
in double quotes, both when the table is created and in all subsequent sql
statements.
fbax=# create table test ("ID" int, ID int);
CREATE
fbax=# insert into test values(1,2);
INSERT 6096748 1
fbax=# select * from test;
ID | id
----+----
1 | 2
(1 row)
fbax=# select "ID" from test;
ID
----
1
(1 row)
fbax=# select ID from test;
id
----
2
(1 row)
Frank
At 03:32 PM 1/31/02 +0100, Sammer Reinhard J. wrote:
>> SELECT id AS "ID" FROM test
>>
>> but why??
>
>sorry, I was not precise.
>
>I am looking for a server or driver side configuration option
>as I don't want to rewrite all my SQL statements (>3500).
>
>I am porting an application running on MsSQL/Oracle DBs.
>
>>> Could you please point me to the fine document where I can
>>> information
>>> regarding:
>>>
>>> Is there a way to force PostgreSQL or the jdbc Driver to
>>> return column
>>> headers all in upper-case letters?
>>>
>>> e.g. table test with column ID type int:
>>> I want that a 'select * from test;' returns 'ID' not 'id' as
>>> column header.
on 1/31/02 8:32 AM, Sammer Reinhard J. wrote: >> SELECT id AS "ID" FROM test >> >> but why?? > > sorry, I was not precise. > > I am looking for a server or driver side configuration option > as I don't want to rewrite all my SQL statements (>3500). > > I am porting an application running on MsSQL/Oracle DBs. > Hi, I am a novice as well, but it seems your problem might be better handled at some point between the database and your SQL. What are you using to connect the two? E.g. psql is an application that exists between the database and your SQL statements; it might be the easiest place to force the headers to appear as upper case. best of luck, Bob
Okay, so how do triggers work then ?
I can write a daemon to accept messages from some ip (there already are 2
java daemons on arnold), add it to a transaction list a execute it asap.
What would the message look like ? a line of sql or what ?
On Thu, 31 Jan 2002, Frank Bax wrote:
> By default, Postgresql will convert all table and column names to lower
> case; both when you are creating the table and in sql statements that it
> processes. To override this, the table and column names must be enclosed
> in double quotes, both when the table is created and in all subsequent sql
> statements.
>
> fbax=# create table test ("ID" int, id int);
> CREATE
> fbax=# insert into test values(1,2);
> INSERT 6096748 1
> fbax=# select * from test;
> ID | id
> ----+----
> 1 | 2
> (1 row)
>
> fbax=# select "ID" from test;
> ID
> ----
> 1
> (1 row)
>
> fbax=# select id from test;
> id
> ----
> 2
> (1 row)
>
> Frank
>
> At 03:32 PM 1/31/02 +0100, Sammer Reinhard J. wrote:
> >> SELECT id AS "ID" FROM test
> >>
> >> but why??
> >
> >sorry, I was not precise.
> >
> >I am looking for a server or driver side configuration option
> >as I don't want to rewrite all my SQL statements (>3500).
> >
> >I am porting an application running on MsSQL/Oracle DBs.
> >
> >>> Could you please point me to the fine document where I can
> >>> information
> >>> regarding:
> >>>
> >>> Is there a way to force PostgreSQL or the jdbc Driver to
> >>> return column
> >>> headers all in upper-case letters?
> >>>
> >>> e.g. table test with column ID type int:
> >>> I want that a 'select * from test;' returns 'ID' not 'id' as
> >>> column header.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> --
> Virus scanned by edNET.
>
--
Chris Thompson
lightershade
t: +44 131 466 7003
d: +44 131 625 5603
--
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender. Any
offers or quotation of service are subject to formal specification.
Errors and omissions excepted. Please note that any views or opinions
presented in this email are solely those of the author and do not
necessarily represent those of edNET or lightershade ltd. Finally, the
recipient should check this email and any attachments for the presence of
viruses. edNET and lightershade ltd accepts no liability for any damage
caused by any virus transmitted by this email.
--
--
Virus scanned by edNET.
> > I am looking for a server or driver side configuration option > > as I don't want to rewrite all my SQL statements (>3500). I'm using jdbc.postgresql.org Driver for a Java/JSP application. > > > > I am porting an application running on MsSQL/Oracle DBs. > > > > Hi, I am a novice as well, but it seems your problem might be > better handled > at some point between the database and your SQL. What are you using to > connect the two? E.g. psql is an application that exists between the > database and your SQL statements; it might be the easiest > place to force the > headers to appear as upper case.
> Sorry, I made a type in my previous example; so that it does not
> demonstrate my point properly/clearly. It's subtle, look for it the
> 'create' statement and last select example.
First, thank you.
>
> By default, Postgresql will convert all table and column
> names to lower
> case; both when you are creating the table and in sql
> statements that it
> processes. To override this, the table and column names must
> be enclosed
> in double quotes, both when the table is created and in all
> subsequent sql
> statements.
>
> fbax=# create table test ("ID" int, ID int);
> CREATE
> fbax=# insert into test values(1,2);
> INSERT 6096748 1
sorry for quoting myself:
> >I am looking for a server or driver side configuration option
> >as I don't want to rewrite all my SQL statements (>3500).
I'm using jdbc org.postgresql.Driver jdbc7.1-1.2.jar with a 7.1.3 DB
I don't understand. In your initial message, you asked:
>I want that a 'select * from test;'
>returns 'ID' not 'id' as column header.
In my response, I showed by example that if you create a table using:
create table test ("ID" int);
then
select * from test;
will indeed return 'ID' and not 'id' as the column header.
And yet you appear to remain unsatified? Why? Perhaps we need to know why
is it so important that the column header is returned in upper-case? If
your SQL statements are written completely in upper-case, but without
double-quotes, they will still work with lower-case column and table names.
If you see different behaviour using the jdbc interface, then perhaps your
query should be directed to pgsql-interfaces@postgresql.org where you'll
find people who know more about jdbc.
Frank
At 07:27 PM 1/31/02 +0100, Sammer Reinhard J. wrote:
>> Sorry, I made a type in my previous example; so that it does not
>> demonstrate my point properly/clearly. It's subtle, look for it the
>> 'create' statement and last select example.
>
>First, thank you.
>
>>
>> By default, Postgresql will convert all table and column
>> names to lower
>> case; both when you are creating the table and in sql
>> statements that it
>> processes. To override this, the table and column names must
>> be enclosed
>> in double quotes, both when the table is created and in all
>> subsequent sql
>> statements.
>>
>> fbax=# create table test ("ID" int, ID int);
>> CREATE
>> fbax=# insert into test values(1,2);
>> INSERT 6096748 1
>
>sorry for quoting myself:
>> >I am looking for a server or driver side configuration option
>> >as I don't want to rewrite all my SQL statements (>3500).
>
>I'm using jdbc org.postgresql.Driver jdbc7.1-1.2.jar with a 7.1.3 DB
> I don't understand. In your initial message, you asked:
> >I want that a 'select * from test;'
> >returns 'ID' not 'id' as column header.
>
> In my response, I showed by example that if you create a table using:
> create table test ("ID" int);
> then
> select * from test;
> will indeed return 'ID' and not 'id' as the column header.
>
> And yet you appear to remain unsatified? Why? Perhaps we
> need to know why
> is it so important that the column header is returned in
> upper-case? If
> your SQL statements are written completely in upper-case, but without
> double-quotes, they will still work with lower-case column
> and table names.
Good guess.
I'm porting a Java application.
Defined variables for column headers are all upper-case because
that's MsSQL/Oracle behavior.
Java is case-sensitive.
If the column header is returned in upper-case, I do neither rewrite my
SQL class (well, in the end I did that...) nor my SQL statements (which
are btw in XML - so using '"' for quoting is a pain in the a.. too ;)