Thread: Bug #891: CAP letters

Bug #891: CAP letters

From
pgsql-bugs@postgresql.org
Date:
venu (madhavaduri@rediffmail.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
CAP letters

Long Description
i want to port SQL server database to Postgresql,while porting i am encouting problem with capital letter table names
andfield names. 

 is it case sensitive??is there any other alternative for this.

even if i create a fieldname in Capital letters,i am unable to retrieve the data in that field.


Sample Code


No file was uploaded with this report

Re: Bug #891: CAP letters

From
Andrew McMillan
Date:
On Tue, 2003-02-04 at 21:00, pgsql-bugs@postgresql.org wrote:
> venu (madhavaduri@rediffmail.com) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> CAP letters
>
> Long Description
> i want to port SQL server database to Postgresql,while porting i am
> encouting problem with capital letter table names and field names.
>
>  is it case sensitive??is there any other alternative for this.
>
> even if i create a fieldname in Capital letters,i am unable to retrieve
> the data in that field.

It is not a bug to do things differently to SQL server :-)

Novice questions like this are best asked on the
pgsql-novice@lists.postgresql.org mailing list.


PostgreSQL lower-cases all identifiers where double quotes are not used
around the identifier.

mytable -> mytable
MYTABLE -> mytable
MyTable -> mytable

"MyTable" -> MyTable


Many other databases seem to prefer uppercase, and to case fold upwards
instead of downwards.

Basically:

If you want your names to be uppercase, define them as:
    "MYNAME"
and that will be used literally.

If you don't care, define them as:
    MYNAME, MyName or myname
and they will be folded to lowercase.

If the names are in lowercase and you want to refer to them in a query
then they will be folded unless quoted:

SELECT * FROM MYTABLE }
SELECT * FROM mytable } - same effect (i.e. "mytable")
SELECT * FROM MyTable }

SELECT * FROM "MyTable" - different (i.e. "MyTable").


In converting from SQL Server you probably want to either ensure all
identifiers are always quoted, or that they never are.  Hopefully you
don't have table names that need to be "MyTableName" and can just lose
the quotes along with your headache.

Regards,
                    Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------