Thread: Can I turn the case sensitive off

Can I turn the case sensitive off

From
"Terence Chang"
Date:
All:
 
I don't remember I even seen a document saying PostgreSQL are case sensitive. I just figure out that my column name are case sensitive. Is there any way that I can turn it off or force the object name to all upper case?
 
Also, I have seen a lot of people having problem troubles to insert unicode into DB, but no clear answer out there.
I guess that I have been searching with wrong keyword or place. Is there a FAQ for this unicode issue? I did not see a Nchar or Nvarchar data type. What data type should I use?
 
thanks!
 

Re: Can I turn the case sensitive off

From
Arguile
Date:
On Thu, 2003-07-24 at 19:34, Terence Chang wrote:
> I don't remember I even seen a document saying PostgreSQL are case sensitive.
> I just figure out that my column name are case sensitive. Is there any
> way that I can turn it off or force the object name to all upper case?
PostgreSQL is case sensitive if identifiers are quoted, otherwise it
folds to lower case.

    SELECT foo, FOo, Foo, fOO          -- all fold down to "foo"
    SELECT "foo", "FOo", "Foo", "fOO"  -- are all different

Not sure why lower was chosen over upper, probably a legibility issue. I
might be mistaken but I think the standard calls for upper; personally I
like the current system.

> Also, I have seen a lot of people having problem troubles to insert unicode
> into DB, but no clear answer out there. I guess that I have been
> searching with wrong keyword or place. Is there a FAQ for this unicode
> issue? I did not see a Nchar or Nvarchar data type. What data type
> should I use?

Any of these:
http://developer.postgresql.org/docs/postgres/datatype-character.html

Try reading this:
http://developer.postgresql.org/docs/postgres/multibyte.html


Re: Can I turn the case sensitive off

From
"Terence Chang"
Date:
I am still getting the error. would this matter with 7.3.3 on windows with
cygwin?

My query only works when I quote the field. Also I have to always use the
schema name in the where clause. Is there any way that I can set default
schema to "app_v08" but not public? Thank you very much!

My table users contains a field "FIRSTNAME" in upper case.
app=> select FIRSTNAME from api_v08.users;
ERROR:  Attribute "firstname" not found
app=> select "firstname" from app_v08.users;
ERROR:  Attribute "firstname" not found
app=> select a.firstname from app_v08.users a;
ERROR:  No such attribute a.firstname
app=> select a.FIRSTNAME from app_v08.users a;
ERROR:  No such attribute a.firstname
app=> select "A"."FIRSTNAME" FROM app_v08.users A;
ERROR:  Relation "A" does not exist
app=> select "FIRSTNAME" FROM app_v08.users;
 FIRSTNAME
-----------
 Terence
(1 row)

app=> select "FIRSTNAME" FROM users;
ERROR:  Relation "users" does not exist



Re: Can I turn the case sensitive off

From
Stephan Szabo
Date:
On Thu, 24 Jul 2003, Terence Chang wrote:

> I am still getting the error. would this matter with 7.3.3 on windows with
> cygwin?
>
> My query only works when I quote the field. Also I have to always use the
> schema name in the where clause. Is there any way that I can set default
> schema to "app_v08" but not public? Thank you very much!
>

See the search_path variable.

> My table users contains a field "FIRSTNAME" in upper case.

Generally speaking, if you quote a name when you create it you should
quote the name when referencing it. If we used spec complient
case-folding, then you'd be able to reference "FIRSTNAME" without quotes,
but "firstname" or "Firstname" would still require quotes, so it's best to
be consistent.



Re: Can I turn the case sensitive off

From
Thomas Kellerer
Date:
Terence Chang schrieb:
> I am still getting the error. would this matter with 7.3.3 on windows with
> cygwin?
>
 From my experience I'd never user quotes at any place (neither during creation
of the table nor in the SELECT, UPDATE statements). All DBMS I know behave like
Postgres. So if you never quote your object names, then you won't have problems.

