uppercase = lowercase

On Friday 14 Feb 2003 8:51 am, jose antonio leo wrote:

Hi!!

How can I make selects not sensitive uppercase and lowercase characters?
This is possible modifying something of psql configuration?

This isn't possible in a general way. If you want "Richard","RICHARD" and
"riCHard" to all test the same you'll need to do something like.

SELECT * FROM people WHERE lower(first_name)='richard';

You can create an index on lower(first_name) if you need to speed things
along.

--
Richard Huxton

Richard Huxton wrote:

On Friday 14 Feb 2003 8:51 am, jose antonio leo wrote:

Hi!!

How can I make selects not sensitive uppercase and lowercase characters?
This is possible modifying something of psql configuration?

This isn't possible in a general way. If you want "Richard","RICHARD" and
"riCHard" to all test the same you'll need to do something like.

SELECT * FROM people WHERE lower(first_name)='richard';

You can create an index on lower(first_name) if you need to speed things
along.

I thought you could only create indices on fields - not on the results
of operations on those fields.
Is it truly possible to create an index on lets say upper(last_name)?

k.

________________________________________________________________________
This email has been scanned for all viruses by the MessageLabs SkyScan
service. For more information on a proactive anti-virus service working
around the clock, around the globe, visit http://www.messagelabs.com
________________________________________________________________________

On Fri, 2003-02-14 at 11:20, Ken Guest wrote:

Is it truly possible to create an index on lets say upper(last_name)?

CREATE INDEX uppername_index ON mytable (upper(last_name));

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"God be merciful unto us, and bless us; and cause his
face to shine upon us." Psalms 67:1

--- Oliver Elphick <olly@lfix.co.uk> wrote:

On Fri, 2003-02-14 at 11:20, Ken Guest wrote:

Is it truly possible to create an index on lets

say upper(last_name)?

CREATE INDEX uppername_index ON mytable
(upper(last_name));

Unless something has changed, I believe that the
"upper" statement would need to be wrapped in a
function marked "immutable". Otherwise the index
would not be used.

__________________________________________________
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

On Fri, 14 Feb 2003, Jeff Eckermann wrote:

--- Oliver Elphick <olly@lfix.co.uk> wrote:

On Fri, 2003-02-14 at 11:20, Ken Guest wrote:

Is it truly possible to create an index on lets

say upper(last_name)?

CREATE INDEX uppername_index ON mytable
(upper(last_name));

Unless something has changed, I believe that the
"upper" statement would need to be wrapped in a
function marked "immutable". Otherwise the index
would not be used.

Upper should be immutable already.

On Fri, 14 Feb 2003, Ken Guest wrote:

Richard Huxton wrote:

On Friday 14 Feb 2003 8:51 am, jose antonio leo wrote:

Hi!!

How can I make selects not sensitive uppercase and lowercase characters?
This is possible modifying something of psql configuration?

This isn't possible in a general way. If you want "Richard","RICHARD" and
"riCHard" to all test the same you'll need to do something like.

SELECT * FROM people WHERE lower(first_name)='richard';

You can create an index on lower(first_name) if you need to speed things
along.

I thought you could only create indices on fields - not on the results
of operations on those fields.
Is it truly possible to create an index on lets say upper(last_name)?

Yes, these are called functional indexes. The only caveat is that the
arguments must all be columns, not constants.

So,

create index bubbahotep on pyramids (substr(col1,0,4));

will fail, but

update pyramids set col2=0,col3=4;
create index test on pyramids (substr(col1,col2,col3));
select * from pyramids where substr(col1,col2,col3) = 'abcd';

will work.

If you didn't know about them, then you probably don't know about partial
indexes either, very useful. let's say you have a table where 99.9% of
all rows have the boole field approved marked true. You can create a
small index on the false ones like so:

create index test on articles (approved) where approved is false;

then

select * from articles where approved is false

should return quickly. Note that the parts of the where clause pretty
much need to be identical, i.e.

