Re: [SQL] encoding
Hi, And excuse me ? [EMAIL PROTECTED] postgresql-8.0.2]# ./configure --enable-locale checking build system type... i686-pc-linux-gnu checking host system type... i686-pc-linux-gnu checking which template to use... linux checking whether to build with 64-bit integer date/time support... BR, Aarni On Saturday 07 May 2005 23:54, you wrote: > Aarni Ruuhimäki wrote: > > You might also want (re?)configure your Pg-system with > > --enable-locale and set your preferred locale and db default encoding > > in initdb to suit your needs, in order to have alphabetical sortings > > etc. work ok. > > If you're still using a PostgreSQL version that has the --enable-locale > option then you rather need to upgrade. -- This is a bugfree broadcast to you from **Kmail** on **Fedora Core 2** linux system -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] encoding
Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <[EMAIL PROTECTED]> writes: >> If you're still using a PostgreSQL version that has the --enable-locale >> option then you rather need to upgrade. > And excuse me ? > [EMAIL PROTECTED] postgresql-8.0.2]# ./configure --enable-locale > checking build system type... i686-pc-linux-gnu Proves nothing, since configure scripts ignore unrecognized --enable and --with options. (The Autoconf boys steadfastly maintain that that's a feature not a bug, but I disagree.) The more relevant check is "configure --help | grep locale" regards, tom lane ---(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
[SQL] Doing range-predicates right (correction)
A few weeks ago I posted a way to do efficient range predicate joins, given only B-tree indexes. I've since gotten back home and looked at the code I last used. My apologies for an offhand hasty posting. The following is the solution I worked out when I used this method on a large data conversion. It has the advantage (over what I posted) that the indexable ranges fit more tightly around the actual ranges --- a pseudo-range will never be more than twice as wide as the original range. For those who didn't see the original, the problem was, how do you get efficient lookups against large tables of the form: TABLE Ranges(rangeid , lower INT, upper INT) ... or any range-bound type that can be mapped to int (e.g. by mapping timestamps to epoch-seconds). ... with queries like select rangeid, sampleid from Samples join Ranges on Samples.val between Ranges.lower and Ranges.upper The problem is that a Btree index on Ranges.lower (or .upper) is ineffective; in most cases, the query planner will rightly ignore it. One (my) solution is to map the given ranges into slightly larger ranges, that have a small number of different widths, and all start on a more limited set of boundaries. One way to do this is to map all ranges to ranges that have a width that is a power of two, and that begin on a whole multiple of that power of two. Unfortunately, if you just map a range (width) to the smallest power of 2 greater than (upper-lower), then lower and upper may be in two different ranges of that width. For example, if your original range is [1003..1030] (i.e. width 28), the power of 2 that covers this range is 32, but 1003 is in the range [992..1023] and 1030 is the one above it. A sloppy fix for this is to take the next higher power of two as the pseudo-width. The original solution created a new version of Ranges that had as many rows as the original Ranges table. The following solution creates a new version with no more than twice as many rows, but with. -- Function to return the lowest power of two greater than -- a given inclusive interval: create or replace function width2(int, int) returns int immutable language 'plpgsql' as ' begin return pow(2, ceil(log(2, $2-$1+1)))::int; end'; -- Construct an augmented Ranges table: select rangeid, lower, upper, width, start-mod(start, width) as start intoPseudoRange from ( select rangeid, lower, upper, start, width2(start, finish) as width from ( select rangeid, lower, upper, lower as start, upper-mod(upper, width2(lower,upper))-1 as finish from Range union all select rangeid, lower, upper, upper-mod(upper, width2(lower,upper)) as start, upper as finish from Range ) as X where start <= finish ) as Y; create unique index PR_start_width on PseudoRange(start,width); The query using PseudoRange also uses a table PseudoWidth. If (lower) and (upper) are ints, this table can at most have 31 rows with values (1,2,4,8,...). This can also be calculated by: select distinct width into PseudoWidth from PseudoRange; ... which will have fewer values, for proportionately faster lookups. The lookup query becomes: select rangeid, sampleid from Samples, PseudoRange as PR, PseudoWidth as PW where PR.width = PW.width and PR.start = Samples.val - (Samples.val % PW.width) and Samples.val between PR.lower and PR.upper The usual query plan will do at most (!) 31 index lookups per Sample row. If this is unacceptable, a power greater than 2 can be used. -- "Dreams come true, not free." -- S.Sondheim ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] encoding
Ain't trying to prove no one no thing. So sorry. Thamks. On Sunday 08 May 2005 19:43, you wrote: > Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <[EMAIL PROTECTED]> writes: > >> If you're still using a PostgreSQL version that has the --enable-locale > >> option then you rather need to upgrade. > > > > And excuse me ? > > > > [EMAIL PROTECTED] postgresql-8.0.2]# ./configure --enable-locale > > checking build system type... i686-pc-linux-gnu > > Proves nothing, since configure scripts ignore unrecognized --enable > and --with options. (The Autoconf boys steadfastly maintain that > that's a feature not a bug, but I disagree.) > > The more relevant check is "configure --help | grep locale" > >regards, tom lane -- Aarni Ruuhimäki Megative Tmi Pääsintie 26 45100 Kouvola Finland +358-5-3755035 +358-50-4910037 www.kymi.com | cfm.kymi.com -- This is a bugfree broadcast to you from **Kmail** on **Fedora Core 2** linux system -- Linux is like a wigwam - no windows, no gates and a free apache inside. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] interesting SQL puzzle - concatenating column with itself.
Hi- I'm doing a conversion from an older database in which a memo field was handled by storing one line per record and then displaying the related records in order. I want to compress all of the lines into a single text field with one record per memo entry. So for instance, the old database looks like this: memo_id | sequence | memo_text --- 666 | 1| The quick 666 | 2| red fox 666 | 3| jumped over 666 | 4| the lazy brown dog And my goal is to transform each group of lines into a single record that looks like this: memo_id | memo_text -- 666 | The quick red fox jumped over the lazy brown dog Any thoughts on how to do this via sql? I could write a little program to do it, but it seems like there must be a pure-SQL solution that I'm not seeing. Thanks -Nick -- -- Nick Fankhauser [EMAIL PROTECTED] Phone 765.935.4283 Fax 765.962.9788 Ray Ontko & Co. - Software Consulting Services http://www.ontko.com ---(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: [SQL] interesting SQL puzzle - concatenating column with itself.
On Sun, May 08, 2005 at 01:55:56PM -0500, Nick Fankhauser wrote: > > Hi- I'm doing a conversion from an older database in which a memo field was > handled by storing one line per record and then displaying the related > records in order. I want to compress all of the lines into a single text > field with one record per memo entry. > > > So for instance, the old database looks like this: > > > memo_id | sequence | memo_text > --- > 666 | 1| The quick > 666 | 2| red fox > 666 | 3| jumped over > 666 | 4| the lazy brown dog > > > And my goal is to transform each group of lines into a single record that > looks like this: > > > memo_id | memo_text > -- > 666 | The quick red fox jumped over the lazy brown dog > > > Any thoughts on how to do this via sql? I could write a little program to > do it, but it seems like there must be a pure-SQL solution that I'm not > seeing. You can create a custom aggregate to do concatenation and group by memo_id. create or replace function text_cat(text, text) returns text called on null input language sql immutable as 'select case when $1 is null then $2 when $2 is null then $1 else $1 || '' '' || $2 end'; create aggregate textcat (basetype = text, sfunc = text_cat, stype = text); create table memos (memo_id int, sequence int, memo_text text); insert into memos values (666, 3, 'jumped over'); insert into memos values (666, 1, 'The quick'); insert into memos values (666, 4, 'the lazy brown dog'); insert into memos values (666, 2, 'red fox'); select memo_id, textcat(memo_text) from (select * from memos order by memo_id, sequence) as foo group by memo_id; The order is not really guaranteed, though if this is a one-shot thing, you may get away with turning off hashed aggregates. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "There was no reply" (Kernel Traffic) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] interesting SQL puzzle - concatenating column with itself.
Excellent! Thanks for providing both the idea and an example. I didn't get the idea right away, but the example made it clear. I'll try that on my table and report back on how it works out. Regards, -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 765.935.4283 Fax 765.962.9788 Ray Ontko & Co. - Software Consulting Services http://www.ontko.com Alvaro Herrera wrote: You can create a custom aggregate to do concatenation and group by memo_id. create or replace function text_cat(text, text) returns text called on null input language sql immutable as 'select case when $1 is null then $2 when $2 is null then $1 else $1 || '' '' || $2 end'; create aggregate textcat (basetype = text, sfunc = text_cat, stype = text); create table memos (memo_id int, sequence int, memo_text text); insert into memos values (666, 3, 'jumped over'); insert into memos values (666, 1, 'The quick'); insert into memos values (666, 4, 'the lazy brown dog'); insert into memos values (666, 2, 'red fox'); select memo_id, textcat(memo_text) from (select * from memos order by memo_id, sequence) as foo group by memo_id; The order is not really guaranteed, though if this is a one-shot thing, you may get away with turning off hashed aggregates. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
