[SQL] union in subselect?
Hi all, Can you use (or work around towards) a union in a subquery? I have :- Members mid int4; mnec bool; -- many NEC members Positions (one position = one holder - excludes NEC) posnchar(4); pholder int4; -- refers to mid Actions caction char(4) -- e.g. UPDT = update team cposn char(4) -- refers to posn clevle int4 -- increasing permission level select 'NEC' as posn from members where mid = 81 and mnec = true; posn -- NEC (1 row) select posn from positions where pholder = 81; posn -- MSEC ITA REG (3 rows) select posn from positions where pholder = 81 union select 'NEC' as posn from members where mnec = true and mid = 81; posn -- ITA MSEC NEC REG (4 rows) So far so good. select * from actions where cposn in (select posn from positions where pholder = 81); caction | cposn | clevel -+---+ ENQT| REG | 2 ENQM| REG | 2 AMET| REG | 2 AMET| ITA | 3 Still works - looking good select * from actions where cposn in (select posn from positions where pholder = 81 union select 'NEC' as posn from members where mnec = true and mid = 81); gives me: ERROR: parser: parse error at or near "union" -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Get name of columns in a table
María Elena Hernández wrote: > To get a list of columns, run "psql -E" and do a standard "\df foo" where foo is your table name. This will show you exactly how PG does it. I've found this very useful with all the backslash commands. HTH - Richard Huxton ---(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] union in subselect?
Gary Stainburn <[EMAIL PROTECTED]> writes: > select * from actions where cposn in (select posn from positions where pholder > = 81 union select 'NEC' as posn from members where mnec = true and mid = 81); > gives me: > ERROR: parser: parse error at or near "union" Update to PG 7.1. 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] Fuzzy matching?
Folks,
For many of my programs, it would be extremely useful to have some form
of "fuzzy matching" for VARCHAR fields. There are two kinds of fuzzy
matching for words that I know of:
1. Phonetic matching, which would be nice but will have to wait for
someone's $100,000 project;
2. Textual mathcing, which I will outline below.
The way textual fuzzy matching should work is as follows:
The developer supplies two VARCHARs to match and a number/percent of
character mis-match that is acceptable:
Fuzzy_match('Thornton','Tornton',1)
And the fuzzy_match should return True if the two phrases are no more
than that number of characters different. Thus, we should get:
fuzzy_match('Thornton','Tornton',1) = TRUE
fuzzy_match('Thornton','Torntin',1) = FALSE
fuzzy_match('Thornton','Torntin',2) = TRUE
Unfortunately, I cannot think of a way to make this happen in a function
without cycling through all the possible permutations of characters for
both words or doing some character-by-character comparison with
elaborate logic for placement. Either of these approaches would be very
slow, and completely unsuitable for column comparisons on large tables.
Can anyone suggest some shortcuts here? Perhaps using pl/perl or
something similar?
Grazie!
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Fuzzy matching?
> And the fuzzy_match should return True if the two phrases are no more
> than that number of characters different. Thus, we should get:
>
> fuzzy_match('Thornton','Tornton',1) = TRUE
> fuzzy_match('Thornton','Torntin',1) = FALSE
> fuzzy_match('Thornton','Torntin',2) = TRUE
>
> Unfortunately, I cannot think of a way to make this happen in a function
> without cycling through all the possible permutations of characters for
> both words or doing some character-by-character comparison with
> elaborate logic for placement. Either of these approaches would be very
> slow, and completely unsuitable for column comparisons on large tables.
>
> Can anyone suggest some shortcuts here? Perhaps using pl/perl or
> something similar?
Sounds like you want something along the lines of soundex or metaphone? I
don't see either function in PostgreSQL, but take a look at the PHP manual
to see examples: http://www.php.net/manual/en/function.soundex.php ,
http://www.php.net/manual/en/function.metaphone.php
I looked at the soundex function in the PHP source, and it looks like it
would be fairly easy to port to a Postgres C function. The algorithm itself
comes from Donald Knuth in "The Art Of Computer Programming, vol. 3: Sorting
And Searching", Addison-Wesley (1973), pp. 391-392.
HTH,
-- Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] Fuzzy matching?
Here's an off the cuff reply:
It sounds like fuzzy_match(str1,str2,num) is
really just a tokenizer-type operation. The number is exactly
one less than the potential number of string segments
that you are interested in. For example:
fuzzy_match('Thornton','Tornton',1) = TRUE
Because the two segements are 'T' and 'ornton'
And also:
fuzzy_match('Thornton','Torntin',2) = TRUE
Becuse the three segments are 'T', "ornt', and 'n'
So, it seems like you could try to build the tokens,
which would be probably more efficient than just trying
all permutations.
HTH
-Robby
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Josh Berkus
Sent: Tuesday, July 31, 2001 11:05 AM
To: [EMAIL PROTECTED]
Subject: [SQL] Fuzzy matching?
Folks,
For many of my programs, it would be extremely useful to have some form
of "fuzzy matching" for VARCHAR fields. There are two kinds of fuzzy
matching for words that I know of:
1. Phonetic matching, which would be nice but will have to wait for
someone's $100,000 project;
2. Textual mathcing, which I will outline below.
The way textual fuzzy matching should work is as follows:
The developer supplies two VARCHARs to match and a number/percent of
character mis-match that is acceptable:
Fuzzy_match('Thornton','Tornton',1)
And the fuzzy_match should return True if the two phrases are no more
than that number of characters different. Thus, we should get:
fuzzy_match('Thornton','Tornton',1) = TRUE
fuzzy_match('Thornton','Torntin',1) = FALSE
fuzzy_match('Thornton','Torntin',2) = TRUE
Unfortunately, I cannot think of a way to make this happen in a function
without cycling through all the possible permutations of characters for
both words or doing some character-by-character comparison with
elaborate logic for placement. Either of these approaches would be very
slow, and completely unsuitable for column comparisons on large tables.
Can anyone suggest some shortcuts here? Perhaps using pl/perl or
something similar?
Grazie!
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Fuzzy matching?
"Josh Berkus" <[EMAIL PROTECTED]> writes: > For many of my programs, it would be extremely useful to have some form > of "fuzzy matching" for VARCHAR fields. There are two kinds of fuzzy > matching for words that I know of: > 1. Phonetic matching, which would be nice but will have to wait for > someone's $100,000 project; Uh, have you looked at contrib/soundex? The Soundex code is kinda specialized but might be just what you want... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Fuzzy matching?
> > Sounds like you want something along the lines of soundex or metaphone? I > > don't see either function in PostgreSQL, but take a look at the PHP manual > > to see examples: http://www.php.net/manual/en/function.soundex.php , > > http://www.php.net/manual/en/function.metaphone.php > > > > See /contrib/soundex. Sorry, missed that -- I only looked in the Documentation :( I guess it's not there because it is a contrib. FWIW, both Oracle and MSSQL have a built-in soundex function. In any case, metaphone is reportedly more accurate (at least for English words) than soundex, and levenshtein offers an entirely different and interesting approach. Any interest in having all three of these in the backend? -- Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Fuzzy matching?
> > See /contrib/soundex. > > Sorry, missed that -- I only looked in the Documentation :( > I guess it's not there because it is a contrib. FWIW, both Oracle and MSSQL > have a built-in soundex function. > > In any case, metaphone is reportedly more accurate (at least for English > words) than soundex, and levenshtein offers an entirely different and > interesting approach. Any interest in having all three of these in the > backend? Sure. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Fuzzy matching?
> > > > Actually, this may even be closer to what you want: > > http://www.php.net/manual/en/function.levenshtein.php > > Hey, that's terrific! I didn't know that those programs existed > outside fo expensive proprietary software. > > Now, who can I talk into porting them (metaphone, levenstein) to > Postgres? Hey, GreatBridge folks? (this would be a significant value > enhancement for Postgres) > > -Josh I wouldn't mind doing it if the core team agrees. It will probably be a couple of weeks before I can get to it though -- not sure if that's soon enough to make it into 7.2. Should it be a contrib, or in the backend? -- Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Fuzzy matching?
> Sounds like you want something along the lines of soundex or metaphone? I > don't see either function in PostgreSQL, but take a look at the PHP manual > to see examples: http://www.php.net/manual/en/function.soundex.php , > http://www.php.net/manual/en/function.metaphone.php > See /contrib/soundex. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Fuzzy matching?
Joe, > In any case, metaphone is reportedly more accurate (at least for > English > words) than soundex, and levenshtein offers an entirely different and > interesting approach. Any interest in having all three of these in > the > backend? I'm quite interested, myself. How difficult is it for somebody that doesn't program C to attach a function from the Contrib directory? If it's not very difficult, then I'd recommend putting metaphone in /contrib, and levenstein in the backend. My reasoning is that levenstein is useful for all roman alphabets, but metaphone is not so useful for non-english versions of postgres. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Fuzzy matching?
Joe, > > Sounds like you want something along the lines of soundex or > metaphone? I > > don't see either function in PostgreSQL, but take a look at the PHP > manual > > to see examples: http://www.php.net/manual/en/function.soundex.php > , > > http://www.php.net/manual/en/function.metaphone.php > > > > I looked at the soundex function in the PHP source, and it looks > like it > > would be fairly easy to port to a Postgres C function. The > algorithm > itself > > comes from Donald Knuth in "The Art Of Computer Programming, vol. > 3: > Sorting > > And Searching", Addison-Wesley (1973), pp. 391-392. > > > > Actually, this may even be closer to what you want: > http://www.php.net/manual/en/function.levenshtein.php Hey, that's terrific! I didn't know that those programs existed outside fo expensive proprietary software. Now, who can I talk into porting them (metaphone, levenstein) to Postgres? Hey, GreatBridge folks? (this would be a significant value enhancement for Postgres) -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Fuzzy matching?
"Joe Conway" <[EMAIL PROTECTED]> writes: > In any case, metaphone is reportedly more accurate (at least for English > words) than soundex, and levenshtein offers an entirely different and > interesting approach. Any interest in having all three of these in the > backend? I'd certainly accept such functions as /contrib material, but probably would want to wait to see if they get used much before putting them in the standard backend ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] RE: Fuzzy matching?
With version 7.2 we will have pl/perlu (untrusted), which will allow use of the various Perl modules which do this sort of thing. > -Original Message- > From: Josh Berkus [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, July 31, 2001 1:16 PM > To: Joe Conway; Bruce Momjian > Cc: Josh Berkus; [EMAIL PROTECTED] > Subject: Re: Fuzzy matching? > > Joe, > > > In any case, metaphone is reportedly more accurate (at least for > > English > > words) than soundex, and levenshtein offers an entirely different and > > interesting approach. Any interest in having all three of these in > > the > > backend? > > I'm quite interested, myself. How difficult is it for somebody that > doesn't program C to attach a function from the Contrib directory? If > it's not very difficult, then I'd recommend putting metaphone in > /contrib, and levenstein in the backend. My reasoning is that > levenstein is useful for all roman alphabets, but metaphone is not so > useful for non-english versions of postgres. > > -Josh > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Fuzzy matching?
Tom, Joe, > Our usual practice with stuff of uncertain usefulness has been to > stick > it in contrib for awhile and see if anyone uses it. If there's > sufficient interest, we'll promote it to mainstream in a future > release. Makes sense to me. Go, Joe! Since I can't help with the porting of metaphone or levenstein (but will benefit immensely), I pledge to write a name-alike data checking PL/pgSQL function which I will post to Roberto's library for public consumption. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Fuzzy matching?
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I'm quite interested, myself. How difficult is it for somebody that > doesn't program C to attach a function from the Contrib directory? Run the install script. > If it's not very difficult, then I'd recommend putting metaphone in > /contrib, and levenstein in the backend. My reasoning is that > levenstein is useful for all roman alphabets, but metaphone is not so > useful for non-english versions of postgres. Our usual practice with stuff of uncertain usefulness has been to stick it in contrib for awhile and see if anyone uses it. If there's sufficient interest, we'll promote it to mainstream in a future release. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Fuzzy matching?
On Wed, 01 Aug 2001 05:44, Tom Lane wrote: > "Josh Berkus" <[EMAIL PROTECTED]> writes: > > For many of my programs, it would be extremely useful to have some form > > of "fuzzy matching" for VARCHAR fields. There are two kinds of fuzzy > > matching for words that I know of: > > > > 1. Phonetic matching, which would be nice but will have to wait for > > someone's $100,000 project; > > Uh, have you looked at contrib/soundex? The Soundex code is kinda > specialized but might be just what you want... Be aware that Soundex simply does not work at all well on names of Pacific origin. Samoan in particular is a total no no. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Fuzzy matching?
I posted this many moons ago to pgsql-hackers. 'Guess nobody noticed.
Tim
Josh Berkus wrote:
>Folks,
>
>For many of my programs, it would be extremely useful to have some form
>of "fuzzy matching" for VARCHAR fields. There are two kinds of fuzzy
>matching for words that I know of:
>
>1. Phonetic matching, which would be nice but will have to wait for
>someone's $100,000 project;
>
>2. Textual mathcing, which I will outline below.
>
>The way textual fuzzy matching should work is as follows:
>The developer supplies two VARCHARs to match and a number/percent of
>character mis-match that is acceptable:
>
>Fuzzy_match('Thornton','Tornton',1)
>
>And the fuzzy_match should return True if the two phrases are no more
>than that number of characters different. Thus, we should get:
>
>fuzzy_match('Thornton','Tornton',1) = TRUE
>fuzzy_match('Thornton','Torntin',1) = FALSE
>fuzzy_match('Thornton','Torntin',2) = TRUE
>
>Unfortunately, I cannot think of a way to make this happen in a function
>without cycling through all the possible permutations of characters for
>both words or doing some character-by-character comparison with
>elaborate logic for placement. Either of these approaches would be very
>slow, and completely unsuitable for column comparisons on large tables.
>
>Can anyone suggest some shortcuts here? Perhaps using pl/perl or
>something similar?
>
>Grazie!
>
>-Josh Berkus
>
>__AGLIO DATABASE SOLUTIONS___
> Josh Berkus
> Complete information technology [EMAIL PROTECTED]
> and data management solutions (415) 565-7293
> for law firms, small businessesfax 621-2533
>and non-profit organizations. San Francisco
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>---(end of broadcast)---
>TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
> Part 1.2
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> base64
>
>
>
> Part 1.3
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> base64
>
>
>
> Part 1.4
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> base64
>
>
>
> Part 1.5
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> binary
>
>
--
Timothy H. Keitt
Department of Ecology and Evolution
State University of New York at Stony Brook
Stony Brook, New York 11794 USA
Phone: 631-632-1101, FAX: 631-632-7626
http://life.bio.sunysb.edu/ee/keitt/
/* Copyright (c) 2000 Timothy H. Keitt */
/* Licence: GPL version 2 or higher (see http://www.gnu.org/) */
#include "postgres.h"
#define STATIC_SIZE 32
/* This must be changed if STATIC_SIZE is changed */
static int4 static_array[STATIC_SIZE][STATIC_SIZE] = {
{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31},
{1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{5, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{9, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{11, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{12, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{13, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{15, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
{16, 0, 0, 0, 0, 0, 0, 0, 0, 0
Re: [SQL] Fuzzy matching?
Oh and despite the copyright notice, I'm happy to put it in the public
domain, so feel free to incorporate into postgresql.
Tim
Timothy H. Keitt wrote:
> I posted this many moons ago to pgsql-hackers. 'Guess nobody noticed.
>
> Tim
>
> Josh Berkus wrote:
>
>> Folks,
>>
>> For many of my programs, it would be extremely useful to have some form
>> of "fuzzy matching" for VARCHAR fields. There are two kinds of fuzzy
>> matching for words that I know of:
>>
>> 1. Phonetic matching, which would be nice but will have to wait for
>> someone's $100,000 project;
>>
>> 2. Textual mathcing, which I will outline below.
>>
>> The way textual fuzzy matching should work is as follows:
>> The developer supplies two VARCHARs to match and a number/percent of
>> character mis-match that is acceptable:
>>
>> Fuzzy_match('Thornton','Tornton',1)
>>
>> And the fuzzy_match should return True if the two phrases are no more
>> than that number of characters different. Thus, we should get:
>>
>> fuzzy_match('Thornton','Tornton',1) = TRUE
>> fuzzy_match('Thornton','Torntin',1) = FALSE
>> fuzzy_match('Thornton','Torntin',2) = TRUE
>>
>> Unfortunately, I cannot think of a way to make this happen in a function
>> without cycling through all the possible permutations of characters for
>> both words or doing some character-by-character comparison with
>> elaborate logic for placement. Either of these approaches would be very
>> slow, and completely unsuitable for column comparisons on large tables.
>>
>> Can anyone suggest some shortcuts here? Perhaps using pl/perl or
>> something similar?
>>
>> Grazie!
>>
>> -Josh Berkus
>>
>> __AGLIO DATABASE SOLUTIONS___
>> Josh Berkus
>> Complete information technology [EMAIL PROTECTED]
>> and data management solutions (415) 565-7293
>> for law firms, small businessesfax 621-2533
>>and non-profit organizations. San Francisco
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> ---(end of broadcast)---
>> TIP 2: you can get off all lists at once with the unregister command
>>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>>
>> Part 1.2
>>
>> Content-Type:
>>
>> text/plain
>> Content-Encoding:
>>
>> base64
>>
>>
>>
>> Part 1.3
>>
>> Content-Type:
>>
>> text/plain
>> Content-Encoding:
>>
>> base64
>>
>>
>>
>> Part 1.4
>>
>> Content-Type:
>>
>> text/plain
>> Content-Encoding:
>>
>> base64
>>
>>
>>
>> Part 1.5
>>
>> Content-Type:
>>
>> text/plain
>> Content-Encoding:
>>
>> binary
>>
>>
>
>
>
>
>/* Copyright (c) 2000 Timothy H. Keitt */
>/* Licence: GPL version 2 or higher (see http://www.gnu.org/) */
>
>#include "postgres.h"
>
>#define STATIC_SIZE 32
>
>/* This must be changed if STATIC_SIZE is changed */
>static int4 static_array[STATIC_SIZE][STATIC_SIZE] = {
> {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
> 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31},
> {1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {5, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {9, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {11, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {12, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {13, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0},
> {14, 0, 0, 0, 0, 0, 0, 0, 0, 0
[SQL] Converting epoch to timestamp?
I searched the docs for function to convert epoch to timestamps but couldn't find any. Are there any? Thanks, -Roberto -- +| Roberto Mello - http://www.brasileiro.net |+ Computer Science, Utah State University - http://www.usu.edu USU Free Software & GNU/Linux Club - http://fslc.usu.edu Space Dynamics Lab, Developer http://www.sdl.usu.edu [<<] [>] [>>] [o] [||] [|>] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