select * from articles where approved is not true;
select * from articles where approved !='t';
select * from aticles where approved ='f';

will not use that index, since they aren't the same suntax. Plus some of
those aren't really equivalent, given nulls in you data set.

how do you mark it immutable?

2/14/2003 7:08:37 AM, Jeff Eckermann <jeff_eckermann@yahoo.com> wrote:

At 11:53 AM 2/14/03 +0000, Oliver Elphick wrote:

On Fri, 2003-02-14 at 11:20, Ken Guest wrote:

Is it truly possible to create an index on lets say upper(last_name)?

CREATE INDEX uppername_index ON mytable (upper(last_name));

If you want case insensitivity is it better to do lower or upper? Or it
doesn't matter?

Thanks,
Link.

--- Dennis Gearon <gearond@cvc.net> wrote:

how do you mark it immutable?

Check the "create function" section in the manual,
under "sql commands". The equivalent syntax for
versions prior to 7.3 is "with (iscachable)", which
you will see if you are running 7.2 or earlier.

We've just heard from one of the experts that you
should not have a problem with just "upper". The
wrapping technique will probably be necessary for
custom functions however.

__________________________________________________
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com

Hi again!!

How can I make selects not accent sensitive?

The select:
Select * from articul where desc like 'jamon'
return the sames record that is
Select * from articul where desc like 'jamon'

Thanks

On Mon, 2003-02-17 at 11:34, jose antonio leo wrote:

Hi again!!

How can I make selects not accent sensitive?

The select:
Select * from articul where desc like 'jamon'
return the sames record that is
Select * from articul where desc like 'jamon'

Select * from articul where to_ascii(desc) like 'jamon'

Cheers

Tony Grant
--
www.tgds.net Library management software toolkit,
redhat linux on Sony Vaio C1XD,
Dreamweaver MX with Tomcat and PostgreSQL

How can I unlock a row lock for a select for update?

Tk, Jose Antonio Leo

On Mon, 2003-03-10 at 09:26, jose antonio leo wrote:

How can I unlock a row lock for a select for update?

Commit the transaction.

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

We don't have unlock. You have to commit the transaction.

---------------------------------------------------------------------------

jose antonio leo wrote:

How can I unlock a row lock for a select for update?

Tk, Jose Antonio Leo

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sorry, I explain more...
I have got a aplication that it do maintenance of DB, One user are updating
a row locked for a select for update and this user switch off your pc and he
leaves the row locked. What can I do for unloked?. Commit it would have to
do user session, no?

-----Mensaje original-----
De: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]En nombre de Bruce Momjian
Enviado el: lunes, 10 de marzo de 2003 17:56
Para: jose antonio leo
CC: pgsql-general
Asunto: Re: [GENERAL] unlock Select for update

We don't have unlock. You have to commit the transaction.

---------------------------------------------------------------------------

jose antonio leo wrote:

How can I unlock a row lock for a select for update?

Tk, Jose Antonio Leo

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Mon, 2003-03-10 at 12:15, jose antonio leo wrote:

Sorry, I explain more...
I have got a aplication that it do maintenance of DB, One user are updating
a row locked for a select for update and this user switch off your pc and he
leaves the row locked.

When the client disconnects, any uncommitted transaction (if any) will
be aborted, so the lock will be released.

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Unfortunately In my aplication this non occur . When the client disconnects
the row keep blocked and then I have kill the process manually for unlocked
the row.

You know why it happends?

-----Mensaje original-----
De: Neil Conway [mailto:neilc@samurai.com]
Enviado el: lunes, 10 de marzo de 2003 19:05
Para: jose antonio leo
CC: Bruce Momjian; pgsql-general
Asunto: RE: [GENERAL] unlock Select for update

On Mon, 2003-03-10 at 12:15, jose antonio leo wrote:

Sorry, I explain more...
I have got a aplication that it do maintenance of DB, One user are

updating

