PostgreSQL Discussion - Re: Creating an index on PostgreSQL 7.4.3

Author

Re: Creating an index on PostgreSQL 7.4.3

Martijn van Oosterhout

2006-04-05, 8:25 pm

On Wed, Apr 05, 2006 at 01:59:46PM -0400, Ardian Xharra (Boxxo) wrote:
> Hello,
> I have a client who is still using the version 7.4.3
> and creating an index like this doesn't work:
> CREATE INDEX client__ascname__idx
ON client (TO_ASCII(lower(name
),'LATIN1'));
> Does exists another way of creating an index in varchar who take on consideration also the accents

I think expression indexes need another level of parenthesis, so maybe:

CREATE INDEX client__ascname__idx
ON client ((TO_ASCII(lower(nam
e),'LATIN 1')));

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> 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.

Ardian Xharra \

2006-04-05, 8:25 pm

No, it doesn't work always the same message:

ERROR: parser: parse error at or near "(" at character 47

----- Original Message -----
From: "Martijn van Oosterhout" <kleptog@svana.org>
To: "Ardian Xharra (Boxxo)" <axharra@boxxo.info>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, April 05, 2006 3:01 PM
Subject: Re: [GENERAL] Creating an index on PostgreSQL 7.4.3

On Wed, Apr 05, 2006 at 01:59:46PM -0400, Ardian Xharra (Boxxo) wrote:
> Hello,
> I have a client who is still using the version 7.4.3
> and creating an index like this doesn't work:
> CREATE INDEX client__ascname__idx
ON client (TO_ASCII(lower(name
),'LATIN
1'));

> Does exists another way of creating an index in varchar who take on
consideration also the accents

I think expression indexes need another level of parenthesis, so maybe:

CREATE INDEX client__ascname__idx
ON client ((TO_ASCII(lower(nam
e),'LATIN
1')));

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> 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.

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/301 - Release Date: 04/04/2006

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2006-04-05, 8:25 pm

"Ardian Xharra \(Boxxo\)" <axharra@boxxo.info> writes:
> No, it doesn't work always the same message:
> ERROR: parser: parse error at or near "(" at character 47

In that case you're not really talking to a 7.4 server, but something
older --- either syntax works OK for me on 7.4.

regression=# create table client(name text);
CREATE TABLE
regression=# CREATE INDEX client__ascname__idx
ON client (TO_ASCII(lower(name
),'LATIN 1'));
CREATE INDEX
regression=# select version();
version
----------------------------------------------------------------
PostgreSQL 7.4.12 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

regards, tom lane

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

http://archives.postgresql.org

Ardian Xharra \

2006-04-05, 8:25 pm

Sorry, the exact version is

PostgreSQL 7.3.3 on sparc-sun-solaris2.8, compiled by GCC gcc (GCC) 3.3.2

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Ardian Xharra (Boxxo)" <axharra@boxxo.info>
Cc: <pgsql-general@postgresql.org>; "Martijn van Oosterhout"
<kleptog@svana.org>
Sent: Wednesday, April 05, 2006 3:14 PM
Subject: Re: [GENERAL] Creating an index on PostgreSQL 7.4.3

> "Ardian Xharra \(Boxxo\)" <axharra@boxxo.info> writes:
>
> In that case you're not really talking to a 7.4 server, but something
> older --- either syntax works OK for me on 7.4.
>
> regression=# create table client(name text);
> CREATE TABLE
> regression=# CREATE INDEX client__ascname__idx
ON client
(TO_ASCII(lower(name
),'LATIN 1'));

> CREATE INDEX
> regression=# select version();
> version
> ----------------------------------------------------------------
> PostgreSQL 7.4.12 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
> (1 row)
>
> regards, tom lane
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.385 / Virus Database: 268.3.5/301 - Release Date: 04/04/2006
>
>

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/301 - Release Date: 04/04/2006

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2006-04-05, 8:25 pm

"Ardian Xharra \(Boxxo\)" <axharra@boxxo.info> writes:
> Sorry, the exact version is
> PostgreSQL 7.3.3 on sparc-sun-solaris2.8, compiled by GCC gcc (GCC) 3.3.2

You really, really, really need to update that, ASAP. Even if you
don't need usable expression indexes, compelling reasons can be found at
http://developer.postgresql.org/doc...ase-7-3-14.html
and following pages.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Sponsored Links