Thread: Uppercase field names not found

I've seen a few comments of people encountering this problem, but not
yet a solution:

Upper case field names in commands are forced to lower case and then not
found.

I was able to create a table with a field  called "ABC".

"SELECT ABC FROM mytable;"
emits the error
"no such attribute or function abc"

On Mon, 2002-06-17 at 20:25, Scot Wilcoxon wrote:
> I've seen a few comments of people encountering this problem, but not
> yet a solution:
>
> Upper case field names in commands are forced to lower case and then not
> found.
>
> I was able to create a table with a field  called "ABC".
>
> "SELECT ABC FROM mytable;"
> emits the error
> "no such attribute or function abc"


If it is possible, you will have a much easier time if you change you
schema to use case folded names

EG:
CREATE TABLE Foo ( ... )
rather than: CREATE TABLE "Foo" ( ... )

otherwise.. You will need to change your code to quote all references to
the name:

EG:
$q = 'SELECT * from "Foo"';

If you don't put ["]'s around the object identifier, it gets case folded
to lower case.

--
David Stanaway

 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT "ABC" FROM mytable;

>> -----Original Message-----
>> From: Scot Wilcoxon [mailto:scot@wilcoxon.org]
>> Sent: 18 June 2002 02:26
>> To: pgsql-admin@postgresql.org
>> Subject: [ADMIN] Uppercase field names not found
>>
>>
>> I've seen a few comments of people encountering this
>> problem, but not
>> yet a solution:
>>
>> Upper case field names in commands are forced to lower case
>> and then not
>> found.
>>
>> I was able to create a table with a field  called "ABC".
>>
>> "SELECT ABC FROM mytable;"
>> emits the error
>> "no such attribute or function abc"
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister
>> command
>>     (send "unregister YourEmailAddressHere" to
>> majordomo@postgresql.org)
>>

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBPQ82yXympNV/C086EQKWpgCZAYVy2AU0lrWUnyGXwxAZ72HsQxEAnR8C
Auyrcz3fyV+nT94gyPtaloE6
=yEfg
-----END PGP SIGNATURE-----

This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.


So I suppose since he's referring to a field, to pull it in all
uppercase, he'd use...
SELECT "ABC" from mytable;
Am I correct?

David Stanaway wrote:

> On Mon, 2002-06-17 at 20:25, Scot Wilcoxon wrote:
>
>>I've seen a few comments of people encountering this problem, but not
>>yet a solution:
>>
>>Upper case field names in commands are forced to lower case and then not
>>found.
>>
>>I was able to create a table with a field  called "ABC".
>>
>>"SELECT ABC FROM mytable;"
>>emits the error
>>"no such attribute or function abc"
>>
>
>
> If it is possible, you will have a much easier time if you change you
> schema to use case folded names
>
> EG:
> CREATE TABLE Foo ( ... )
> rather than: CREATE TABLE "Foo" ( ... )
>
> otherwise.. You will need to change your code to quote all references to
> the name:
>
> EG:
> $q = 'SELECT * from "Foo"';
>
> If you don't put ["]'s around the object identifier, it gets case folded
> to lower case.
>
> --
> David Stanaway
>

On Tue, 2002-06-18 at 16:58, Digital Wokan wrote:
> So I suppose since he's referring to a field, to pull it in all
> uppercase, he'd use...
> SELECT "ABC" from mytable;
> Am I correct?


Yes.  Same with functions too I guess.  Sequences were tricky,
I think.. you needed:   currval('"mytable_ABC_seq"')

I avoid non-lower case names personally. It is not very portable to rely
on that in your application and will be confusing if anyone else messes
with your code.

--
David Stanaway