pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCase Preservation disregarding case sensitivity?
9+ messages / 6 participants
[nested] [flat]
* Case Preservation disregarding case sensitivity?
@ 2006-10-27 23:23 beau hargis <beauh@bluefrogmobile.com>
2006-10-28 01:50 ` Re: Case Preservation disregarding case sensitivity? Frank Bax <fbax@sympatico.ca>
2006-10-28 02:38 ` Re: Case Preservation disregarding case sensitivity? Joe <dev@freedomcircle.net>
2006-10-28 14:38 ` Re: Case Preservation disregarding case sensitivity? Richard Broersma Jr <rabroersma@yahoo.com>
2006-10-28 14:59 ` Re: Case Preservation disregarding case sensitivity? Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 4 replies; 9+ messages in thread
From: beau hargis @ 2006-10-27 23:23 UTC (permalink / raw)
To: pgsql-sql
Hello all.
As this is my first post to this particular mailing list, let me preface this
with a couple of items:
1) I have, indeed, searched far and wide for the answer to my question. I have
read and understood the responses to similar posts posing similar questions.
My next stop after this is the postgresql source code.
2) I develop database driven applications for telephony and mobile
applications in C/C++, perl and python. The databases I am working with
exceed 100GB. We have several production database systems.
3) I am pushing hard to replace our MySQL installations with PostgreSQL
(open-source and/or commercial) because Postgres has performed better by
several orders of magnitude and has been more stable and as consumed half the
resources in my testing.
The behavior I am seeing in Postgres in my development and migration process
is a showstopper. The manner in which Postgres handles case-sensitivity
breaks the current tools that I use and the applications that have been
written.
I am aware of the double-quote 'feature' which indicates that an element
should be treated in a case-sensitive way. This as been the 'answer' to every
question of this sort. This 'feature' does not solve the problem and
introduces other problems.
I have a data set that is designed in such a way as:
CREATE TABLE user_profile (
userProfileId int8 not null primary key,
userProfileTypeId int8 not null
);
CREATE TABLE user_profile_type (
userProfileTypeId int8 not null primary key,
userProfileType varchar not null
);
Ok, the description of the table from the database:
Table "public.user_profile"
Column | Type | Modifiers
-------------------+--------+-----------
userprofileid | bigint | not null
userprofiletypeid | bigint | not null
Indexes:
"user_profile_pkey" PRIMARY KEY, btree (userprofileid)
Ok, adding a foreign key:
ALTER table user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
(userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);
...works successfully.
Works as it is supposed to work. Postgres is supposed to be case sensitive,
which is not a problem, but I want it to preserve the case of the column
names.
Every search has turned up the suggestion of using double-quotes around
identifiers in the creation of the tables. So we'll try that:
DROP TABLE user_profile;
DROP TABLE user_profile_type;
CREATE TABLE user_profile (
"userProfileId" int8 not null primary key,
"userProfileTypeId" int8 not null
);
CREATE TABLE user_profile_type (
"userProfileTypeId" int8 not null primary key,
"userProfileType" varchar not null
);
The table description:
Table "public.user_profile"
Column | Type | Modifiers
-------------------+--------+-----------
userProfileId | bigint | not null
userProfileTypeId | bigint | not null
Indexes:
"user_profile_pkey" PRIMARY KEY, btree ("userProfileId")
Case was preserved. Now lets add the foreign key just as we did before (note
that the case in the table definition and the ALTER TABLE query is the same):
ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
(userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);
ERROR: column "userprofiletypeid" referenced in foreign key constraint does
not exist
The case was preserved, but the case-insensitivity only works one way. The
case of the input identifier was 'normalized' in a way, but not matched
against the column names in a case-insensitive way. This is the behavior that
breaks the tools that I use. I cannot modify these tools to add quotes around
the identifiers, and in my mind I should not have to.
OK, another query (perfectly valid SQL):
insert into user_profile_type
(userProfileTypeId,userProfileType) VALUES(1,'ABNORMAL');
ERROR: column "userprofiletypeid" of relation "user_profile_type" does not
exist
I am hoping that there is an easy way to obtain case-preservation with
case-insensitivity, or at the very least, case-preservation and complete
case-sensitivity, or case-preservation and a consistant case-conversion
strategy.
The case of the column names need to be preserved because that is the way the
schema is designed and most importantly (VERY, VERY IMPORTANT), column names
are used in apps as hash values, or as named references which are case
sensitive and as such need to be delivered to the client in exactly in the
manner specified at the time of table creation.
Again, I am looking for a way (magic, patches, whiskey, etc) that will give me
case-preservation with EITHER case-sensitivity OR case-insensitivity, but not
both as I am seeing.
Thanks in advance. I am hoping to find a solution to this so I can actually
convert one of our databases to use Postgres. And I can say that little
issues like this are precisely why Postgres was never used in this
organization before, even though several of the other database developers
like the features, stability and performance of Postgres.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Preservation disregarding case sensitivity?
2006-10-27 23:23 Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
@ 2006-10-28 01:50 ` Frank Bax <fbax@sympatico.ca>
3 siblings, 0 replies; 9+ messages in thread
From: Frank Bax @ 2006-10-28 01:50 UTC (permalink / raw)
To: pgsql-sql
At 07:23 PM 10/27/06, beau hargis wrote:
>I am aware of the double-quote 'feature' which indicates that an element
>should be treated in a case-sensitive way. This as been the 'answer' to every
>question of this sort. This 'feature' does not solve the problem and
>introduces other problems.
If you use double-quotes when creating the table, you need to use
double-quotes EVERY time you access those elements. Neither of your two
examples (that produced errors) have double quotes.
>ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
>(userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);
>
>ERROR: column "userprofiletypeid" referenced in foreign key constraint does
>not exist
>insert into user_profile_type
>(userProfileTypeId,userProfileType) VALUES(1,'ABNORMAL');
>
>ERROR: column "userprofiletypeid" of relation "user_profile_type" does not
>exist
The second query should be:
insert into user_profile_type ("userProfileTypeId","userProfileType")
VALUES(1,'ABNORMAL');
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Preservation disregarding case sensitivity?
2006-10-27 23:23 Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
@ 2006-10-28 02:38 ` Joe <dev@freedomcircle.net>
2006-10-31 03:28 ` Re: Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
3 siblings, 1 reply; 9+ messages in thread
From: Joe @ 2006-10-28 02:38 UTC (permalink / raw)
To: beau hargis <beauh@bluefrogmobile.com>; +Cc: pgsql-sql
Hi Beau,
On Fri, 2006-10-27 at 16:23 -0700, beau hargis wrote:
> I am hoping that there is an easy way to obtain case-preservation with
> case-insensitivity, or at the very least, case-preservation and complete
> case-sensitivity, or case-preservation and a consistant case-conversion
> strategy.
>
> The case of the column names need to be preserved because that is the way the
> schema is designed and most importantly (VERY, VERY IMPORTANT), column names
> are used in apps as hash values, or as named references which are case
> sensitive and as such need to be delivered to the client in exactly in the
> manner specified at the time of table creation.
>
> Again, I am looking for a way (magic, patches, whiskey, etc) that will give me
> case-preservation with EITHER case-sensitivity OR case-insensitivity, but not
> both as I am seeing.
>
> Thanks in advance. I am hoping to find a solution to this so I can actually
> convert one of our databases to use Postgres. And I can say that little
> issues like this are precisely why Postgres was never used in this
> organization before, even though several of the other database developers
> like the features, stability and performance of Postgres.
I went through the same issue in my conversion from MySQL to Postgres
and (since I had a small application) I ended up changing up all my
tables and columns "UserProfile" to user_profile.
I'm afraid however, that it's MySQL that is the odd man out. I haven't
researched this completely but I believe PG follows either the FIPS-127
or SQL-92 standard with respect to what are called "delimited
identifiers". Basically, this says if you want case sensitivity in
identifier names, you have to use double quotes wherever you refer to
the identifier. Without the double quotes, the SQL implementor can
either use UPPERCASE (as I believe Oracle and DB2 do) or lowercase (as
PG does) when it displays those identifiers.
Joe
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Preservation disregarding case sensitivity?
2006-10-27 23:23 Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
2006-10-28 02:38 ` Re: Case Preservation disregarding case sensitivity? Joe <dev@freedomcircle.net>
@ 2006-10-31 03:28 ` beau hargis <beauh@bluefrogmobile.com>
2006-10-31 03:23 ` Re: Case Preservation disregarding case sensitivity? Tom Lane <tgl@sss.pgh.pa.us>
2006-11-14 18:43 ` Re: Case Preservation disregarding case sensitivity? Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 9+ messages in thread
From: beau hargis @ 2006-10-31 03:28 UTC (permalink / raw)
To: pgsql-sql; pgsql-hackers@postgresql.org
On Friday 27 October 2006 19:38, Joe wrote:
> Hi Beau,
>
> On Fri, 2006-10-27 at 16:23 -0700, beau hargis wrote:
> > I am hoping that there is an easy way to obtain case-preservation with
> > case-insensitivity, or at the very least, case-preservation and complete
> > case-sensitivity, or case-preservation and a consistant case-conversion
> > strategy.
> >
> > The case of the column names need to be preserved because that is the way
> > the schema is designed and most importantly (VERY, VERY IMPORTANT),
> > column names are used in apps as hash values, or as named references
> > which are case sensitive and as such need to be delivered to the client
> > in exactly in the manner specified at the time of table creation.
> >
>
> I went through the same issue in my conversion from MySQL to Postgres
> and (since I had a small application) I ended up changing up all my
> tables and columns "UserProfile" to user_profile.
>
> I'm afraid however, that it's MySQL that is the odd man out. I haven't
> researched this completely but I believe PG follows either the FIPS-127
> or SQL-92 standard with respect to what are called "delimited
> identifiers". Basically, this says if you want case sensitivity in
> identifier names, you have to use double quotes wherever you refer to
> the identifier. Without the double quotes, the SQL implementor can
> either use UPPERCASE (as I believe Oracle and DB2 do) or lowercase (as
> PG does) when it displays those identifiers.
>
> Joe
Again, I am at the same point I was at when searching and searching for
information on the problem, and I am not sure what the SQL standard has to
say about it: I do not, and I dont think that anyone else who is struggling
to deal with the problem does, care about case-sensitivity. I am interested
in case preservation of column names. I do, indeed, want identifiers treated
in a case insensitive way, but I want the case PRESERVED in the table
definitions and I want that case, as preserved, to be reflected in the field
names as returned by the server to any client library that connects and
initiates a query.
Case-preservation is not the same as case-sensitivity; nor is
case-normalization the same as case-insensitivity. What PostgreSQL is doing
is converting any, and all, identifiers to a lower case and then matching
those against the identifiers (as stored in the table definition) in a
case-sensitive manner. It 'normalizes' the case of the identifiers so that it
has a common internal representation; the desires of the programmer and
database architect be damned.
Referenced specification details:
From FIPS-127:
=======================================
3. Delimited identifiers. In the previous ANSI SQL specification, it was not
possible for an application to specify identifiers with spaces or other
special symbols. Also, it was not possible to protect against future assaults
on the name space for (identifier) by additions to the (reserved word) list.
The new facility for (delimited identifier) allows a user to enclose all
identifiers in double-quotation marks, thereby ensuring that the name defined
or referenced may contain spaces or other special symbols and will not be
impacted by future additions to the (reserved word) list.
=======================================
From SQL-92/Sec. 5.2:
=======================================
10)The <identifier body> of a <regular identifier> is equivalent
to an <identifier body> in which every letter that is a lower-
case letter is replaced by the equivalent upper-case letter
or letters. This treatment includes determination of equiva-
lence, representation in the Information and Definition Schemas,
representation in the diagnostics area, and similar uses.
11)The <identifier body> of a <regular identifier> (with every
letter that is a lower-case letter replaced by the equivalent
upper-case letter or letters), treated as the repetition of
a <character string literal> that specifies a <character set
specification> of SQL_TEXT, shall not be equal, according to
the comparison rules in Subclause 8.2, "<comparison predicate>",
to any <reserved word> (with every letter that is a lower-case
letter replaced by the equivalent upper-case letter or letters),
treated as the repetition of a <character string literal> that
specifies a <character set specification> of SQL_TEXT.
12)Two <regular identifier>s are equivalent if their <identifier
body>s, considered as the repetition of a <character string
literal> that specifies a <character set specification> of
SQL_TEXT, compare equally according to the comparison rules
in Subclause 8.2, "<comparison predicate>".
13)A <regular identifier> and a <delimited identifier> are equiva-
lent if the <identifier body> of the <regular identifier> (with
every letter that is a lower-case letter replaced by the equiva-
lent upper-case letter or letters) and the <delimited identifier
body> of the <delimited identifier> (with all occurrences of
<quote> replaced by <quote symbol> and all occurrences of <dou-
blequote symbol> replaced by <double quote>), considered as
the repetition of a <character string literal> that specifies a
<character set specification> of SQL_TEXT and an implementation-
defined collation that is sensitive to case, compare equally
according to the comparison rules in Subclause 8.2, "<comparison
predicate>".
14)Two <delimited identifier>s are equivalent if their <delimited
identifier body>s (with all occurrences of <quote> replaced
by <quote symbol> and all occurrences of <doublequote symbol>
replaced by <doublequote>), considered as the repetition of a
<character string literal> that specifies a <character set spec-
ification> of SQL_TEXT and an implementation-defined collation
that is sensitive to case, compare equally according to the
comparison rules in Subclause 8.2, "<comparison predicate>".
========================================
Having installed DB2 Enterprise today and taking it for a spin, it does indeed
behave in a similar manner. However, after reading through both
specifications, it seems that DB2 follows more of the spec than PostgreSQL.
The specifications state that for purpose of comparing identifiers, both
shall be converted to upper-case. DB2 displays all identifiers in upper-case
whereas PostgreSQL displays all identifiers in lower-case. This alone would
be a deviation from the specification.
It seems, however, that the regular-identifier/delimited-identifier
distinction is for the embedding of spaces or normally invalid characters
from other character sets, not for actually creating case-sensitive
identifiers. It is only a side-effect. It does not enforce any method for the
representation of the identifiers which the client expects, either. Since a
query can be constructed which specifies aliases (... AS ...), the
presentation of the data is implementation/runtime specific.
Considering the differences that already exist between database systems and
their varying compliance with SQL and the various extensions that have been
created, I do not consider that the preservation of case for identifiers
would violate any SQL standard. The end result being eaiser migration, the
continued operation of cross-platform tools and applications, and a database
designer or data architect having more flexibility in the construction and
presentation of a database.
Again, the distinction between case-sensitivity and case-preservation, and,
case-insensitivity and case-normalization needs to be recognized. Having
case-preservation defined in the CREATE TABLE syntax and/or a server
configuration seems, to me, to be a valuable addition. Of course, making it
an option would not interfere with the current state of affairs.
I have looked through the source code and have identified places which need to
be patched to make this possible. Tomorrow I will work on that and see what
it will impact in the code. Let me know what you think.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Preservation disregarding case sensitivity?
2006-10-27 23:23 Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
2006-10-28 02:38 ` Re: Case Preservation disregarding case sensitivity? Joe <dev@freedomcircle.net>
2006-10-31 03:28 ` Re: Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
@ 2006-10-31 03:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-31 06:34 ` Re: [HACKERS] Case Preservation disregarding case sensitivity? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2006-10-31 03:23 UTC (permalink / raw)
To: beau hargis <beauh@bluefrogmobile.com>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
beau hargis <beauh@bluefrogmobile.com> writes:
> Considering the differences that already exist between database systems and
> their varying compliance with SQL and the various extensions that have been
> created, I do not consider that the preservation of case for identifiers
> would violate any SQL standard.
That's not how I read the spec. It is true that we are not 100% spec
compliant, but that isn't a good argument for moving further away from
spec. Not to mention breaking backwards compatibility with our
historical behavior. The change you propose would fix your application
at the cost of breaking other people's applications. Perhaps you
should consider fixing your app instead.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [HACKERS] Case Preservation disregarding case sensitivity?
2006-10-27 23:23 Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
2006-10-28 02:38 ` Re: Case Preservation disregarding case sensitivity? Joe <dev@freedomcircle.net>
2006-10-31 03:28 ` Re: Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
2006-10-31 03:23 ` Re: Case Preservation disregarding case sensitivity? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-10-31 06:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2006-10-31 06:34 UTC (permalink / raw)
To: Chuck McDevitt <cmcdevitt@greenplum.com>; +Cc: beau hargis <beauh@bluefrogmobile.com>; pgsql-sql; pgsql-hackers@postgresql.org
"Chuck McDevitt" <cmcdevitt@greenplum.com> writes:
> At Teradata, we certainly interpreted the spec to allow case-preserving,
> but case-insensitive, identifiers.
Really?
As I see it, the controlling parts of the SQL spec are (SQL99 sec 5.2)
26) A <regular identifier> and a <delimited identifier> are
equivalent if the <identifier body> of the <regular identifier>
(with every letter that is a lower-case letter replaced by the
corresponding upper-case letter or letters) and the <delimited
identifier body> of the <delimited identifier> (with all
occurrences of <quote> replaced by <quote symbol> and all
occurrences of <doublequote symbol> replaced by <double quote>),
considered as the repetition of a <character string literal>
that specifies a <character set specification> of SQL_IDENTIFIER
and an implementation-defined collation that is sensitive to
case, compare equally according to the comparison rules in
Subclause 8.2, "<comparison predicate>".
27) Two <delimited identifier>s are equivalent if their <delimited
identifier body>s, considered as the repetition of a <character
string literal> that specifies a <character set specification>
of SQL_IDENTIFIER and an implementation-defined collation
that is sensitive to case, compare equally according to the
comparison rules in Subclause 8.2, "<comparison predicate>".
Note well the "sensitive to case" bits there. Now consider
CREATE TABLE tab (
"foobar" int,
"FooBar" timestamp,
"FOOBAR" varchar(3)
);
We can *not* reject this as containing duplicate column names, else we
have certainly violated rule 27. Now what will you do with
SELECT fooBar FROM tab;
? The spec is unquestionably on the side of "you selected the varchar
column"; historical Postgres practice is on the side of "you selected
the int column". AFAICS a case-insensitive approach would have to
fail with some "I can't identify which column you mean" error. I am
interested to see where you find support for that in the spec...
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Preservation disregarding case sensitivity?
2006-10-27 23:23 Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
2006-10-28 02:38 ` Re: Case Preservation disregarding case sensitivity? Joe <dev@freedomcircle.net>
2006-10-31 03:28 ` Re: Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
@ 2006-11-14 18:43 ` Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 2006-11-14 18:43 UTC (permalink / raw)
To: beau hargis <beauh@bluefrogmobile.com>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
beau hargis wrote:
> Having installed DB2 Enterprise today and taking it for a spin, it does indeed
> behave in a similar manner. However, after reading through both
> specifications, it seems that DB2 follows more of the spec than PostgreSQL.
> The specifications state that for purpose of comparing identifiers, both
> shall be converted to upper-case. DB2 displays all identifiers in upper-case
> whereas PostgreSQL displays all identifiers in lower-case. This alone would
> be a deviation from the specification.
True. We lowercase because historically we have, and because
all-upper-case is hard to read.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Preservation disregarding case sensitivity?
2006-10-27 23:23 Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
@ 2006-10-28 14:38 ` Richard Broersma Jr <rabroersma@yahoo.com>
3 siblings, 0 replies; 9+ messages in thread
From: Richard Broersma Jr @ 2006-10-28 14:38 UTC (permalink / raw)
To: beau hargis <beauh@bluefrogmobile.com>; pgsql-sql
> Case was preserved. Now lets add the foreign key just as we did before (note
> that the case in the table definition and the ALTER TABLE query is the same):
>
> ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
> (userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);
> ^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^
> ERROR: column "userprofiletypeid" referenced in foreign key constraint does
> not exist
When ever you defince a column with quotes, all references to it must also contain quotes. Try:
ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
("userProfileTypeId") REFERENCES user_profile_type ("userProfileTypeId");
> OK, another query (perfectly valid SQL):
>
> insert into user_profile_type
> (userProfileTypeId,userProfileType) VALUES(1,'ABNORMAL');
> ^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^
> ERROR: column "userprofiletypeid" of relation "user_profile_type" does not
> exist
Try:
insert into user_profile_type
("userProfileTypeId","userProfileType") VALUES(1,'ABNORMAL');
>
> I am hoping that there is an easy way to obtain case-preservation with
> case-insensitivity, or at the very least, case-preservation and complete
> case-sensitivity, or case-preservation and a consistant case-conversion
> strategy.
>
> Again, I am looking for a way (magic, patches, whiskey, etc) that will give me
> case-preservation with EITHER case-sensitivity OR case-insensitivity, but not
> both as I am seeing.
Perhaps in your queries or views you use the AS keywork to respecify the column name with
upper/lower cases.
i.e.
mydb=# select id as "Id" from foo;
Id
---------
goodbye
(1 row)
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Case Preservation disregarding case sensitivity?
2006-10-27 23:23 Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
@ 2006-10-28 14:59 ` Richard Broersma Jr <rabroersma@yahoo.com>
3 siblings, 0 replies; 9+ messages in thread
From: Richard Broersma Jr @ 2006-10-28 14:59 UTC (permalink / raw)
To: beau hargis <beauh@bluefrogmobile.com>; pgsql-sql
> Again, I am looking for a way (magic, patches, whiskey, etc) that will give me
> case-preservation with EITHER case-sensitivity OR case-insensitivity, but not
> both as I am seeing.
>
> Thanks in advance. I am hoping to find a solution to this so I can actually
> convert one of our databases to use Postgres. And I can say that little
> issues like this are precisely why Postgres was never used in this
> organization before, even though several of the other database developers
> like the features, stability and performance of Postgres.
Here is an explination from the postgresql manual:
http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
...
Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to
lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by
PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of
unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that
unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo"
according to the standard. If you want to write portable applications you are advised to always
quote a particular name or never quote it.)
...
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-11-14 18:43 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-27 23:23 Case Preservation disregarding case sensitivity? beau hargis <beauh@bluefrogmobile.com>
2006-10-28 01:50 ` Frank Bax <fbax@sympatico.ca>
2006-10-28 02:38 ` Joe <dev@freedomcircle.net>
2006-10-31 03:28 ` beau hargis <beauh@bluefrogmobile.com>
2006-10-31 03:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-31 06:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-14 18:43 ` Bruce Momjian <bruce@momjian.us>
2006-10-28 14:38 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-10-28 14:59 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox