Re: [GENERAL] Case sensitivity
On 12/10/2013 10:31 PM, Dev Kumkar wrote: I know about CITEXT data type, but what am looking for is if there any parameter at database level which just makes the database case insensitive. there's nothing that will do that in postgres. whats wrong with using CITEXT ? -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Case sensitivity
Thanks John. Yes CITEXT would work, the only thing its needs DDL changes across and hence was looking for any such global database parameter setting while creating database. I have been looking at other discussions and doesn't look like anything of that coming up soon that makes database case insensitive. regards... On Wed, Dec 11, 2013 at 12:10 PM, John R Pierce wrote: > On 12/10/2013 10:31 PM, Dev Kumkar wrote: > >> I know about CITEXT data type, but what am looking for is if there any >> parameter at database level which just makes the database case insensitive. >> > > there's nothing that will do that in postgres. > > whats wrong with using CITEXT ? > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- :o) dev
Re: [GENERAL] Case sensitivity
Can case-insensitive collation help here? On Wed, Dec 11, 2013 at 4:55 PM, Dev Kumkar wrote: > Thanks John. > > Yes CITEXT would work, the only thing its needs DDL changes across and > hence was looking for any such global database parameter setting while > creating database. I have been looking at other discussions and doesn't > look like anything of that coming up soon that makes database case > insensitive. > > regards... > > > On Wed, Dec 11, 2013 at 12:10 PM, John R Pierce wrote: > >> On 12/10/2013 10:31 PM, Dev Kumkar wrote: >> >>> I know about CITEXT data type, but what am looking for is if there any >>> parameter at database level which just makes the database case insensitive. >>> >> >> there's nothing that will do that in postgres. >> >> whats wrong with using CITEXT ? >> >>
Re: [GENERAL] Case sensitivity
On Wed, Dec 11, 2013 at 04:55:07PM +0530, Dev Kumkar wrote: > creating database. I have been looking at other discussions and doesn't > look like anything of that coming up soon that makes database case > insensitive. You could build lower() indexes on any column you want to search CI and lower() all the input text during searches, in order to avoid any work on the schema. Bit of a kludge, though. Best, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Case sensitivity
Actually for searches lower will work. But the other important aspect is 'inserts' which would result 2 rows if the values are 'A' and 'a'. Intent here to have it case insensitive. If CITEXT it will update the same row and works. CITEXT is an alternative but was wondering if there is any other alternate solution/setting while creating database. Also does CITEXT fetch via JDBC works the same way as fetch/set string values? Any quick comments here. http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html Regards... On Wed, Dec 11, 2013 at 8:58 PM, Andrew Sullivan wrote: > On Wed, Dec 11, 2013 at 04:55:07PM +0530, Dev Kumkar wrote: > You could build lower() indexes on any column you want to search CI > and lower() all the input text during searches, in order to avoid any > work on the schema. Bit of a kludge, though. > > Best, > > A >
Re: [GENERAL] Case sensitivity
On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar wrote: > Actually for searches lower will work. > But the other important aspect is 'inserts' which would result 2 rows if > the values are 'A' and 'a'. Intent here to have it case insensitive. > > If CITEXT it will update the same row and works. > CITEXT is an alternative but was wondering if there is any other alternate > solution/setting while creating database. > > Also does CITEXT fetch via JDBC works the same way as fetch/set string > values? Any quick comments here. > > http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html > > Regards... > Also if the key columns are CITEXT is there any performance issues on indexes?
Re: [GENERAL] Case sensitivity
+ hackers On Thu, Dec 12, 2013 at 12:34 PM, Dev Kumkar wrote: > On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar wrote: > >> Actually for searches lower will work. >> But the other important aspect is 'inserts' which would result 2 rows if >> the values are 'A' and 'a'. Intent here to have it case insensitive. >> >> If CITEXT it will update the same row and works. >> CITEXT is an alternative but was wondering if there is any other >> alternate solution/setting while creating database. >> >> Also does CITEXT fetch via JDBC works the same way as fetch/set string >> values? Any quick comments here. >> >> http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html >> >> Regards... >> > > Also if the key columns are CITEXT is there any performance issues on > indexes? >
Re: [GENERAL] Case sensitivity
On Thu, Dec 12, 2013 at 12:47 PM, Dev Kumkar wrote: > + hackers > > > > On Thu, Dec 12, 2013 at 12:34 PM, Dev Kumkar wrote: > >> On Wed, Dec 11, 2013 at 9:47 PM, Dev Kumkar wrote: >> >>> Actually for searches lower will work. >>> But the other important aspect is 'inserts' which would result 2 rows if >>> the values are 'A' and 'a'. Intent here to have it case insensitive. >>> >>> If CITEXT it will update the same row and works. >>> CITEXT is an alternative but was wondering if there is any other >>> alternate solution/setting while creating database. >>> >>> Also does CITEXT fetch via JDBC works the same way as fetch/set string >>> values? Any quick comments here. >>> >>> http://techie-experience.blogspot.in/2013/04/hibernate-supporting-case-insensitive.html >>> >>> Regards... >>> >> >> Also if the key columns are CITEXT is there any performance issues on >> indexes? >> > I am ok with CITEXT but for changing the database design for the primary/foreign key columns to be CITEXT need some suggestions/comments regarding performance for inserts/reads. Awaiting...
Re: [GENERAL] Case sensitivity
Frank Millman wrote: Hi all Is there an LC_COLLATE setting, or any other method, which allows all data in a database to be treated in a case-insensitive manner? I have two scenarios in mind. There are workarounds for both of them, but it would be nice if they were not necessary. 1. In a UNIQUE column, I would like a value of 'a' to be rejected if there is already a value of 'A'. Workaround - create a unique index on LOWER(col_name). 2. I would like WHERE col_name = 'x' and WHERE col_name LIKE 'x%' to find 'X' and 'X1'. Workaround - WHERE LOWER(col_name) = 'x' and WHERE col_name ILIKE 'x%'. You could define your own type if you want to go to that level of effort. Or, you could just decide only to allow upper-case values (or lower-case). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Case sensitivity
Frank Millman wrote: > Hi all > > Is there an LC_COLLATE setting, or any other method, which allows all > data in a database to be treated in a case-insensitive manner? I was hoping to stimulate some discussion on this topic, but it seems I will have to kick-start it myself and see if anyone responds. My area of interest is general accounting/business systems, using a typical Western character set. I would imagine that this is a common scenario, but it is not universal, so read my comments in this context. In the good old days, data entry was always done in upper case, by dedicated data entry personnel. These days, it is typically done by a wide variety of individuals, who carry out a wide range of tasks, most of which require lower case (word processing, email) with the occasional use of the shift key to enter a capital letter. In this context, here are two undesirable effects. 1. The user tries to call up account 'A001', but they enter 'a001'. First problem, the system does not find the account. Second problem, the system allows them to create a new account with the code 'a001'. Now you have 'A001' and 'a001'. This is a recipe for chaos. 2. The user tries to call up a product item using a search string on the description. Assume they enter 'Wheel nut'. Assume further that the person who created the product item used the description 'Wheel Nut'. Try explaining to the user why the system cannot find the item they are looking for. I mentioned in my original post that there are workarounds for these problems. However, it seems to me that in a typical system you would want to apply the workaround on every table, and therefore there is a case for saying that the database should handle it. I have some experience of two other database systems, and it is of interest to see how they handle it. 1. D3 (the latest implementation of the old Pick Database System). In the early days it was case sensitive. When they brought out a new version in the early 90's they changed it to case insensitive. As you would expect, an upgrade required a full backup and restore. I was involved in many of these, some of them quite large. On two occasions I found that accounts were out of balance after the restore, and on investigation found that situations similar to 'A001' 'a001' had crept into the old database, and on restore the second insert was rejected as the first one already existed. When this was explained to the user, the reaction was always concern that this 'error' had been allowed to happen, and relief that the new version ensured that it could never happen again. 2. MSSQL Server 2000. Each time you create a new database you have to specify which 'collation' to use. There is a wide range available, both case sensitive and case insensitive. The default (on my system at least) is case insensitive, and I believe that in practice this is what most people want. There may well be counter-arguments to this being handled by the database, and I would be interested to hear them. However, from my point of view, if this capability is not currently available in PostgreSQL, I would like to propose that it is considered for some future release. Looking forward to any comments. Frank Millman ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Case sensitivity
On Tue, Aug 09, 2005 at 09:35:25AM +0200, Frank Millman wrote:
> Frank Millman wrote:
>
> > Hi all
> >
> > Is there an LC_COLLATE setting, or any other method, which allows all
> > data in a database to be treated in a case-insensitive manner?
>
> I was hoping to stimulate some discussion on this topic, but it seems I will
> have to kick-start it myself and see if anyone responds.
I know there have been complaints from people that their database is
sorting case-insensetively when they wish it wouldn't. This generally
happens when the LC_COLLATE is set to en_US or some such. However, I
think that even the en_US locale just fiddles the sort order, but
doesn't make upper and lowercase equal.
> I mentioned in my original post that there are workarounds for these
> problems. However, it seems to me that in a typical system you would want to
> apply the workaround on every table, and therefore there is a case for
> saying that the database should handle it.
These "workarounds" are the recommended way of dealing with this issue.
Another option would be to create a new datatype 'itext' which works
like text except it compares case insensetively. PostgreSQL is flexible
like that. Here's something to get you started, see below for example.
http://svana.org/kleptog/pgsql/type_itext.sql
At the moment it uses SQL functions for the comparisons, for production
you'd probably want to have them in C for performance. Also, it's not
pg_dump safe (no operator class support).
BTW, I can't beleive I'm the first to do this, but hey. It's also my
first type with index support so it may be buggy. But it does work for
basic tests...
Have a nice day,
--- snip ---
test=# create table itest ( pkey serial primary key, val itext );
NOTICE: CREATE TABLE will create implicit sequence "itest_pkey_seq"
for "serial" column "itest.pkey"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"itest_pkey" for table "itest"
CREATE TABLE
test=# insert into itest (val) values ('a');
INSERT 72279 1
test=# insert into itest (val) values ('A');
INSERT 72280 1
test=# insert into itest (val) values ('b');
INSERT 72281 1
test=# select * from itest where val = 'a';
pkey | val
--+-
1 | a
2 | A
(2 rows)
test=# create unique index itest_val on itest(val);
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
test=# delete from itest where val = 'a';
DELETE 2
test=# create unique index itest_val on itest(val);
CREATE INDEX
test=# insert into itest (val) values ('a');
INSERT 72284 1
test=# insert into itest (val) values ('A');
ERROR: duplicate key violates unique constraint "itest_val"
--
Martijn van Oosterhout http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
pgpKSBxrlhuIF.pgp
Description: PGP signature
Re: [GENERAL] Case sensitivity
On Tue, Aug 09, 2005 at 11:57:48AM +0200, Martijn van Oosterhout wrote: > Another option would be to create a new datatype 'itext' which works > like text except it compares case insensetively. PostgreSQL is flexible > like that. Here's something to get you started, see below for example. > > http://svana.org/kleptog/pgsql/type_itext.sql > > At the moment it uses SQL functions for the comparisons, for production > you'd probably want to have them in C for performance. Also, it's not > pg_dump safe (no operator class support). Oops, turns out there *is* a CREATE OPERATOR CLASS but my version of psql doesn't have it in command completion. And when you use that it *is* saved by pg_dump. Problem solved. I've tested various things, DISTINCT works, ORDER BY works, GROUP BY works. Neat huh? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpVyptdulwvP.pgp Description: PGP signature
Re: [GENERAL] Case sensitivity
Frank Millman wrote: Frank Millman wrote: Hi all Is there an LC_COLLATE setting, or any other method, which allows all data in a database to be treated in a case-insensitive manner? I was hoping to stimulate some discussion on this topic, but it seems I will have to kick-start it myself and see if anyone responds. My area of interest is general accounting/business systems, using a typical Western character set. I would imagine that this is a common scenario, but it is not universal, so read my comments in this context. In the good old days, data entry was always done in upper case, by dedicated data entry personnel. These days, it is typically done by a wide variety of individuals, who carry out a wide range of tasks, most of which require lower case (word processing, email) with the occasional use of the shift key to enter a capital letter. In this context, here are two undesirable effects. Martijn has pointed to a case-insensitive type, but I'll add a couple of points. 1. The user tries to call up account 'A001', but they enter 'a001'. First problem, the system does not find the account. Second problem, the system allows them to create a new account with the code 'a001'. Now you have 'A001' and 'a001'. This is a recipe for chaos. The basic problem here is that the value isn't text. This is partly the fault of development-systems not having a way to deal with sophisticated types in databases. What should happen is that you define some suitable type "AccountCode" which is defined as allowing character data in the form of (e.g.) "[A-Z][0-9][0-9][0-9]". That type can cast to/from text but doesn't need access to the full range of text-handling functions (e.g. concatenating two account-codes is probably meaningless). Of course, you want to define this in one place and have both the database constraints and user-interface understand what you want. Ironically, MS-Access does this quite well with its tight coupling of user-interface and data storage. 2. The user tries to call up a product item using a search string on the description. Assume they enter 'Wheel nut'. Assume further that the person who created the product item used the description 'Wheel Nut'. Try explaining to the user why the system cannot find the item they are looking for. Here, I'm not convinced a case-insensitive type is any more useful than just using ILIKE. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Case sensitivity
Martijn van Oosterhout writes:
> Another option would be to create a new datatype 'itext' which works
> like text except it compares case insensetively. PostgreSQL is flexible
> like that. Here's something to get you started, see below for example.
> http://svana.org/kleptog/pgsql/type_itext.sql
> At the moment it uses SQL functions for the comparisons, for production
> you'd probably want to have them in C for performance.
I believe there is a C-coded type like this on gborg ("citext" is the
name I think).
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Case sensitivity
On Tue, Aug 09, 2005 at 11:02:47AM -0400, Tom Lane wrote:
> Martijn van Oosterhout writes:
> > Another option would be to create a new datatype 'itext' which works
> > like text except it compares case insensetively. PostgreSQL is flexible
> > like that. Here's something to get you started, see below for example.
>
> > http://svana.org/kleptog/pgsql/type_itext.sql
>
> > At the moment it uses SQL functions for the comparisons, for production
> > you'd probably want to have them in C for performance.
>
> I believe there is a C-coded type like this on gborg ("citext" is the
> name I think).
And so it is, full points to Tom. Here's the link:
http://gborg.postgresql.org/project/citext/projdisplay.php
I couldn't work out any obvious way to make google spit out this link
without the magic word "citext", so hopefully this reference will raise
the score enough that a plain google search for "case insensitive
postgresql" will find it.
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
pgpvElz37qptR.pgp
Description: PGP signature
Re: [GENERAL] Case sensitivity
Frank Millman wrote: > Hi all > > Is there an LC_COLLATE setting, or any other method, which allows all > data in a database to be treated in a case-insensitive manner? > Thanks for all the replies, guys, I really appreciate it. Here is what I have decided to do. If anyone sees any problems with my approach, please let me know. Interesting though the citypes are, I will not use them. If I get anywhere with the app I am developing (it is making progress, but rather slowly) I will release it as an open source project. I do not want to make it a requirement that everyone must install a new datatype before they can use it. To handle searching for a row based on a string, I will use "LOWER(colname) = 'x'" and "LOWER(colname) LIKE 'x%'". AFAICT, the second one is equivalent to "colname ILIKE 'x%'", provided I force 'x' to lowercase first. I prefer it as I need to support SQL Server as well, and this should work on both platforms. The problem of inserting 'a001' when 'A001' exists is still potentially there, but it should not occur within my app, due to the way I handle table maintenance. I do not distinguish between INSERT and UPDATE from the user's point of view, but allow them to enter a primary key, do a SELECT, and then assume UPDATE mode if the row exists, and INSERT mode if it does not. As I will use SELECT WHERE LOWER(colname) = 'a001', it will find 'A001' and go into UPDATE mode, so there should be no danger of duplication. It does not feel entirely robust, so I will have to go through my app carefully to see if I can find any loopholes in this theory. Two questions. 1. Will SELECT WHERE LOWER(colname) = 'a001' use the index, or must I create a separate index on LOWER(colname)? 2. I was expecting to have a problem with LOWER(colname) if the column was of a numeric or date type, but it accepts it without complaining. Is it safe for me to rely on this behaviour in the future? Thanks again to everyone. Frank ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Case sensitivity
Frank Millman wrote: > 1. Will SELECT WHERE LOWER(colname) = 'a001' use the index, or must I create a separate index on LOWER(colname)? Sorry. RTFM. The docs clearly state that this is necessary. Frank ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Case sensitivity
To handle searching for a row based on a string, I will use
"LOWER(colname)
= 'x'" and "LOWER(colname) LIKE 'x%'". AFAICT, the second one is
equivalent
to "colname ILIKE 'x%'", provided I force 'x' to lowercase first.
I would strongly suggest you do this instead:
LOWER(colname) = LOWER('x')
This is far more bullet-proof than lower-casing in the client, in case
the client and the server differ (mismatched locales, etc.). If you
don't want to use ILIKE for portability reasons (perfectly reasonable),
I suggest the analogous:
LOWER(colname) LIKE LOWER('x%')
- John D. Burger
MITRE
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Case sensitivity
# [EMAIL PROTECTED] / 2005-08-10 13:18:32 +0200: > 1. Will SELECT WHERE LOWER(colname) = 'a001' use the index, or must I create > a separate index on LOWER(colname)? the latter -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Case Sensitivity
Hello you should to use a citext datatype http://www.postgresql.org/docs/9.0/interactive/citext.html regards Pavel Stehule 2011/1/13 Phillip Smith : > Hi, > I'm moving over from MS SQL. I've been googling this for ages now and > suprisingly cannot find a clear answer. > > I want my data in tables to be case insensitive. > > This is so i can: > 1. Put keys on natural key fields, for instance a product part number. I > product number 'ABC123' inserted i need to disallow 'abc123' to be inserted > as a second row. Please don't tell me i have to add another column holding a > lowered version of the product number. My database is littered with this > need and i would end up bloating my table schema. > > 2. I need to query case insensitively. SELECT * FROM product WHERE > product_number = 'ABC123' should return the same row as SELECT * FROM > product WHERE product_number = 'abc123' > > Is there a database wide collation setting i can make, or a case > insensitive character type. There are lots of online posts regarding using > LOWER function for querying. This is a workaround for point (2) but does not > remedy point (1) above. > > Many thanks > > Phillip > > Phillip Smith > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Case Sensitivity
> you should to use a citext datatype > > http://www.postgresql.org/docs/9.0/interactive/citext.html Or: - use a functional index with lower() to ensure uniqueness - use a BEFORE trigger to lower() the inserted data Karsten -- GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit gratis Handy-Flat! http://portal.gmx.net/de/go/dsl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Case Sensitivity
Thanks Pavel, Is this the only way?. I would prefer to use a collation setting if this is possible. Do you know whether there would be a performance hit using non standard character strings? Phillip
Re: [GENERAL] Case Sensitivity
Hi Karsten, modifying user inputted data is not an option for me. I need to maintain the original data. I had read about indexing on a lower function. This all seems a bit of a bodgy workaround to me. A clean Case Insensitive collation setting is cleaner. Is this supported in postgreSQL 9? Regards Phillip Phillip
Re: [GENERAL] Case Sensitivity
2011/1/13 Phillip Smith : > Thanks Pavel, > > Is this the only way?. I would prefer to use a collation setting if this is > possible. Do you know whether there would be a performance hit using non > standard character strings? It is preferred way. PostgreSQL doesn't support a collations, and what I know collation in 9.1 will not allow a comparation between case sensitive and case insensitive text, because collation in PostgreSQL is based on unix locale, and there are not case insensitive locale. I don't know about performance problems. It does same work like case insensitive collation. Regards Pavel Stehule > > Phillip > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Case Sensitivity
On Thu, Jan 13, 2011 at 03:29:03PM -, Phillip Smith wrote: > modifying user inputted data is not an option for me. I need to > maintain the original data. I had read about indexing on a lower > function. This all seems a bit of a bodgy workaround to me. A clean > Case Insensitive collation setting is cleaner. Is this supported in > postgreSQL 9? There isn't such a collation setting as far as I know, and it's actually not as clean as you think. It turns out to work reasonably well in ASCII-land, but not very well in other circumstances. In general, the case-insensitive but case-preserving technique that English-speaking computer users have come to find normal is a bad fit for a wide variety of languages. (Even "Latin" characterset-using languages have trouble, because of historic ways of handling accents. Is the capital version of é E or É? Well, both, it turns out, depending on whom you believe. I could bore you about the effects of this in the DNS all day long, but I'll resist the temptation.) Putting an index on lower(column) and then doing all your comparisons with lower(datum) works fairly well, and I don't see how it's any more bodgy than a database-wide case insensitive collation. For instance, I can assure you that customers named Leblanc and LeBlanc care about whether those two compare equally. In your customer name field, if you have a database-wide collation setting, you can't make the distinction. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case sensitivity?
On Mon, Jun 6, 2011 at 2:24 PM, Heine Ferreira wrote: > Hi > > I posted a similar question before. I was told that Postgres is always case > sensitive. > I was actually referring to text data not identifiers. > I mean char and varchar fields. > I basicically want to avoid using upper in comparisons. > With some databases you can use a utf8 characterset and a unicode or win1252 > coallation. > Has anyone tried this? > Do you know if this will work? Maybe the citext type would do what you want? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case sensitivity?
On Mon, Jun 06, 2011 at 10:24:45PM +0200, Heine Ferreira wrote: > I basicically want to avoid using upper in comparisons. > Has anyone tried this? > Do you know if this will work? There's a contrib module that will allow you to do this. See the citext datatype. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Case sensitivity
> create table test (val varchar(10));
> insert into test values ('A');
> insert into test values ('a');
> select * from test where val < 'a';
>
> Results on 8.0.1 linux server (UNICODE)
> val
> -
> (0 rows)
>
> Results on 8.0.3 windows server (UNICODE) val
> -
> A
> (1 row)
>
> I am guessing that the 8.0.3 on windows is the correct
> version - but what changes this and how can I set it?
UNICODE is not currently supported on win32. We hope to have this done
in 8.1, but in 8.0 bad things like this will happen. See
http://www.postgresql.org/docs/faqs.FAQ_windows.html#2.6.
//Magnus
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Case sensitivity
Howard Cole <[EMAIL PROTECTED]> writes: > Has case sensitivity changed between 8.0.1 and 8.0.3 Nope. > Results on 8.0.3 windows server (UNICODE) Unicode doesn't work at all well on Windows. Consider using a different encoding. Also, you'd better check that you have matching locale and encoding settings on both those installations ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Case sensitivity
I looked into the problem further - and could not repeat the problem on my windows XP machine. The problem was isolated to a server which uses the 64bit edition of W2K3 server. Otherwise the W2K3 64bit edition seems to run PG ok. Are there any plans for fixing the UNICODE encoding on Windows? Regards, Howard Cole www.selestial.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Case sensitivity
> I looked into the problem further - and could not repeat the > problem on my windows XP machine. The problem was isolated to > a server which uses the 64bit edition of W2K3 server. > Otherwise the W2K3 64bit edition seems to run PG ok. > > Are there any plans for fixing the UNICODE encoding on Windows? Yes. There is a patch floating around to use ICU, which will fix this (amongst the other things it does). The hope is that it'll be completed in time for 8.1. //Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Case sensitivity
Tom, Magnus. Thanks for the great support. Howard Cole www.selestial.com Magnus Hagander wrote: Are there any plans for fixing the UNICODE encoding on Windows? Yes. There is a patch floating around to use ICU, which will fix this (amongst the other things it does). The hope is that it'll be completed in time for 8.1. //Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Case sensitivity
On Sunday 11 January 2004 09:29, Dario Ottaviano wrote: > I use postgres on a window server (IIS 5.1) > Is there anybody that knows if is possible to make postgres no case > sensitive in the manipulating data into tables/views? There's no general "case_sensitive = yes/no" flag. There are case-insensitive operators you can use though. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Case sensitivity
On Mon, 12 Jan 2004, Richard Huxton wrote: > On Sunday 11 January 2004 09:29, Dario Ottaviano wrote: > > I use postgres on a window server (IIS 5.1) > > Is there anybody that knows if is possible to make postgres no case > > sensitive in the manipulating data into tables/views? > > There's no general "case_sensitive = yes/no" flag. There are case-insensitive > operators you can use though. Is there a case insensitive locale? Or is that even possible? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Case sensitivity
Jason Tesser wrote: I am converting data from Access into Postgres and ran into an issue with case sensitivity. Can I write queries in Access that will be case insensitive without rewriting the queries. So I would like to know if this be handled in Postgres or even if someone knows in Access. Thank you. I believe no. You can index on LOWER(f) or UPPER(f) but that still requires a query rewriting to WHERE LOWER(f) = You can also write INSERT triggers to convert values to upper case, but that still requires query rewriting if you're comparing with mixed-case values. This has been discussed many times. See the archive for the various threads on this. To be safe, if you want your application to be portable, you should always assume case-sensitivity. AFAIK, only Access, SQL Server, and MySQL that are case-insensitive by default while others including Postgres are case-sensitive. Any other popular DB that are case-sensitive out there? -- dave ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Case sensitivity?
On Tue, 27 Dec 2005, dfx wrote:
Dear Sirs,
I have a little problem:
- Contest: Server side: Linux Fedora core 4 with PostgreSQL v. 8.0
Client side: both Linux Fedora core 4 with pgAdmin III v.
1.4 and/or Windows 2000 server vith pgAdmin III v. 1.4
- I Made a table:
CREATE TABLE "Accoglienza"
(
"IdAccoglienza" int4 NOT NULL DEFAULT
nextval('public."Accoglienza_IdAccoglienza_seq"'::text),
"IdCongresso" int4 NOT NULL DEFAULT 0,
CONSTRAINT "Accoglienza_Pk" PRIMARY KEY ("IdAccoglienza")
)
WITHOUT OIDS;
if I try "INSERT INTO Accoglienza (IdCongresso) VALUES (23)"
I get an error "ERROR: la relazione "accoglienza" non esiste" (The relation
"accoglienza does not exist")
please note the change of the case of "A" to "a";
if I rename the table to "accoglienza" the error disappear, but arise
another error on "IdAccoglienza" that change in "idaccoglienza". (i.e. all
converted to lower case)
The same error arise with window client and linux client.
How I can set case-insensitive the system, or avoid the conversion to lower
case?
Create the table without closing the relation's name with doble quotes:
CREATE TABLE Accoglienza
(
IdAccoglienza int4 NOT NULL DEFAULT
nextval('public.Accoglienza_IdAccoglienza_seq'::text),
IdCongresso int4 NOT NULL DEFAULT 0,
CONSTRAINT Accoglienza_Pk PRIMARY KEY (IdAccoglienza)
)
WITHOUT OIDS;
--
12:00:02 up 4 days, 23:12, 1 user, load average: 0.35, 0.54, 0.70
-
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática| '@' || 'unl.edu.ar';
Universidad Nacional| DBA, Programador,
del Litoral | Administrador
-
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Case sensitivity?
On 12/27/05, dfx <[EMAIL PROTECTED]> wrote:
> Dear Sirs,
>
> I have a little problem:
>
> - Contest: Server side: Linux Fedora core 4 with PostgreSQL v. 8.0
> Client side: both Linux Fedora core 4 with pgAdmin III v.
> 1.4 and/or Windows 2000 server vith pgAdmin III v. 1.4
>
> - I Made a table:
>
> CREATE TABLE "Accoglienza"
> (
> "IdAccoglienza" int4 NOT NULL DEFAULT
> nextval('public."Accoglienza_IdAccoglienza_seq"'::text),
> "IdCongresso" int4 NOT NULL DEFAULT 0,
> CONSTRAINT "Accoglienza_Pk" PRIMARY KEY ("IdAccoglienza")
> )
> WITHOUT OIDS;
>
> if I try "INSERT INTO Accoglienza (IdCongresso) VALUES (23)"
>
> I get an error "ERROR: la relazione "accoglienza" non esiste" (The relation
> "accoglienza does not exist")
>
> please note the change of the case of "A" to "a";
>
> if I rename the table to "accoglienza" the error disappear, but arise
> another error on "IdAccoglienza" that change in "idaccoglienza". (i.e. all
> converted to lower case)
>
> The same error arise with window client and linux client.
>
> How I can set case-insensitive the system, or avoid the conversion to lower
> case?
>
the server always will transfor the names of the object to lower case
unless you surround them with quotes ("")
so if you say
CREATE TABLE TablE --> it will create a table called table
and if you say
CREATE TABLE "TablE" --> it will create a table called "TablE"
and in this case, if you try to select from table it will give an
error... it will give you an error even if you type TablE you have to
type "TablE" or always live with lower case object names...
> It depends on setting of pgAdmin or on setting of the server?
>
> Thank you.
>
>
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Case sensitivity?
Due to case weirdness in Postgres, particularly when accessing it from PHP, I completely abandoned mixed case table and column names. I don't know if things have changed in the past several years, but when I first made the leap, mixed case was more trouble than it was worth. Even after I got over the initial hump and figured out how to make it work, there was enough weird behavior (i.e. being able to access a non-existent array element) in PHP that it led to a lot of bugs when using mixed-case in Postgres. That's my two cents. Your mileage may vary... dfx wrote: Dear Sirs, please note the change of the case of "A" to "a"; if I rename the table to "accoglienza" the error disappear, but arise another error on "IdAccoglienza" that change in "idaccoglienza". (i.e. all converted to lower case) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Case sensitivity?
On Tue, Dec 27, 2005 at 09:14:20AM -0600, John McCawley wrote: > Due to case weirdness in Postgres, particularly when accessing it from > PHP, I completely abandoned mixed case table and column names. I don't > know if things have changed in the past several years, but when I first > made the leap, mixed case was more trouble than it was worth. Well, the rule is very simple, either always quote your identifiers, or never quote them. If you always quote them, then you always need to specify them in the same case. If you never quote them, then you always get case-insensetivity. > Even after I got over the initial hump and figured out how to make it > work, there was enough weird behavior (i.e. being able to access a > non-existent array element) in PHP that it led to a lot of bugs when > using mixed-case in Postgres. That's my two cents. Your mileage may > vary... Hmm, you mean how column titles come back as lowercase? By strict SQL standard they should come back as all upper-case, but you probably don't want that either... SQL has always been case-folding, never case-insensetive. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpNSImQZT6Zt.pgp Description: PGP signature
Re: [GENERAL] Case sensitivity?
Well, the rule is very simple, either always quote your identifiers, or never quote them. If you always quote them, then you always need to specify them in the same case. If you never quote them, then you always get case-insensetivity. My memory is a little hazy, as it's been a few years, but I believe my ultimate problem was that my code ended up being quite confusing. I was able to use my mixed case, as you said, when I used quotes in the SQL query (which had to be escaped in PHP), however, the column names came in all lowercase, so I ended up having to switch between the two, leading to me making mistakes. See this example (note my example is using my DB wrapper class): RunSQLReturnRS($sSQL); $foobar = $result->fields["foobar"]; ?> I generally give my local variables the same name as their database counterparts, as shown by foobar in this example. The problem is that foobar ends up as lowercase when it gets back to me. If I get into the habit of typing "FooBar" mixed case in my SQL queries, it is only a matter of time before I do the following: $Foobar = $result->fields["FooBar"]; PHP won't raise any error here, but rather just create a new "FooBar" variable in the fields map, which is a pretty insidious behavior. Rather than open myself up to inevitable bugs, I just abandoned mixed case. I still end up with a similar problem when I misspell column names, but there isn't a whole lot I can do to avoid that other than type more carefully. I know that this is more of a PHP issue than a Postgres issue, but these days a lot of people are using higher level languages for web development etc., and can run into similar issues, so I figured I'd mention this as an argument for avoiding mixed case in the database. John ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Case sensitivity?
Hi, On Tue, 2005-12-27 at 15:54 -0800, dfx wrote: > How I can set case-insensitive the system, or avoid the conversion to > lower case? You need to double-quote the object names that you don't want to be converted to lower case, like CREATE TABLE "DevrimGunduz". Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Case sensitivity
Bruce Richardson wrote: >Discovering that text fields in Postgres are case sensitive - so "Text" ><> "text" and both can go into the same UNIQUE column - threw me a bit, >as this is different from other databases I've worked with. Well, they are different, aren't they? >Is there a toggle to turn off case sensitivity, either for a whole >database or for a column? Or do I just have to get used to ~*? You can create a unique index on lower(column); ~* is very nice, any way. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If we confess our sins, he is faithful and just to forgive us our sins, and to cleanse us from all unrighteousness." I John 1:9
Re: [GENERAL] Case sensitivity
Very good book. I have the .pdf file of it from before they took it off the site. John Clark Naldoza y Lopez wrote: > > Hello Scott, > > PostgreSQL also supports regular expressions, _VERY_ useful..;-) > > ~* regular expression, case-insensitive..;-) > > Try to get a copy of Bruce's book > > Introduction and Concepts > by Bruce Momjian > > A sample from the book: > SELECT * FROM friend WHERE firstname ~* '[bc]' > > read it online, or just read the docs online...;-) > > http://techdocs.postgresql.org > > Cheers, > > John Clark > > p.s. don't change PostgreSQL, change your SQL statements...;-) > > Scott Gilbert wrote: > > > > We intend to move from M$ Access to PostgreSQL, however, much of our legacy > > code operates under the assumption that string or text comparisons are case > > insensitive whereas postgres is case sensitive. This issue extends to SQL > > Select statements as well as straight comparison (i.e. in addition to the <, > > > and = operators, the WHERE and IN clauses in SQL statements are also > > issues) so we can't simply modify the function attached to the comparison > > operators. Is there some means of modifying the behaviour of PostgreSQL so > > that it is case insensitive? > > -- > /) John Clark Naldoza y Lopez (\ > / )Software Design Engineer III ( \ > _( (__ Web-Application Development_) )_ > (((\ \> /_>Cable Modem Network Management System <_\( \_/ / NEC Telecom Software Phils., Inc. \ \_/ ) > \ / \ / >\_/ phone: (+63 32) 233-9142 loc. 3113\_/ >/ / cellphone: (+63 919) 399-4742 \ \ > / / email: [EMAIL PROTECTED]\ \ > > "Intelligence is the ability to avoid doing work, yet getting the work > done" > --Linus Torvalds > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Case sensitivity
Hello Scott, PostgreSQL also supports regular expressions, _VERY_ useful..;-) ~* regular expression, case-insensitive..;-) Try to get a copy of Bruce's book Introduction and Concepts by Bruce Momjian A sample from the book: SELECT * FROM friend WHERE firstname ~* '[bc]' read it online, or just read the docs online...;-) http://techdocs.postgresql.org Cheers, John Clark p.s. don't change PostgreSQL, change your SQL statements...;-) Scott Gilbert wrote: > > We intend to move from M$ Access to PostgreSQL, however, much of our legacy > code operates under the assumption that string or text comparisons are case > insensitive whereas postgres is case sensitive. This issue extends to SQL > Select statements as well as straight comparison (i.e. in addition to the <, > > and = operators, the WHERE and IN clauses in SQL statements are also > issues) so we can't simply modify the function attached to the comparison > operators. Is there some means of modifying the behaviour of PostgreSQL so > that it is case insensitive? -- /) John Clark Naldoza y Lopez (\ / )Software Design Engineer III ( \ _( (__ Web-Application Development_) )_ (((\ \> /_>Cable Modem Network Management System <_\
Re: [GENERAL] Case sensitivity issue
select * from Apples where lower(color) like '%red%'; ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol Känns det oklart? Fråga på! On 9 Sep 2001, Michael Gay wrote: > If I am doing a command such as > > select * from Apples where color like '%red%'; > > how do I make it case insensitive? > > Thanks. > > mike > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Case sensitivity issue
Szabo Zoltan <[EMAIL PROTECTED]> writes:
> Use: ILIKE
>
> > If I am doing a command such as
> > select * from Apples where color like '%red%';
> > how do I make it case insensitive?
I was trying to solve a similar problem where I wanted an exact match to work
in PostgreSQL and MySQL and ended up with:
lower(Field) = lower('string')
Is there another way that works on both?
--
matthew rice <[EMAIL PROTECTED]> starnix inc.
tollfree: 1-87-pro-linuxthornhill, ontario, canada
http://www.starnix.com professional linux services & products
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Case sensitivity issue
SELECT * FROM apples WHERE lower(color) LIKE '%red%'; Make sure that what ever 'red' might be is always lower case (you can pass it through lower() too, of course).. -Mitch - Original Message - From: "Michael Gay" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 10, 2001 12:02 AM Subject: [GENERAL] Case sensitivity issue > If I am doing a command such as > > select * from Apples where color like '%red%'; > > how do I make it case insensitive? > > Thanks. > > mike > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Case sensitivity issue
use "ILIKE" instead of "LIKE" or use a regular expression match: ~* 'red' - Original Message - From: "Michael Gay" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, September 09, 2001 11:02 PM Subject: [GENERAL] Case sensitivity issue > If I am doing a command such as > > select * from Apples where color like '%red%'; > > how do I make it case insensitive? > > Thanks. > > mike > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Case sensitivity problems with user name
Will Rutherdale (rutherw) wrote: When I try going into psql as user postgres to change the user name, I get this problem: postgres=# alter user mickey rename to Mickey; ERROR: role “mickey” already exists you might try... alter user "mickey" rename to "Mickey"; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Case sensitivity problems with user name
Yes, that worked, thanks. After also doing this: alter database "mickey" rename to "Mickey"; it solved the problem. Pardon my newness to Postgres. -Will -Original Message- From: John R Pierce [mailto:pie...@hogranch.com] Sent: 24 March 2009 16:21 To: Will Rutherdale (rutherw) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Case sensitivity problems with user name Will Rutherdale (rutherw) wrote: > When I try going into psql as user postgres to change the user name, I > get this problem: > > postgres=# alter user mickey rename to Mickey; > > ERROR: role "mickey" already exists > you might try... alter user "mickey" rename to "Mickey"; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Case sensitivity problems with user name
On 24/03/2009 20:38, Will Rutherdale (rutherw) wrote: > Pardon my newness to Postgres. We've all been there at some point! - except of course for Tom Lane. :-) Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
