On Wednesday 06 August 2008 07:59, Rafael Domiciano wrote:
> I have read the article... tnks, very helpful.
>
> But, can I create a index using function like "substring"? I would like to
> create something like this:
Actually, Richard Broersma is the one who commented on that approach. I have
nev
I have read the article... tnks, very helpful.
But, can I create a index using function like "substring"? I would like to
create something like this:
CREATE INDEX indtest_01 ON table_01
((SUBSTRING(month_year, 3, 4) || SUBSTRING(month_year, 1, 2))
2008/8/4 Terry Lee Tucker <[EMAIL PROTECTED]>
>
On Monday 04 August 2008 11:09, Frank Bax wrote:
> Terry Lee Tucker wrote:
> > On Monday 04 August 2008 10:05, Richard Broersma wrote:
> >> On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <[EMAIL PROTECTED]> wrote:
> >>> In some db's if you
> >>> use a lower() or upr() it will always do a table scan ins
Terry Lee Tucker wrote:
On Monday 04 August 2008 10:05, Richard Broersma wrote:
On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <[EMAIL PROTECTED]> wrote:
In some db's if you
use a lower() or upr() it will always do a table scan instead of using a
index
True, this would also happen in PostgreSQL.
On Monday 04 August 2008 10:05, Richard Broersma wrote:
> On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <[EMAIL PROTECTED]> wrote:
> >In some db's if you
> > use a lower() or upr() it will always do a table scan instead of using a
> > index
>
> True, this would also happen in PostgreSQL. However, you
On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <[EMAIL PROTECTED]> wrote:
>In some db's if you
> use a lower() or upr() it will always do a table scan instead of using a
> index
True, this would also happen in PostgreSQL. However, you can overcome
this by creating a "functional" index:
http://www.po
My environment setup as:
show lc_ctype;
lc_ctype
-
fr_CA.UTF-8
(1 row)
fis=> SELECT 'Ä' ~* 'ä';
?column?
--
f
(1 row)
fis=> SELECT 'Ä' ilike 'ä';
?column?
--
f
(1 row)
I got the same result: false
"=?ISO-8859-1?Q?Ragnar_=D6sterlund?=" <[EMAIL PROTECT
"=?ISO-8859-1?Q?Ragnar_=D6sterlund?=" <[EMAIL PROTECTED]> writes:
> I'm not sure if this is a bug or if I'm doing something wrong. I have
> a database encoded with ISO-8859-1, aka LATIN1. When I do something
> like:
> SELECT 'Ä' ~* 'ä';
> it returns false.
Check the database's locale setting (LC
On Thu, Sep 25, 2003 at 08:46:39PM -0700, Josh Berkus wrote:
>
> NULLIF is the converse of COALESCE().
Oh, ooops! My apologies.
> Any idea when you're going to overhaul the CookBook?
*sighs*
The software is pretty much ready. I'll have time to install and configure
it next week, after my e
Roberto,
> > Another problem is in creating function...
> > How can i create a function that accept and return any type. the type
> > "any" is not allowing as parameter or return type. Is it possible? i want
> > to create a function similar to NULLIF().
>
> Use the standard coalesce().
NULLIF is
Thanks to all of you for your valuable suggesstions
does postgresql internally uses the = operator(text,text) for any other purposes.
i think that overloading it solves the index problem too...
Tom Lane <[EMAIL PROTECTED]> wrote:
Josh Berkus <[EMAIL PROTECTED]>writes:>> How can i create a f
On Wed, Sep 24, 2003 at 23:30:08 -0600,
Roberto Mello <[EMAIL PROTECTED]> wrote:
> On Wed, Sep 24, 2003 at 08:35:50PM -0700, George A.J wrote:
>
> > Another problem is in creating function...
> > How can i create a function that accept and return any type. the type "any" is not
> > allowing as
Josh Berkus <[EMAIL PROTECTED]> writes:
>> How can i create a function that accept and return any type. the type "any"
>> is not allowing as parameter or return type. Is it possible? i want to
>> create a function similar to NULLIF().
> You can't, nor will you be able to -- in te future, some 7.4
On Wed, Sep 24, 2003 at 08:35:50PM -0700, George A.J wrote:
> hai
>
> i am using postgresql 7.3.x. I am converting a database in MS SQL server to
> PostgreSQL.
>
> The main problems i am facing is that in sql server the text comparisons are case
> insensitive. how can i compare text case ins
On Wed, 24 Sep 2003, George A.J wrote:
> The main problems i am facing is that in sql server the text comparisons
> are case insensitive. how can i compare text case insensitive in
> postgresql without using an upper() or lower() function in both sides
> (=). Is there any option to set in postgre
jinujose,
> i am using postgresql 7.3.x. I am converting a database in MS SQL server to
> PostgreSQL.
Good luck to you!
> The main problems i am facing is that in sql server the text comparisons
> are case insensitive. how can i compare text case insensitive in postgresql
> without using an uppe
On Friday 06 Dec 2002 12:33 pm, Pedro Igor wrote:
> Someone knows how config the postgresql for case-insensitive mode ?
There isn't really a case-insensitive mode (for various reasons to do with
locales AFAICT).
There are various case-insensitive comparisons: ILIKE instead of LIKE etc.
A very c
Try:
- The ILIKE operator, for example,
SELECT * FROM account WHERE username ILIKE "test";
- upper() or lower(), for example,
SELECT * FROM accont WHERE lower(username) = "test";
-
Andrew J. Perrin - Assistant Professor of Sociology
Mark <[EMAIL PROTECTED]> writes:
> Even the postgresql documentation asserts something similar to this:
You're reading obsolete documentation. There is no such assertion
(as far as I can find, anyway) in the 7.1 documentation. The speed
advantage of bpchar --- which was always extremely margina
On 30 May 2001 12:53:22 -0400, Tom Lane wrote:
>
> You are operating under misinformation about what's efficient or not.
> There are no performance penalties that I know of for varchar ... if
> anything, bpchar is the less efficient choice, at least in Postgres.
> The extra I/O costs for those
On 30 May 2001, Mark wrote:
> On 30 May 2001 11:16:35 -0700, Stephan Szabo wrote:
> > On Wed, 30 May 2001, Tom Lane wrote:
> >
> > > Mark <[EMAIL PROTECTED]> writes:
> > > > It appears that the behavior of a bpchar compare with a string literal
> > > > is not implicitly trimming the bpchar befo
On 30 May 2001 11:16:35 -0700, Stephan Szabo wrote:
> On Wed, 30 May 2001, Tom Lane wrote:
>
> > Mark <[EMAIL PROTECTED]> writes:
> > > It appears that the behavior of a bpchar compare with a string literal
> > > is not implicitly trimming the bpchar before the compare, which IMHO is
> > > incorr
On Wed, 30 May 2001, Tom Lane wrote:
> Mark <[EMAIL PROTECTED]> writes:
> > It appears that the behavior of a bpchar compare with a string literal
> > is not implicitly trimming the bpchar before the compare, which IMHO is
> > incorrect behavior. Is my opinion valid?
>
> regression=# create tab
Mark <[EMAIL PROTECTED]> writes:
> It appears that the behavior of a bpchar compare with a string literal
> is not implicitly trimming the bpchar before the compare, which IMHO is
> incorrect behavior. Is my opinion valid?
regression=# create table foo (f1 char(20));
CREATE
regression=# insert i
I use a few of them, and in my opinion there is a distinct group of
characters at last in the 8859-1 character set which have a lower and
upper case instance. The ranges are 0xC0 to 0xDD for upper case and 0xE0
to 0xFD for upper (with the exception of 0xD0, 0xD7, 0xF0, and 0xF7).
I haven't examin
It appears that the behavior of a bpchar compare with a string literal
is not implicitly trimming the bpchar before the compare, which IMHO is
incorrect behavior. Is my opinion valid? If so, how difficult of a fix
would this be in terms of time and effort? Should I submit a bug report
to anothe
It appears that the behavior of a bpchar compare with a string literal
is not implicitly trimming the bpchar before the compare, which IMHO is
incorrect behavior. Is my opinion valid? If so, how difficult of a fix
would this be in terms of time and effort? Should I submit a bug report
to anothe
It appears that the behavior of a bpchar compare with a string literal
is not implicitly trimming the bpchar before the compare, which IMHO is
incorrect behavior. Is my opinion valid? If so, how difficult of a fix
would this be in terms of time and effort? Should I submit a bug report
to anothe
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> If upper() and lower() operate on characters in 8859-1 and other character
> sets when the appropriate locale is set, then a difference in the behavior
> of upper() and lower() would seem like a bug.
Au contraire ... upper() and lower() are not sy
Can you please explain in little more detail? I am curious.
I haven't noticed any discussion about upper() being different from
lower() when it comes to such comparisons. As far as I know, upper()
and lower() only operate on ascii characters a-z. If you are using the
default locale, neither func
This is a good point - and it means that Postgres is not following the SQL Standard in
this regard. According to the standard, a scalar string function of a single string
argument should return the same "type" of string as its input. So
upper() should return a fixed-char-field. But it doesn'
We tried these but it didn't work. However, that's because username is
a bpchar and not a varchar, so its padded with blanks. so we tried
where lower(trim(username)) = 'test' and it works. We'll change that
column to varchar. The real problem was in the datatype for username.
Thanks,
On 29 M
select * from account where lower(username) = lower('test');
Troy
>
> Is it possible to execute a query using a where clause that allows case
> insensitive comparison between a field and text.
>
> For example:
>
> select * from account where username = 'test'
>
> where username could be
On Tue, May 15, 2001 at 02:48:24PM +0200, Borek Lupoměský wrote:
>Is there an operator for case insensitive string comparison, or
> should I use regular expression matching with ~* '^string$'?
possibility:
- use upper() / lower() inside query
- regex operators: ~* or !~*
SELECT whatever FROM wherever WHERE lower(yourfield) = 'this';
You can do it with a case inseneitive regex search but they can't use
indexes and can become very slow on large tables..
SELECT whatever FROM wherever WHERE yourfield ~* 'this';
lower() does leak a bit of memory from what I've hear
Joern,
select myfield from tablea where lower(myfield) = 'mysearch';
or
select myfield from tablea where myfield ~* 'mysearch';
Troy
>
> Hello together,
>
> how can I handle case insensitive search in a table?
>
>
>
> --
> Linux is like wigwam - no windows, no gates, apache inside.
> In
36 matches
Mail list logo
