Thread: Can I turn the case sensitive off
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!
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
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
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.
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
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
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 | +-----------------------------------------------------------------+
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. -------------------------------------------------
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
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