Re: Bug #891: CAP letters

On Tue, 2003-02-04 at 21:00, pgsql-bugs(at)postgresql(dot)org wrote:
> venu (madhavaduri(at)rediffmail(dot)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(at)lists(dot)postgresql(dot)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/
---------------------------------------------------------------------