Thomas


Re: Can I turn the case sensitive off

From
Andrew Sullivan
Date:
On Thu, Jul 24, 2003 at 04:34:26PM -0700, Terence Chang wrote:
> All:
>
> I don't remember I even seen a document saying PostgreSQL are case
> sensitive. I just figure out that my column name are case

The docs have it in a footnote:

http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-syntax.html#FTN.AEN1031

PostgreSQL's approach is backwards from the standard.

> sensitive. Is there any way that I can turn it off or force the
> object name to all upper case?

Yes.  Double-quote them.  Alternatively, _never_ double-quote and
just refer to everything in all upper case.  Postgres will
automatically fold them to lower case anyway, but you won't get them
displayed in all upper case.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: Can I turn the case sensitive off

From
Ron Johnson
Date:
On Fri, 2003-07-25 at 07:28, Andrew Sullivan wrote:
> On Thu, Jul 24, 2003 at 04:34:26PM -0700, Terence Chang wrote:
> > All:
> >
> > I don't remember I even seen a document saying PostgreSQL are case
> > sensitive. I just figure out that my column name are case
>
> The docs have it in a footnote:
>
> http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-syntax.html#FTN.AEN1031
>
> PostgreSQL's approach is backwards from the standard.
>
> > sensitive. Is there any way that I can turn it off or force the
> > object name to all upper case?
>
> Yes.  Double-quote them.  Alternatively, _never_ double-quote and
> just refer to everything in all upper case.  Postgres will
> automatically fold them to lower case anyway, but you won't get them
> displayed in all upper case.

Is there any firm discussion about making this more standard?  I'm
sure that as PG becomes more popular, more people will get bitten
by this.

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+



Re: Can I turn the case sensitive off

From
"Reuben D. Budiardja"
Date:
On Friday 25 July 2003 02:37 am, Thomas Kellerer wrote:
> Terence Chang schrieb:
> > I am still getting the error. would this matter with 7.3.3 on windows
> > with cygwin?
>
>  From my experience I'd never user quotes at any place (neither during
> creation of the table nor in the SELECT, UPDATE statements). All DBMS I
> know behave like Postgres. So if you never quote your object names, then
> you won't have problems.

FWIW:
I ran into this problem before. I used to develop using Oracle, where column
name fold to UPPER case. So in my habits, I created table using pgaccess and
type them in UPPER case for both column name and table name

Then I could not access from psql. After banging my head to the wall for
couple days, I then realize I have to use the double quotes. So somehow
pgaccess write the create table statements using doble quotes.

RDB


--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN
-------------------------------------------------
/"\  ASCII Ribbon Campaign against HTML
\ /  email and proprietary format
 X   attachments.
/ \
-------------------------------------------------
Have you been used by Microsoft today?
Choose your life. Choose freedom.
Choose LINUX.
-------------------------------------------------


Re: Can I turn the case sensitive off

From
Tom Lane
Date:
Ron Johnson <ron.l.johnson@cox.net> writes:
> On Fri, 2003-07-25 at 07:28, Andrew Sullivan wrote:
>> The docs have it in a footnote:
>> http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-syntax.html#FTN.AEN1031
>>
>> PostgreSQL's approach is backwards from the standard.

> Is there any firm discussion about making this more standard?

It's been discussed (see the archives).  It's not changing in the
foreseeable future.

            regards, tom lane

Re: Can I turn the case sensitive off

From
"Terence Chang"
Date:
This is exactly what I was doing. I use PostgreSQL Manager Pro. The tool
covert all my column name and table name in the double quote. So I have all
my column/table/function created in upper case (Oracle habit). Now, I have
to quote all of them.

I should stay with psql, I guess. :-)

Thanks! At least I know there are people like out there.. :-)
======================================================
>Then I could not access from psql. After banging my head to the wall for
>couple days, I then realize I have to use the double quotes. So somehow
>pgaccess write the create table statements using doble quotes.

>RDB