Re: [SQL] expressions operating on arrays
[EMAIL PROTECTED] writes: > 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 ? No, those are support functions for GIST indexes on intarrays. They're not useful to call directly. regards, tom lane ---(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] Table Sorting and Limit Question
Since the id is a sequence type it should be unique. In this case it also happens to be my primary key into this table which will also make it unique. So the query below would only return 1 row. The part I'm missing is the offset number of the limit. Is there any way to figure out which offset into the query would contain the id I'm interested in. Dawn Hollingsworth Principal Engineer AirDefense, Inc. On Thu, 2002-08-08 at 21:42, Ludwig Lim wrote: > > --- Dawn Hollingsworth <[EMAIL PROTECTED]> wrote: > > > > > > Currently we have a table with a sequence number( id > > ) as a primary key, > > a date field which is indexed and several other > > columns. The user > > interface allows the user to sort the data by date > > and limits the result > > set to 100 rows. > > > > The question is: > > The user interface needs the capability to sort the > > table by date but > > pull out the hundred row set that contains id > > say...542 for example. > > > > What would be the best way to do this taking into > > account this table is > > several hundred thousand rows? > > try also to index the id. > > Try : > Select * > from > where id= > order by date > limit > > In this case you is 100 since you want to > return at most 100 rows. > > hope that helps. > > ludwig. > > > __ > Do You Yahoo!? > HotJobs - Search Thousands of New Jobs > http://www.hotjobs.com > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] expressions operating on arrays
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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
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] How to update record in a specified order
Hi all, I want to update a field with a 'NEXTVAL', but I want the record updated in a specific order. Any simple way of doing this other than having to create a temp table? JLL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL syntax
Well It's Friday and I am still geting vacation messages from Bob Tom Lane wrote: > > Jean-Luc Lachance <[EMAIL PROTECTED]> writes: > > Can someone *please* temporarely remove > >"Bob Powell" <[EMAIL PROTECTED]> > > from the list so we do not get a vacation message for every message one > > posts. > > I complained to Marc about that a week or more ago, but I guess he > doesn't want to bounce Bob from the lists just for being incompetent > about configuring "vacation". I've set my own mailserver to deny > connections from hotchkiss.org ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(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])
Re: [SQL] How to update record in a specified order
JLL, > I want to update a field with a 'NEXTVAL', but I want the record updated > in a specific order. > Any simple way of doing this other than having to create a temp table? Please be more speciifc. What do you mean, "specified order"? -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] How to update record in a specified order
As in an order by clause... If it existed. Josh Berkus wrote: > > JLL, > > > I want to update a field with a 'NEXTVAL', but I want the record updated > > in a specific order. > > Any simple way of doing this other than having to create a temp table? > > Please be more speciifc. What do you mean, "specified order"? > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > ---(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 ---(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] How to update record in a specified order
On 9 Aug 2002 at 14:37, Josh Berkus wrote: > JLL, > > > I want to update a field with a 'NEXTVAL', but I want the record updated > > in a specific order. > > Any simple way of doing this other than having to create a temp table? > > Please be more speciifc. What do you mean, "specified order"? My reading of what is required: - sort a given result set according to some criteria - then do something like UPDATE table SET myfield = NEXTVAL - each row in the result set would get a value one more than the previous row -- Dan Langille I'm looking for a computer job: http://www.freebsddiary.org/dan_langille.php ---(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] retrieving all rows from a "tree" in one select - how ?
I'll be curious to see the responses to this. I myself deal with this same situation every day. Although we're currently using MySQL but moving it to postgres (which is why I'm on these lists..) > -- select a tree starting with node 1234 and all its descendants: > SELECT * FROM t WHERE id=1234 OR ANCESTOR(t.parent) IS 1234; I've seen some really weird solutions to this. I'm not sure if a subselect can do this or not. I doubt it. Since MySQL limits us greatly we resort to a lookup field for each record in the node. ie. (Forgive ASCII art please) 1 --> 2 --> 4 --> 5 --> 6 --> 8 --> 9 --> 3 --> 7 --> 10 The record for id=9 would have a field index='-1-2-6-8-x' When we want all records under node id=6 we just use: select * from t where index like "%-6-%"; We prefix with '-' for arbitrary level searches. We suffix with -x for an unknown (but good) reason. My memory is leaving me. > -- select the path from tree node 2345 to the root > SELECT * FROM t WHERE id=2345 OR DESCENTANT(t.parent) IS 2345; With our lookup/index field this is trivial. Unfortunately, it makes the application responsible for parsing and is probably not what you're after. Just my two cents. It works very well for us (make the lookup field an index btw) but their is probably a much better way in postgres. I don't remember if postgres allows regexes in the where clause (ie. rlike in mysql) but with that you can "find all nodes 3 or more leaves down from node 123" or even weirder stuff. We have trees with 60,000 nodes 30-40 levels deep. Queries on the tree take very little time at all. Adam Erickson ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to update record in a specified order
JLL,
So, you want to update a field with a NEXTVAL counter, but with the counter
ordered by another column?
If so, you will have to use a procedure. Ordering your UPDATEs is not part
of SQL -- it requires a procedural element. Here's a simple procedure (you
debug it):
CREATE PROCEDURE add_my_table_counter ()
RETURNS TEXT AS '
DECLARE v_rec RECORD;
BEGIN
WHILE v_rec IN SELECT * FROM my_table ORDER BY last_name LOOP
UPDATE my_table SET counter_field = NEXTVAL(''my_sequence'')
WHERE my_table.id = v_rec.id;
END LOOP;
RETURN ''Done updating.'';
END;'
LANGUAGE 'plpgsql';
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(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] retrieving all rows from a "tree" in one select - how ?
Guys, Check out Joe Celko's two chapters on tree structures in the 2nd edition of "SQL for Smarties". It's pretty comprehensive. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] retrieving all rows from a "tree" in one select - how ?
folk, have you looked at ltree ? http://www.sai.msu.su/~megera/postgres/gist/ltree/ Regards, Oleg On Fri, 9 Aug 2002, Adam Erickson wrote: > I'll be curious to see the responses to this. I myself deal with this same > situation every day. Although we're currently using MySQL but moving it to > postgres (which is why I'm on these lists..) > > > -- select a tree starting with node 1234 and all its descendants: > > SELECT * FROM t WHERE id=1234 OR ANCESTOR(t.parent) IS 1234; > > I've seen some really weird solutions to this. I'm not sure if a subselect > can do this or not. I doubt it. Since MySQL limits us greatly we resort to > a lookup field for each record in the node. > > ie. (Forgive ASCII art please) > > 1 --> 2 > --> 4 > --> 5 > --> 6 > --> 8 >--> 9 > --> 3 > --> 7 > --> 10 > > The record for id=9 would have a field index='-1-2-6-8-x' > > When we want all records under node id=6 we just use: > select * from t where index like "%-6-%"; > > We prefix with '-' for arbitrary level searches. We suffix with -x for an > unknown (but good) reason. My memory is leaving me. > > > -- select the path from tree node 2345 to the root > > SELECT * FROM t WHERE id=2345 OR DESCENTANT(t.parent) IS 2345; > > With our lookup/index field this is trivial. Unfortunately, it makes the > application responsible for parsing and is probably not what you're after. > > Just my two cents. It works very well for us (make the lookup field an > index btw) but their is probably a much better way in postgres. I don't > remember if postgres allows regexes in the where clause (ie. rlike in mysql) > but with that you can "find all nodes 3 or more leaves down from node 123" > or even weirder stuff. We have trees with 60,000 nodes 30-40 levels deep. > Queries on the tree take very little time at all. > > Adam Erickson > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] getting ILIKE or ~* to use indexes....
Hi folks, can anyone tell me or point me to the right thread. I want my query to use indexes for company name searches but its not happening unless is use '=' which does not server the purpose. eg tradein_clients=# explain SELECT co_name FROM unified_data where co_name ilike '%rajesh%' ; NOTICE: QUERY PLAN: Seq Scan on unified_data (cost=0.00..19293.00 rows=1 width=25) EXPLAIN tradein_clients=# explain SELECT co_name FROM unified_data where co_name = 'rajesh' ; NOTICE: QUERY PLAN: Index Scan using unified_data_co_name_key on unified_data (cost=0.00..6.26 rows=1 width=25) EXPLAIN tradein_clients=# Regards mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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