a row locked for a select for update and this user switch off your pc and

he

leaves the row locked.

When the client disconnects, any uncommitted transaction (if any) will
be aborted, so the lock will be released.

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

On Tue, Mar 11, 2003 at 09:10:21AM +0100, jose antonio leo wrote:

Unfortunately In my aplication this non occur . When the client disconnects
the row keep blocked and then I have kill the process manually for unlocked
the row.

You know why it happends?

You're obviously doing something wrong, since if the client disconnected,
then there would be nothing to kill. You'll need to explain more about your
system before we can help you.

-----Mensaje original-----
De: Neil Conway [mailto:neilc@samurai.com]
Enviado el: lunes, 10 de marzo de 2003 19:05
Para: jose antonio leo
CC: Bruce Momjian; pgsql-general
Asunto: RE: [GENERAL] unlock Select for update

On Mon, 2003-03-10 at 12:15, jose antonio leo wrote:

Sorry, I explain more...
I have got a aplication that it do maintenance of DB, One user are

updating

a row locked for a select for update and this user switch off your pc and

he

leaves the row locked.

When the client disconnects, any uncommitted transaction (if any) will
be aborted, so the lock will be released.

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Yes...
It is a web system. I Send part of code and explain in it.

JSP1
<jsp:useBean id="beanOfe" scope="session" class="eurocash.ManOfe"/> This
useBean instance ManOfe and connect to DB.

The user select a row to update and the jsp call the method
consultaTransaccional
beanOfe.consultaTransaccional(hae) //call the method consultaTransaccional
of ManOfe

JSP2
<jsp:useBean id="beanOfe" scope="session" class="eurocash.ManOfe"/> This
useBean instance ManOfe and connect to DB.

The user update the row and submit. the jsp call to method modifica...

beanOfe.modifica(hae) //call the method modifica of ManOfe

CLASS
public class ManOfe {
public UtilBD utilBD = new UtilBD(); // connect to DB

public Vector consultaTransaccional ( HOfe hOfeParam )
{
Vector vHOfe = new Vector();
hOfe.utilBD.inicioTransaccion(); //init transacstion
vHOfe = hOfe.consultaForUpdate( hOfeParam ); //execute select for
update. Lock the rows.
return (vHOfe); //return to jsp2
}

public HOfe modifica ( HOfe hOfeParam )
{
hOfeReturn = hOfe.modifica( hOfeParam ); // update the row locks
before
hOfe.utilBD.finalTransaccion(); //end transaction, unlock row.
return (hOfeReturn);
}

}

In this system if the web user close your browser the row keeps blocked.

I hope that you understand to me

-----Mensaje original-----
De: Martijn van Oosterhout [mailto:kleptog@svana.org]
Enviado el: martes, 11 de marzo de 2003 10:01
Para: jose antonio leo
CC: Neil Conway; pgsql-general; Bruce Momjian
Asunto: Re: [GENERAL] unlock Select for update

On Tue, Mar 11, 2003 at 09:10:21AM +0100, jose antonio leo wrote:

Unfortunately In my aplication this non occur . When the client

disconnects

the row keep blocked and then I have kill the process manually for

unlocked

the row.

You know why it happends?

You're obviously doing something wrong, since if the client disconnected,
then there would be nothing to kill. You'll need to explain more about your
system before we can help you.

-----Mensaje original-----
De: Neil Conway [mailto:neilc@samurai.com]
Enviado el: lunes, 10 de marzo de 2003 19:05
Para: jose antonio leo
CC: Bruce Momjian; pgsql-general
Asunto: RE: [GENERAL] unlock Select for update

On Mon, 2003-03-10 at 12:15, jose antonio leo wrote:

Sorry, I explain more...
I have got a aplication that it do maintenance of DB, One user are

updating

a row locked for a select for update and this user switch off your pc

and

he

leaves the row locked.

When the client disconnects, any uncommitted transaction (if any) will
be aborted, so the lock will be released.

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/