Thread: How is the right query for this condition ?
Dear All Suppose I created a database with single table like this : ------start---------- CREATE DATABASE bino; CREATE TABLE myrecords(record text); ------end------------ and I fill myrecords with this : ------start---------- COPY myrecords (record) FROM stdin; 1 12 123 1234 \. ------end------------ In my bash script, I have variable called 'vseek', that will be use for query parameter. How to query the table , for (i.e): a. If vseek = '127' , I want the result is ==> '12' b. if vseek = '123987' , I want the result is ==> '123' c. if vseek = '14789' , I want the result is ==> '1' Kindly please give me any enlightment Sincerely -bino-
2009/11/23 Bino Oetomo <bino@indoakses-online.com>:
> Dear All
>
> Suppose I created a database with single table like this :
> ------start----------
> CREATE DATABASE bino;
> CREATE TABLE myrecords(record text);
> ------end------------
>
> and I fill myrecords with this :
> ------start----------
> COPY myrecords (record) FROM stdin;
> 1
> 12
> 123
> 1234
> \.
> ------end------------
>
> In my bash script, I have variable called 'vseek', that will be use for
> query parameter.
> How to query the table , for (i.e):
>
> a. If vseek = '127' , I want the result is ==> '12'
> b. if vseek = '123987' , I want the result is ==> '123'
> c. if vseek = '14789' , I want the result is ==> '1'
>
> Kindly please give me any enlightment
You can use a plpgsql to do that e.g.
create or replace function getMatchingRecord(vseek text)
returns text as $$
declare
str text;
len integer;
ret text;
begin
len := char_length(vseek);
loop
exit when len = 0;
str := substring(vseek from 1 for len);
select "record" into ret from myrecords where "record" = str;
if found then
return ret;
end if;
len := len - 1;
end loop;
end;
$$ language plpgsql;
Then call it as so:
KarooDB=> select getMatchingRecord('127');
getmatchingrecord
-------------------
12
(1 row)
KarooDB=> select getMatchingRecord('123987');
getmatchingrecord
-------------------
123
(1 row)
> Sincerely
> -bino-
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/
Dear Sir Brian Modra wrote: > You can use a plpgsql to do that e.g. > > create or replace function getMatchingRecord(vseek text) > returns text as $$ > declare > str text; > len integer; > ret text; > ... I Just try your solution , and it's work like a charm Thankyou for your enlightment Sincerely -bino-
In article <5a9699850911222009j272071fbi1dd0c40dfdf62311@mail.gmail.com>, Brian Modra <epailty@googlemail.com> writes: > 2009/11/23 Bino Oetomo <bino@indoakses-online.com>: >> Dear All >> >> Suppose I created a database with single table like this : >> ------start---------- >> CREATE DATABASE bino; >> CREATE TABLE myrecords(record text); >> ------end------------ >> >> and I fill myrecords with this : >> ------start---------- >> COPY myrecords (record) FROM stdin; >> 1 >> 12 >> 123 >> 1234 >> \. >> ------end------------ >> >> In my bash script, I have variable called 'vseek', that will be use for >> query parameter. >> How to query the table , for (i.e): >> >> a. If vseek = '127' , I want the result is ==> '12' >> b. if vseek = '123987' , I want the result is ==> '123' >> c. if vseek = '14789' , I want the result is ==> '1' >> >> Kindly please give me any enlightment > You can use a plpgsql to do that e.g. > create or replace function getMatchingRecord(vseek text) > ... For larger tables where an index search would be useful, check out pgfoundry.org/projects/prefix: CREATE TABLE myrecords ( record prefix_range NOT NULL, PRIMARY KEY (record) ); COPY myrecords (record) FROM stdin; 1 12 123 1234 \. SELECT id, record FROM myrecords WHERE record @> '127' ORDER BY length(record::text) DESC LIMIT 1; SELECT id, record FROM myrecords WHERE record @> '123987' ORDER BY length(record::text) DESC LIMIT 1; SELECT id, record FROM myrecords WHERE record @> '14789' ORDER BY length(record::text) DESC LIMIT 1;
Harald Fuchs wrote: > > > For larger tables where an index search would be useful, check out > pgfoundry.org/projects/prefix: > > ... > Wow ... yet another enlightment Thankyou, I realy appreciate Sincerely -bino-
Dear All Harald Fuchs wrote: > For larger tables where an index search would be useful, check out > pgfoundry.org/projects/prefix: > > > CREATE TABLE myrecords ( > record prefix_range NOT NULL, > PRIMARY KEY (record) > ); > > COPY myrecords (record) FROM stdin; > 1 > 12 > 123 > 1234 > \. > > I downloaded pgfoundry's prefix, postgresql-8.3-prefix_1.0.0-1_i386.deb I install it using dpkg , and run the prefix.sql Create database .. named 'prefbino', and CREATE TABLE myrecords ( record prefix_range NOT NULL, PRIMARY KEY (record) ); Looks good, next I try to create some records, But I got this error : -----------------------START------------------ prefbino=# COPY myrecords (record) FROM stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1 >> 12 >> 123 >> 1234 >> \. ERROR: duplicate key value violates unique constraint "myrecords_pkey" CONTEXT: COPY myrecords, line 2: "12" -----------------------STOP------------------ Kindly please give me further enlightment Sincerely -bino-
In article <4B0BBC8E.6010803@indoakses-online.com>, Bino Oetomo <bino@indoakses-online.com> writes: > I downloaded pgfoundry's prefix, postgresql-8.3-prefix_1.0.0-1_i386.deb > I install it using dpkg , and run the prefix.sql > Create database .. named 'prefbino', and > CREATE TABLE myrecords ( > record prefix_range NOT NULL, > PRIMARY KEY (record) > ); > Looks good, next > I try to create some records, But I got this error : > -----------------------START------------------ > prefbino=# COPY myrecords (record) FROM stdin; > Enter data to be copied followed by a newline. > End with a backslash and a period on a line by itself. >>> 1 >>> 12 >>> 123 >>> 1234 >>> \. > ERROR: duplicate key value violates unique constraint "myrecords_pkey" > CONTEXT: COPY myrecords, line 2: "12" > -----------------------STOP------------------ > Kindly please give me further enlightment At least in prefix 1.0.0 unique indexes seem to be broken. Just drop the primary key and add a separate index: CREATE INDEX myrecords_record_ix ON myrecords USING gist (record);
Dear Harald Harald Fuchs wrote: > At least in prefix 1.0.0 unique indexes seem to be broken. Just drop > the primary key and add a separate index: > > CREATE INDEX myrecords_record_ix ON myrecords USING gist (record); > > > Yup .. it works now. Thankyou for your enlightment Sincerely -bino-
Hi,
Bino Oetomo <bino@indoakses-online.com> writes:
> ERROR: duplicate key value violates unique constraint "myrecords_pkey"
> CONTEXT: COPY myrecords, line 2: "12"
I think I should add the following code comment to the documentation, if
not already done:
/*
* We invent a prefix_range ordering for convenience, but that's
* dangerous. Use the BTree opclass at your own risk.
*
* On the other hand, when your routing table does contain pretty static
* data and you test it carefully or know it will fit into the ordering
* simplification, you're good to go.
*
* Baring bug, the constraint is to have non-overlapping data.
*/
You have to remember that '12'::prefix_range could be spelled as the
regular expression '12.*'. So that '1'::prefix_range contains '12'.
The BTree opclass is not made to resist to overlapping data. Maybe in
this case though we could say that 12 contains less elements than 1 so
it's less than 1. Here's a test to redefine the pr_cmp() operator in
term of that, as a patch against current CVS (which is 1.0.0).
Can you test with this version and maybe better data set?
Note that as said earlier the indexing you need to speed up queries is
the GiST one, but it could be you want the PK constraint noneless.
prefix=# select prefix_range_cmp('1', '12');
prefix_range_cmp
------------------
1 -- it is 0 without the patch.
(1 row)
This means '1'::prefix_range > '12'::prefix_range and you're now able to
create your PRIMARY KEY on the example data. It's still not very useful
for the general case, but could be argued as better...
Of course changing that will discard any btree containing a prefix_range
column, so that's going to be 1.1.0 if workable.
Regards,
--
dim
PS: no worry about the operators themselves, they are defined atop cmp:
static inline
bool pr_lt(prefix_range *a, prefix_range *b, bool eqval) {
int cmp = pr_cmp(a, b);
return eqval ? cmp <= 0 : cmp < 0;
}
static inline
bool pr_gt(prefix_range *a, prefix_range *b, bool eqval) {
int cmp = pr_cmp(a, b);
return eqval ? cmp >= 0 : cmp > 0;
}
? README.html
? TESTS.html
? prefix.sql
? prefixes.check.pl
? todo
? varlena.gavin.snippet.c
? debian/files
? debian/postgresql-8.3-prefix
? debian/postgresql-8.3-prefix.debhelper.log
? debian/postgresql-8.3-prefix.substvars
? debian/postgresql-8.4-prefix
? debian/postgresql-8.4-prefix.debhelper.log
? debian/postgresql-8.4-prefix.substvars
Index: prefix.c
===================================================================
RCS file: /cvsroot/prefix/prefix/prefix.c,v
retrieving revision 1.54
diff -p -u -r1.54 prefix.c
--- prefix.c 6 Oct 2009 09:55:32 -0000 1.54
+++ prefix.c 25 Nov 2009 16:10:47 -0000
@@ -520,7 +520,11 @@ int pr_cmp(prefix_range *a, prefix_range
cmp = memcmp(p, q, mlen);
if( cmp == 0 )
- return (a->first == b->first) ? (a->last - b->last) : (a->first - b->first);
+ /*
+ * we are comparing e.g. '1' and '12' (the shorter contains the
+ * smaller), so let's pretend '12' < '1' as it contains less elements.
+ */
+ return (alen == mlen) ? 1 : -1;
return cmp;
}
In article <87tywid19x.fsf@hi-media-techno.com>,
Dimitri Fontaine <dfontaine@hi-media.com> writes:
> The BTree opclass is not made to resist to overlapping data. Maybe in
> this case though we could say that 12 contains less elements than 1 so
> it's less than 1. Here's a test to redefine the pr_cmp() operator in
> term of that, as a patch against current CVS (which is 1.0.0).
> Can you test with this version and maybe better data set?
Looks good.
> Note that as said earlier the indexing you need to speed up queries is
> the GiST one, but it could be you want the PK constraint noneless.
Indeed - I think it's a good thing to be able to prevent something
like
INSERT INTO myrecords (record) VALUES ('12'), ('12');
Oh, here's another gripe: how about renaming README.txt to README.prefix
and removing TESTS.* from "make install"?
Hi, Le 23 nov. 2009 à 17:04, Harald Fuchs a écrit : > SELECT id, record > FROM myrecords > WHERE record @> '127' > ORDER BY length(record::text) DESC > LIMIT 1; In prefix 1.0.0 you can say ORDER BY length(record) DESC directly... -- Dimitri Fontaine PostgreSQL DBA, Architecte
Hi, Dimitri Fontaine <dfontaine@hi-media.com> writes: > Can you test with this version and maybe better data set? [...] > Of course changing that will discard any btree containing a prefix_range > column, so that's going to be 1.1.0 if workable. http://github.com/dimitri/prefix http://github.com/dimitri/prefix/commit/e1bcb8e28305c9257655548a70c9fc05a21e9a1e It's being uploaded to debian now. I'm going to release 1.1.0 source tarball (only) on pgfoundry. Regards, -- dim