[SQL] expressions operating on arrays
Hi,
I would like to say:
create table test1 (array1 int4[]);
insert into test1 values ('{123,234,345}');
insert into test1 values ('{456,567,678}');
now what I don't know how to do:
-- consider contents of array:
select * from test1 where array1 CONTAINS 567;
-- APPEND '789' to array in second row:
update test1 set array1=(select array1 from test1 where array1 CONTAINS
'567' UNION select '789');
How do I go about expressing and operating on the contents of an array ?
Thanks,
John
--
-- Gospel of Jesus is the saving power of God for all who believe --
## To some, nothing is impossible. ##
http://Honza.Vicherek.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] expressions operating on arrays
thanks for the pointer, I'm now able to use the operators in expressions!
w.r.t. modifying the array contents: I looked through
/usr/lib/pgsql/contrib/intarray/_int.sql.in , but it didn't make me any
smarter. Are the "..._union" and "..._picksplit" functions supposed to add
into and remove elements from the arrays ? How would one update a row,
then, to add an element into one array and remove an element from another?
create table t(id int4[], txt text[]);
update t set id=g_int_union(t.id, '{555}') where t.id *= 444; -- ??
update t set text=g_int_picksplit(t.text, '{"removeme"}') where t.text *= "removeme";
-- ??
thx,
John
On Fri, 9 Aug 2002, Oleg Bartunov wrote:
> Look at contrib/intarray. You'll get an index access as a bonus
>
> Oleg
On Fri, 9 Aug 2002, Achilleus Mantzios wrote:
>
> look at /usr/local/src/postgresql-7.2.1/contrib/intarray
>
>
> On Fri, 9 Aug 2002 [EMAIL PROTECTED] wrote:
>
> >
> >
> > Hi,
> >
> > I would like to say:
> >
> > create table test1 (array1 int4[]);
> > insert into test1 values ('{123,234,345}');
> > insert into test1 values ('{456,567,678}');
> >
> > now what I don't know how to do:
> >
> > -- consider contents of array:
> > select * from test1 where array1 CONTAINS 567;
> >
> > -- APPEND '789' to array in second row:
> > update test1 set array1=(select array1 from test1 where array1 CONTAINS
> > '567' UNION select '789');
> >
> >
> > How do I go about expressing and operating on the contents of an array ?
> >
> >
> > Thanks,
> >
> >John
--
-- Gospel of Jesus is the saving power of God for all who believe --
## To some, nothing is impossible. ##
http://Honza.Vicherek.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] expressions operating on arrays
Great !
I'll try patching it.
Thanks Oleg & Teodor for doing all this great work !
Long live PostgreSQL !
see yea,
John
On Fri, 9 Aug 2002, Oleg Bartunov wrote:
> Hmm,
>
> you dont' need to use GiST supporting functions !
> We've posted a patch to current CVS, it has everything you need.
> Please, check http://www.sai.msu.su/~megera/postgres/gist/code/7.3/README.intarray
>
> It looks like that patch should works with 7.2 also.
>
> Oleg
> On Fri, 9 Aug 2002 [EMAIL PROTECTED] wrote:
>
> >
> > thanks for the pointer, I'm now able to use the operators in expressions!
> >
> > w.r.t. modifying the array contents: I looked through
> > /usr/lib/pgsql/contrib/intarray/_int.sql.in , but it didn't make me any
> > smarter. Are the "..._union" and "..._picksplit" functions supposed to add
> > into and remove elements from the arrays ? How would one update a row,
> > then, to add an element into one array and remove an element from another?
> >
> > create table t(id int4[], txt text[]);
> > update t set id=g_int_union(t.id, '{555}') where t.id *= 444; -- ??
> > update t set text=g_int_picksplit(t.text, '{"removeme"}') where t.text *=
>"removeme"; -- ??
> >
> > thx,
> >
> > John
> >
> > On Fri, 9 Aug 2002, Oleg Bartunov wrote:
> >
> > > Look at contrib/intarray. You'll get an index access as a bonus
> > >
> > > Oleg
> >
> > On Fri, 9 Aug 2002, Achilleus Mantzios wrote:
> >
> > >
> > > look at /usr/local/src/postgresql-7.2.1/contrib/intarray
> > >
> > >
> >
> > > On Fri, 9 Aug 2002 [EMAIL PROTECTED] wrote:
> > >
> > > >
> > > >
> > > > Hi,
> > > >
> > > > I would like to say:
> > > >
> > > > create table test1 (array1 int4[]);
> > > > insert into test1 values ('{123,234,345}');
> > > > insert into test1 values ('{456,567,678}');
> > > >
> > > > now what I don't know how to do:
> > > >
> > > > -- consider contents of array:
> > > > select * from test1 where array1 CONTAINS 567;
> > > >
> > > > -- APPEND '789' to array in second row:
> > > > update test1 set array1=(select array1 from test1 where array1 CONTAINS
> > > > '567' UNION select '789');
> > > >
> > > >
> > > > How do I go about expressing and operating on the contents of an array ?
> > > >
> > > >
> > > > Thanks,
> > > >
> > > >John
> >
> >
>
> Regards,
> Oleg
> _
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
--
-- Gospel of Jesus is the saving power of God for all who believe --
## To some, nothing is impossible. ##
http://Honza.Vicherek.com/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] retrieving all rows from a "tree" in one select - how ?
Hi,
I realize that a relational database may not be ideal for storing (and
retrieving) tree-like strucutres, but it looks like you guys are doing
with PostgreSQL the impossible anyway.
Having table t of all nodes:
CREATE SEQUENCE nodeIDseq START 1;
CREATE TABLE t(
id int PRIMARY KEY DEFAULT NEXTVAL('nodeIDseq'),
parent int REFERENCES t,
mydata int4
);
INSERT INTO t VALUES (0,0);
I was wondering whether there is a known (and perhaps working) way to do
things like:
-- select a tree starting with node 1234 and all its descendants:
SELECT * FROM t WHERE id=1234 OR ANCESTOR(t.parent) IS 1234;
and
-- select the path from tree node 2345 to the root
SELECT * FROM t WHERE id=2345 OR DESCENTANT(t.parent) IS 2345;
(I've seen some terse soutions at
http://www.brasileiro.net/postgres/cookbook/view-recipes.adp?section_id=2&format=long
but they don't seem to be complete.)
(Also I've looket at ltrees from GiST, but "ltree" seems to require that
the ID attribute contains all ancestors.)
Thanks,
John
--
-- Gospel of Jesus is the saving power of God for all who believe --
## To some, nothing is impossible. ##
http://Honza.Vicherek.com/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] slowing down too fast - why ?
Hi, I must be doing something silly. I have a 900MHz, 384MB RAM, and this thing is slow. (Postgresql-7.1.2). And growing exponencially slower. SQL: http://John.Vicherek.com/slow/schema.sql (save to /tmp/schema.sql) am.dat: http://John.Vicherek.com/slow/am.dat (save to /tmp/am.dat ) perl: http://John.Vicherek.com/slow/rpm2filerian.pl (save to /tmp/rpm2filerian.pl) when I do : createdb filerian psql -d filerian -f /tmp/schema.sql echo 'create table times (the_moment datetime, the_number int4);' | psql -d filerian cd /tmp/mdk/8.2/i586/Mandrake/RPMS # lots of RPMs here while sleep 10 ; do echo 'insert into times values( now(), count(file.id));' | psql -d filerian ; done 2>&1 >/dev/null & for i in *.rpm ; do echo $i ; perl /tmp/rpm2filerian.pl 0 $i ; done Why are the times so bad ? Why is it slowing so fast ? Am I missing any useful indeces ? This shows the slowage: select the_number,min(the_moment) from times group by the_number; PS: if you look in the perl code for "exec", immediatelly above will you find the query it is doing. Thanx, John -- -- Gospel of Jesus is the saving power of God for all who believe -- ## To some, nothing is impossible. ## http://Honza.Vicherek.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] slowing down too fast - why ?
and I forgot to mention that my stats are available at: http://John.Vicherek.com/slow/times.query.txt John On Sun, 11 Aug 2002 [EMAIL PROTECTED] wrote: > > > Hi, > > I must be doing something silly. I have a 900MHz, 384MB RAM, and > this thing is slow. (Postgresql-7.1.2). > > And growing exponencially slower. > > SQL: http://John.Vicherek.com/slow/schema.sql (save to /tmp/schema.sql) > am.dat: http://John.Vicherek.com/slow/am.dat (save to /tmp/am.dat ) > perl: http://John.Vicherek.com/slow/rpm2filerian.pl (save to /tmp/rpm2filerian.pl) > > when I do : > > createdb filerian > psql -d filerian -f /tmp/schema.sql > echo 'create table times (the_moment datetime, the_number int4);' | psql -d filerian > cd /tmp/mdk/8.2/i586/Mandrake/RPMS # lots of RPMs here > while sleep 10 ; do echo 'insert into times values( now(), count(file.id));' | psql >-d filerian ; done 2>&1 >/dev/null & > for i in *.rpm ; do echo $i ; perl /tmp/rpm2filerian.pl 0 $i ; done > > > Why are the times so bad ? Why is it slowing so fast ? > > Am I missing any useful indeces ? > > This shows the slowage: > select the_number,min(the_moment) from times group by the_number; > > PS: if you look in the perl code for "exec", immediatelly above will you > find the query it is doing. > >Thanx, > > John > > > > > -- -- Gospel of Jesus is the saving power of God for all who believe -- ## To some, nothing is impossible. ## http://Honza.Vicherek.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] slowing down too fast - why ?
I've even launched the backend with "-F" and removed BEGIN/COMMIT and LOCK TABLE and FOR UPDATE, but I still get slow response. only when count(*) from file is 16000, I get about 2-3 rows / second on average. When count(*) from file was 100, I get about 20-30 rows / second. Help ! Thanx, John On Sun, 11 Aug 2002 [EMAIL PROTECTED] wrote: > > and I forgot to mention that my stats are available at: > http://John.Vicherek.com/slow/times.query.txt > > John > > On Sun, 11 Aug 2002 [EMAIL PROTECTED] wrote: > > > > > > > Hi, > > > > I must be doing something silly. I have a 900MHz, 384MB RAM, and > > this thing is slow. (Postgresql-7.1.2). > > > > And growing exponencially slower. > > > > SQL: http://John.Vicherek.com/slow/schema.sql (save to /tmp/schema.sql) > > am.dat: http://John.Vicherek.com/slow/am.dat (save to /tmp/am.dat ) > > perl: http://John.Vicherek.com/slow/rpm2filerian.pl (save to /tmp/rpm2filerian.pl) > > > > when I do : > > > > createdb filerian > > psql -d filerian -f /tmp/schema.sql > > echo 'create table times (the_moment datetime, the_number int4);' | psql -d >filerian > > cd /tmp/mdk/8.2/i586/Mandrake/RPMS # lots of RPMs here > > while sleep 10 ; do echo 'insert into times values( now(), count(file.id));' | >psql -d filerian ; done 2>&1 >/dev/null & > > for i in *.rpm ; do echo $i ; perl /tmp/rpm2filerian.pl 0 $i ; done > > > > > > Why are the times so bad ? Why is it slowing so fast ? > > > > Am I missing any useful indeces ? > > > > This shows the slowage: > > select the_number,min(the_moment) from times group by the_number; > > > > PS: if you look in the perl code for "exec", immediatelly above will you > > find the query it is doing. > > > >Thanx, > > > > John > > > > > > > > > > > > -- -- Gospel of Jesus is the saving power of God for all who believe -- ## To some, nothing is impossible. ## http://Honza.Vicherek.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] "reverse()" on strings
Hi, CREATE TABLE file (name varchar(255)); I have a couple of milion filenames. I need to build index based on extensions. A couple of possibilities come to mind: CREATE INDEX extension_idx ON file (reverse(name)); -- but I didn't find a function called "reverse" CREATE INDEX extension_idx ON file (regex_match( '.*(\.[^\.]*)$' ); -- but I didn't find a function called "regex_match" which would return string matched in brackets () Any ideas ? Help ? Hints ? Thanks in advance ! John PS: if there is reverse on strings -- where could I find "reverse()" on arrays ? - thx -- -- Gospel of Jesus is the saving power of God for all who believe -- ## To some, nothing is impossible. ## ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "reverse()" on strings
Jeff & Josh, thanks for showing me a solution ! John PS: just curious: is there anything I can do to affect effectiveness of the cache, when ISCACHABLE is used ? (I.e. size / expiring algorithm, or order of inputs - E.g. when building the index, is there anything that would order the inputs first, to maximize cache hit/miss ratio, such as "CREATE INDEX extension_idx ON file (reverse(name)) ORDER BY name" ? On Mon, 26 Aug 2002, Josh Berkus wrote: > Jeff, h012, > > > CREATE FUNCTION fn_strrev(text) returns text as ' > > return reverse($_[0]) > > ' language 'plperl' with (iscachable); > > If you make that "WITH (ISCACHABLE, ISSTRICT)" the index will be faster to > update on columns which contain large numbers of NULLs. "ISSTRICT" refers to > the fact that if the function receives a NULL, it will output a NULL, and > thus saves the parser the time running NULLs through the function. > > Also, remember to use this index, you'll have to call the exact same function > in your queries. > > -- -- Gospel of Jesus is the saving power of God for all who believe -- ## To some, nothing is impossible. ## http://Honza.Vicherek.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
