Re: [GENERAL] problem with thai language==again
Hi Tom lane How can I get in deep about locale and encoding? Please advise url or resource! thanks for your kindness tina - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: Sent: Sunday, February 13, 2005 1:48 PM Subject: Re: [GENERAL] problem with thai language > <[EMAIL PROTECTED]> writes: > > I use postgresql7.2 / redhat8 > > I try "select 'X1'='X2' ;" and X1=(D button) in thai language, = > > X2=(8 button) in thai language > > result is true .This is wrong result!. > > We've seen a lot of reports of bizarre behavior when you select a > database encoding that does not match the character set implied by > the locale setting you're using. Apparently strcoll() on many platforms > goes nuts when handed input that is not legally encoded according to > what it thinks the character set is. > > In short: check your locale and your encoding. > > regards, tom lane > > ---(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/docs/faq
Re: [GENERAL] regular expressions in query
elein wrote: No doubt someone more adept at perl can write this function as a one-liner. create or replace function just_digits(text) returns text as $$ my $innum = $_[0]; $innum =~ s/\D//g; return $innum; $$ language 'plperl' SELECT telephone FROM addresses WHERE user_id = 'bob' AND just_digits(telephone) = '1115551212'; --elein I've thought about things like this in the past, and a thought that occurred to me was to add a functional index on just_digits(telephone) to the table. Would this not allow the above query to use an index while searching? -- Russ. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] regular expressions in query
At 09:57 AM 2/13/2005 +, Russ Brown wrote: I've thought about things like this in the past, and a thought that occurred to me was to add a functional index on just_digits(telephone) to the table. Would this not allow the above query to use an index while searching? I think it should. But for phone numbers it may be better to reverse the digits before indexing - usually whilst the area code changes, the last 4 or 5 digits don't change. This way you can do a LIKE search on *5678. Where the number ends with 5678. I'm not sure how to get Postgresql to index from the ending to the start of a string vs the normal from the start to the end, so in my webapp I reversed it at the application layer. If you are going to do this sort of thing at the application layer you might as well do the nondigit removal there too. e.g. $phone=~tr/0-9%_//cd; # I allowed the wildcards % and _ $phone=reverse $phone; You may still wish to store the phone numbers "as is" for display purposes. Link. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] regular expressions in query
Lincoln Yeoh wrote: At 09:57 AM 2/13/2005 +, Russ Brown wrote: I've thought about things like this in the past, and a thought that occurred to me was to add a functional index on just_digits(telephone) to the table. Would this not allow the above query to use an index while searching? I think it should. But for phone numbers it may be better to reverse the digits before indexing - usually whilst the area code changes, the last 4 or 5 digits don't change. This way you can do a LIKE search on *5678. Where the number ends with 5678. I'm not sure how to get Postgresql to index from the ending to the start of a string vs the normal from the start to the end, so in my webapp I reversed it at the application layer. If you are going to do this sort of thing at the application layer you might as well do the nondigit removal there too. e.g. $phone=~tr/0-9%_//cd; # I allowed the wildcards % and _ $phone=reverse $phone; You may still wish to store the phone numbers "as is" for display purposes. Link. make sure the table stores as text rather than as numeric data. then you can use the excellent perl string tools to pull the last 4 characters of the number. $base=((strlen-4,strlen) $base being the last 4 digits. then convert to numeric to test against search requirements. Jaqui ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Website Documentation
Tom Lane wrote: > Bruce Momjian writes: > > Russell Smith wrote: > >> The release notes for 8.0 and 7.4 only go to version 8.0.0 and 7.4.6. > > > If you want the changes to the server between releases see the Release > > notes in the documentation. > > I think his point is that those notes aren't up on the website. We > should make a regular practice of updating the on-line docs to match > each minor release, even if nothing changed except the release notes > (which is often not the case, anyway). Uh, my next question is why isn't the documentation updated on the web site for each minor release? We update the tarball HTML for each release. This seems like a pretty large omission. Is it the web comments that prevent it? -- 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 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] comparing dates with timestamps ?
how to compare dates with timestamp..if i dont care about the hour:minute part of the timestamp... i.e. i want to be able to do exact match : timestamp in (select date from table where .) tia ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] comparing dates with timestamps ?
On Sun, 13 Feb 2005 18:57:30 +0200, raptor <[EMAIL PROTECTED]> wrote: > how to compare dates with timestamp..if i dont care about > the hour:minute part of the timestamp... i.e. i want > to be able to do exact match : > > timestamp in (select date from table where .) timestamp::DATE in ((select date from table where...)) -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-www] [GENERAL] Website Documentation
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian > Sent: 13 February 2005 15:16 > To: Tom Lane > Cc: Russell Smith; pgsql-general@postgresql.org; > [EMAIL PROTECTED] > Subject: Re: [pgsql-www] [GENERAL] Website Documentation > > Uh, my next question is why isn't the documentation updated on the web > site for each minor release? We update the tarball HTML for each > release. This seems like a pretty large omission. > > Is it the web comments that prevent it? No, it's my free time, and if I'm honest, the fact that I usually forget all about it (perhaps a reminder email could be added to the release procedure?). It's on my list to do now anyway - hopefully I'll get it done this week, however I'm off to Japan on Wednesday so if I haven't done it by then it'll be mid-to-late the following week. Regards, Dave. ---(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: [pgsql-www] [GENERAL] Website Documentation
Dave Page wrote: > > Uh, my next question is why isn't the documentation updated on the web > > site for each minor release? We update the tarball HTML for each > > release. This seems like a pretty large omission. > > > > Is it the web comments that prevent it? > > No, it's my free time, and if I'm honest, the fact that I usually forget > all about it (perhaps a reminder email could be added to the release > procedure?). It's on my list to do now anyway - hopefully I'll get it > done this week, however I'm off to Japan on Wednesday so if I haven't > done it by then it'll be mid-to-late the following week. Agreed. Who controls the release procedure checklist? Marc? -- 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 7: don't forget to increase your free space map settings
Re: [pgsql-www] [GENERAL] Website Documentation
> -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > Sent: 13 February 2005 18:12 > To: Dave Page > Cc: Tom Lane; Russell Smith; pgsql-general@postgresql.org; > [EMAIL PROTECTED] > Subject: Re: [pgsql-www] [GENERAL] Website Documentation > > Agreed. Who controls the release procedure checklist? Marc? I thought you did :-) /D ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] comparing dates with timestamps ?
On Sun, 2005-02-13 at 18:57 +0200, raptor wrote: > how to compare dates with timestamp..if i dont care about > the hour:minute part of the timestamp... i.e. i want > to be able to do exact match : > > timestamp in (select date from table where .) Try something like this: (created is a timestamp data type) SELECT created::date FROM orders.payment WHERE id = 100; created 2004-11-28 or SELECT id, email FROM orders.payment WHERE created::date = now()::date; -Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting & Development * --- Now hosting Ruby on Rails Apps --- / ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-www] [GENERAL] Website Documentation
Dave Page wrote: > > > > -Original Message- > > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > > Sent: 13 February 2005 18:12 > > To: Dave Page > > Cc: Tom Lane; Russell Smith; pgsql-general@postgresql.org; > > [EMAIL PROTECTED] > > Subject: Re: [pgsql-www] [GENERAL] Website Documentation > > > > Agreed. Who controls the release procedure checklist? Marc? > > I thought you did :-) Uh, I do the release changes list. I think we need to add the rebuild to the release "packaging" checklist which I think Marc deals with. -- 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 [EMAIL PROTECTED])
[GENERAL] unsubscribe
__ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-www] [GENERAL] Website Documentation
Dave Page wrote: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 13 February 2005 18:12 To: Dave Page Cc: Tom Lane; Russell Smith; pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: Re: [pgsql-www] [GENERAL] Website Documentation Agreed. Who controls the release procedure checklist? Marc? I thought you did :-) Who's on first?? -- Until later, Geoffrey ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] find next in an index
On Sun, 2005-02-13 at 01:24 -0500, Greg Stark wrote:
> Neil Dugan <[EMAIL PROTECTED]> writes:
>
> > Hi,
> > I am trying to find out how to get the next record according to a
> > particular index.
> > I have a table with a name field and a serial field. The name field
> > isn't unique so I made an index on name(varchar) & serialno(bigserial).
> > I also have an index just on 'name'. I am having trouble working out
> > the syntax for the query.
> >
> > select * from table where name>='jack' and serialno!='2'
> > order by name,serialno;
>
> >From what you describe it sounds like you are really asking for
>
> SELECT *
> FROM table
> WHERE (name > 'jack')
> OR (name = 'jack' AND serialno>2)
> ORDER BY name, serialno
> LIMIT 1
>
> However Postgres doesn't really handle this very well. If it uses the index at
> all it fetches all the records starting from the beginning of the table
> stopping when it finds the right one.
>
> One option is to do
>
> SELECT *
> FROM table
> WHERE name >= 'jack'
>AND ((name > 'jack') OR (name = 'jack' AND serialno>2))
> ORDER BY name, serialno
> LIMIT 1
>
> Which is fine as long as there are never too many records with the name
> 'jack'. If you have can possibly have hundreds of records with the name 'jack'
> then it's going to spend time skimming through all of them even if you're
> already far down the list.
>
> To guarantee reasonable behaviour it looks like you have to do this:
>
> (
> SELECT *
> FROM table
>WHERE name > 'jack'
>ORDER BY name, serialno
>LIMIT 1
> ) UNION ALL (
> SELECT *
> FROM table
>WHERE name = 'jack' AND serialno>2
>ORDER BY name, serialno
>LIMIT 1
> )
> ORDER BY name, serialno
> LIMIT 1
>
>
>
> I think there's a todo item about making indexes handle the row-wise
> comparison operators like:
>
> WHERE (name,serialno) > ('jack',2)
>
> But that doesn't work properly in Postgres currently. (It may seem to, but
> don't be confused, it's actually not doing what you want). It's too bad since
> it would be a nice clean simple way to get exactly the right behaviour.
>
Thanks Greg,
I have put your suggestion (number 2) in my code. It is working quite
well.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Index search order hints for R-Tree indexes
Hi,
Im really noob when it comes to R-Tree indexes, but we have a use for one.
What I've done is setup time along the X axis and some other quantity (such
as a the minimum and maximum of some other, pertinent value, which we want
to search quickly) along the Y axis. These quite convieniently make a box
shape.
So once the above boxes are indexed, then the search query can construct any
box it wants and use the '&&' operator to test for overlaps. This is
working really well on the test data we have.
Now the search query wants the first N results (N is small, like 10-20
small) from the X axis (the time range) from the possibly hundreds of
matching rows.
What I dont know is how to express in the query that those are the rows I
want. If I was using normal float8 or int8 values, for example, then I'd use
something like
SELECT * FROM testtable WHERE starttime > 1108351025
ORDER BY starttime ASC LIMIT 10;
but I dont know how to express the equivalent for boxes.
Currently I ORDER BY one of the time components ('starttime') which makes up
one of the coordinates of the box. Of course postgres has no idea it makes
up one of the box coordinates, so it extracts all possible matches into a
temporary table and sorts that to get what I want. This is suboptimal.
Ive tried simply ordering by the boxes, which results in
ERROR: could not identify an ordering operator for type box
How can I do this, or is it a limitation of the geometric indexes?
.Guy
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] possible bug with compound index.
I am using PostgreSQL 7.4.7 I have a table with serveral fields two of these are a serialno (bigserial) and name(varchar). I have created two indexs on these fields. 1) on name 2) on name,serialno if I use the command 'select * from table order by name limit 1' everything is OK if I use the command 'select * from table order by name desc limit 1' everything is OK if I use the command 'select * from table order by name,serialno limit 1' everything is OK if I use the command 'select * from table order by name,serialno desc limit 1' The command is SLOW and gives back the INCORRECT data. Doing an 'explain' on the above query says that the index wasn't used. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] possible bug with compound index.
On Mon, 14 Feb 2005, Neil Dugan wrote: > I am using PostgreSQL 7.4.7 > I have a table with serveral fields two of these are a serialno > (bigserial) and name(varchar). I have created two indexs on these > fields. > 1) on name > 2) on name,serialno > if I use the command > 'select * from table order by name limit 1' > everything is OK > if I use the command > 'select * from table order by name desc limit 1' > everything is OK > if I use the command > 'select * from table order by name,serialno limit 1' > everything is OK > if I use the command > 'select * from table order by name,serialno desc limit 1' > The command is SLOW and gives back the INCORRECT data. Without any example data and result, it's hard to say what you were expecting or got. I'd expect the highest numbered serialno record for the lowest sorting name from the above which is what any tests I've tried do. Right now I believe it won't consider index usage because the ordering asked for doesn't match either a forward ordering of the index(name, serialno) or a reverse order (name desc, serialno desc). ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] possible bug with compound index.
On Sun, 2005-02-13 at 20:40 -0800, Stephan Szabo wrote: > On Mon, 14 Feb 2005, Neil Dugan wrote: > > > I am using PostgreSQL 7.4.7 > > I have a table with serveral fields two of these are a serialno > > (bigserial) and name(varchar). I have created two indexs on these > > fields. > > 1) on name > > 2) on name,serialno > > if I use the command > > 'select * from table order by name limit 1' > > everything is OK > > if I use the command > > 'select * from table order by name desc limit 1' > > everything is OK > > if I use the command > > 'select * from table order by name,serialno limit 1' > > everything is OK > > if I use the command > > 'select * from table order by name,serialno desc limit 1' > > The command is SLOW and gives back the INCORRECT data. > > Without any example data and result, it's hard to say what you were > expecting or got. I'd expect the highest numbered serialno record for the > lowest sorting name from the above which is what any tests I've tried do. > > Right now I believe it won't consider index usage because the ordering > asked for doesn't match either a forward ordering of the index(name, > serialno) or a reverse order (name desc, serialno desc). Thanks Stephan, for the hint on using desc twice. 'select * from table order by name desc,serialno desc limit 1' does work. I didn't realise it was separating the order into two sections, I'm sorry if this caused any trouble for you. My mistake, bye! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
