[SQL] Is index usage in LIKE-queries with UTF-8 implemented in PG-8.3 ?
Hi all. Anybody knows if the following query will use an index-scan in PG-8.3? SELECT name FROM person WHERE name LIKE 'and%'; I know this works in "C"-locale, but I need it with UTF-8, probably the rest of the world soon too... -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Is index usage in LIKE-queries with UTF-8 implemented in PG-8.3 ?
Hello, it's possible. You have to use varchar_pattern_ops: CREATE INDEX like_index ON person(name varchar_pattern_ops); Regards Pavel Stehule 2007/6/28, Andreas Joseph Krogh <[EMAIL PROTECTED]>: Hi all. Anybody knows if the following query will use an index-scan in PG-8.3? SELECT name FROM person WHERE name LIKE 'and%'; I know this works in "C"-locale, but I need it with UTF-8, probably the rest of the world soon too... -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] record fields as columns in reports
Hi everybody, I have a script that runs every night and produces a list of a few error conditions and the number. My manager would like it in a form where he can produce some charts in a spreadsheet (that's probably why he's a manager ";-). So the table I store the errormessages in has a format like this: CREATE TABLE repport_history ( rundate date, errordescription character varying(255), number bigint ) And I would like an output with something like: 06/22 06/2306/2406/25 ERROR1 10 10 9 8 ERROR2250300 220 200 ERROR3 4 2 0 0 (probably in csv or something like that but that is the easy part ";-) The problems are: - how to create a dynamic result type? (is that possible in a stored procedure?) - how to make sure that missing records are reported as 0 (some errors might not have entries on some dates) Has somebody ever made a generic solution for something like this? (or do I need a reporting tool?) Thanks in advance, Reinoud -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Is index usage in LIKE-queries with UTF-8 implemented in PG-8.3 ?
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > Anybody knows if the following query will use an index-scan in PG-8.3? > SELECT name FROM person WHERE name LIKE 'and%'; > I know this works in "C"-locale, but I need it with UTF-8, "C locale" and "UTF8" are entirely orthogonal things. regards, tom lane ---(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: [SQL] record fields as columns in reports
On Thu, Jun 28, 2007 at 04:46:15PM +0200, Reinoud van Leeuwen wrote: > And I would like an output with something like: > > 06/22 06/2306/2406/25 > ERROR1 10 10 9 8 > ERROR2250300 220 200 > ERROR3 4 2 0 0 I think this should be possible with the "crosstab" functionality delivered in Joe Conway's tablefunc package, in contrib/. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(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: [SQL] record fields as columns in reports
On 6/28/07, Reinoud van Leeuwen <[EMAIL PROTECTED]> wrote: So the table I store the errormessages in has a format like this: CREATE TABLE repport_history ( rundate date, errordescription character varying(255), number bigint ) And I would like an output with something like: 06/22 06/2306/2406/25 ERROR1 10 10 9 8 ERROR2250300 220 200 ERROR3 4 2 0 0 1. See contrib/tablefunc. 2. PivotTables (OpenOffice.org or Excel). ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is index usage in LIKE-queries with UTF-8 implemented in PG-8.3 ?
On Thursday 28 June 2007 17:20:56 Tom Lane wrote: > Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > > Anybody knows if the following query will use an index-scan in PG-8.3? > > SELECT name FROM person WHERE name LIKE 'and%'; > > I know this works in "C"-locale, but I need it with UTF-8, > > "C locale" and "UTF8" are entirely orthogonal things. > > regards, tom lane I had the impression that LIKE-queries of type LIKE '123%' couldn't use an index if encoding was UTF-8. I was wrong obviously. The varchar_pattern_ops suggested by Pavel did the trick for me. Tom: Can you comment on, very short, if the discussion on HACKERS with title "like/ilike improvements" will improve LIKE queries like this? Anybody knows if queries of type "LIKE '%234%'" ever will be able to use indexes, and if someone is working on it? I'm sure I'm not the only one who would very much appreciate it:-) -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(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: [SQL] Is index usage in LIKE-queries with UTF-8 implemented in PG-8.3 ?
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > Anybody knows if queries of type "LIKE '%234%'" ever will be able to use > indexes, and if someone is working on it? Perhaps you are looking for full text search (contrib/tsearch2)? regards, tom lane ---(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: [SQL] Is index usage in LIKE-queries with UTF-8 implemented in PG-8.3 ?
On Thursday 28 June 2007 17:54:59 Tom Lane wrote: > Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > > Anybody knows if queries of type "LIKE '%234%'" ever will be able to use > > indexes, and if someone is working on it? > > Perhaps you are looking for full text search (contrib/tsearch2)? No, I know very well that tsearch2 exists, and use it, but it doesn't handle "contains" queries. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] SQL problem...
On Jun 28, 1:43 pm, "Bauhaus" <[EMAIL PROTECTED]> wrote: > I have the following table Price: > > FuelID PriceDate Price > LPG1/05/2007 0,2 > LPG13/05/2007 0,21 > SPS 2/05/2007 1,1 > SPS 15/05/2007 1,08 > > And I have to make the following query: > > FuelID PriceDate_from PriceDate_To Price > LPG1/05/2007 13/05/2007 0,2 > SPS 2/05/2007 15/05/20071,1 > LPG13/05/2007 0,21 > SPS 15/05/2007 1,08 SELECT fuelid, pricedate AS pricedate_from , (SELECT MIN(pricedate) FROM price WHERE fuelid = s.fuelid AND pricedate > s.pricedate) AS pricedate_to, price FROM price s ORDER BY pricedate_from; ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] record fields as columns in reports
am Thu, dem 28.06.2007, um 16:46:15 +0200 mailte Reinoud van Leeuwen folgendes: > Hi everybody, > > I have a script that runs every night and produces a list of a few error > conditions and the number. > > My manager would like it in a form where he can produce some charts in a > spreadsheet (that's probably why he's a manager ";-). > > So the table I store the errormessages in has a format like this: > > CREATE TABLE repport_history > ( > rundate date, > errordescription character varying(255), > number bigint > ) > > > And I would like an output with something like: > > 06/22 06/2306/2406/25 > ERROR1 10 10 9 8 > ERROR2250300 220 200 > ERROR3 4 2 0 0 > > > (probably in csv or something like that but that is the easy part ";-) > > The problems are: > - how to create a dynamic result type? (is that possible in a stored > procedure?) > - how to make sure that missing records are reported as 0 > (some errors might not have entries on some dates) You can do it with conditionals. Circa: select errordescription, sum (case when rundate = '2007-06-22'::date then number else 0 end) as "06/22", sum(case when rundate = '2007-06-23'::date then number else 0 end) as "06/23" ... group by errordescription order by errordescription; and you can use a VIEW and change the fix conditions to expressions with current_date. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
