[SQL] simple web search
Hello, I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. What is the most elegant way to build a single query to match search words with multiple columns? Thanks, ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] simple web search
> I'm considering implementing a search box on my review web site > http://lesculturelles.net and am looking for a simple way to match > entered words against several columns on related tables: > show.show_name, story.title, person.firtname, person.lastname, etc. one solution would be a view: create view search_v as select 'show'::name as tab_nm, show_id as tab_pk, 'Show Name' as description, show_name as search from show union select 'story'::name, story_id, 'Story Title', title from story union ... your query would be select * from search_v where '$string' ilike search this would return a list the user could use to drill down further. many ways to skin this cat. It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] simple web search
On Fri, Feb 23, 2007 at 10:01:22AM -0800, chester c young wrote: > > I'm considering implementing a search box on my review web site > > http://lesculturelles.net and am looking for a simple way to match > > entered words against several columns on related tables: > > show.show_name, story.title, person.firtname, person.lastname, etc. > > one solution would be a view: > > create view search_v as select > 'show'::name as tab_nm, > show_id as tab_pk, > 'Show Name' as description, > show_name as search > from show > union select > 'story'::name, > story_id, > 'Story Title', > title > from story > union ... > > your query would be > select * from search_v where '$string' ilike search > > this would return a list the user could use to drill down further. Thanks, this looks promising. The union and view ideas are indeed inspiring. What is that ::name cast for? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] simple web search
> > create view search_v as select > > 'show'::name as tab_nm, > > show_id as tab_pk, > > 'Show Name' as description, > > show_name as search > > from show > > union select > > 'story'::name, > > story_id, > > 'Story Title', > > title > > from story > > union ... > > > What is that ::name cast for? it's not needed here - sorry. name is the data type pg uses for table names &tc. it's frequently a good idea to cast to name when when messing around in the data dictionary. TV dinner still cooling? Check out "Tonight's Picks" on Yahoo! TV. http://tv.yahoo.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] simple web search
Hello Louis-David, On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote: > I'm considering implementing a search box on my review web site > http://lesculturelles.net and am looking for a simple way to match > entered words against several columns on related tables: show.show_name, > story.title, person.firtname, person.lastname, etc. > > What is the most elegant way to build a single query to match search > words with multiple columns? You may want to take a look at contrib/tsearch2. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] simple web search
I think contrib/tsearch2 http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ is what you need. Oleg On Fri, 23 Feb 2007, chester c young wrote: I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. one solution would be a view: create view search_v as select 'show'::name as tab_nm, show_id as tab_pk, 'Show Name' as description, show_name as search from show union select 'story'::name, story_id, 'Story Title', title from story union ... your query would be select * from search_v where '$string' ilike search this would return a list the user could use to drill down further. many ways to skin this cat. It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] simple web search
On Fri, Feb 23, 2007 at 01:31:14PM -0500, Joe wrote: > Hello Louis-David, > > On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote: > > I'm considering implementing a search box on my review web site > > http://lesculturelles.net and am looking for a simple way to match > > entered words against several columns on related tables: show.show_name, > > story.title, person.firtname, person.lastname, etc. > > > > What is the most elegant way to build a single query to match search > > words with multiple columns? > > You may want to take a look at contrib/tsearch2. Thanks Joe, I initially wanted to avoid dipping my toe into tsearch2 but it might be what I need after all :) ---(end of broadcast)--- TIP 1: 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] simple web search
On Fri, 23 Feb 2007, Louis-David Mitterrand wrote: On Fri, Feb 23, 2007 at 01:31:14PM -0500, Joe wrote: Hello Louis-David, On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote: I'm considering implementing a search box on my review web site http://lesculturelles.net and am looking for a simple way to match entered words against several columns on related tables: show.show_name, story.title, person.firtname, person.lastname, etc. What is the most elegant way to build a single query to match search words with multiple columns? You may want to take a look at contrib/tsearch2. Thanks Joe, I initially wanted to avoid dipping my toe into tsearch2 but it might be what I need after all :) Don't be afraid, it's not so difficult to start. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] how do I to generate a sequence Range or Set of integer constants
dear SQL friends, What I want to do might be done differantly. Right now I can't think of another solution other than a select statement I would like to create a sequence range of integer constants. Join this sequence against a ID Range in a database and look for missing Id's. Another application for this would be to simply populate a database with say 1000.. Records Now: Is there a syntax that allows for the following. create table XX (id int); insert into XX (select xx from "1 to 1000" of integers) or... select IntSeq.MissingValues, x.UniqIntId,x.A,x.B,x.C, from MyDataTable x left outer join ( select MissingValues from "1 to 1000" of integers ) IntSeq on MissingValues=x.UniqIntId I'm hoping that someone has done this and might be able to point to some function or methode to do this Thanks, Stefan Becker -- email: [EMAIL PROTECTED] tel : +49 (0)6232-497631 http://www.yukonho.de ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] selecting random row values in postgres
Hi all, I'm trying to write a query to select random values from a set of 'GROUP BY' see the scenario below to understand the problem here (the actual problem cannot be discussed here so i'm taking an example scenario) Assume there is a table id | name | year_of_birth query: I want to select for each year_of_birth a random name. --> so i do a group by year_of_birth, now i have a set of names, is there any function to select just one name from these set of names. The current approach i'm using to solve this problem is 1) getting these names in a single string using a custom function 'group_concat' 2) Convert the single string into an array 3) use postgresql random function to generate a random number 4) us the random number to select a element from the array previously created. The solution is there but it's kinda hack, is there any other better way of solving this problem. Thanks, Sumeet
Re: [SQL] how do I to generate a sequence Range or Set of integer constants
am Fri, dem 23.02.2007, um 19:25:35 +0100 mailte Stefan Becker folgendes: > Now: Is there a syntax that allows for the following. > > create table XX (id int); > insert into XX (select xx from "1 to 1000" of integers) Perhaps you are searching for generate_series(): test=*# select generate_series(1,10); generate_series - 1 2 3 4 5 6 7 8 9 10 (10 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] how do I to generate a sequence Range or Set of integer constants
On Fri, 2007-02-23 at 12:25, Stefan Becker wrote: > dear SQL friends, > > What I want to do might be done differantly. Right now I can't > think of another solution other than a select statement > > I would like to create a sequence range of integer constants. Join > this sequence against a ID Range in a database and look for missing > Id's. > > Another application for this would be to simply populate a database with > say 1000.. Records > > Now: Is there a syntax that allows for the following. > > create table XX (id int); > insert into XX (select xx from "1 to 1000" of integers) > > or... > > select IntSeq.MissingValues, x.UniqIntId,x.A,x.B,x.C, > from MyDataTable x > left outer join > ( > select MissingValues from "1 to 1000" of integers > ) IntSeq on MissingValues=x.UniqIntId select * from generate_series(1,1000); ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how do I to generate a sequence Range or Set of integer constants
On Fri, 2007-02-23 at 19:25 +0100, Stefan Becker wrote: > dear SQL friends, > > What I want to do might be done differantly. Right now I can't > think of another solution other than a select statement > > I would like to create a sequence range of integer constants. Join > this sequence against a ID Range in a database and look for missing > Id's. > > Another application for this would be to simply populate a database with > say 1000.. Records > > Now: Is there a syntax that allows for the following. > > create table XX (id int); > insert into XX (select xx from "1 to 1000" of integers) > > or... > > select IntSeq.MissingValues, x.UniqIntId,x.A,x.B,x.C, > from MyDataTable x > left outer join > ( > select MissingValues from "1 to 1000" of integers > ) IntSeq on MissingValues=x.UniqIntId > > > I'm hoping that someone has done this and might be able to > point to some function or methode to do this Maybe something like this will help: SELECT id FROM generate_series(1, (SELECT last_value FROM id_seq)) AS s(id) EXCEPT SELECT UniqIntId FROM MyDataTable ORDER BY id; The id_seq is the sequence on your ID column, assuming it has one, or you can replace the (SELECT ... FROM id_seq) by 1000. Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] selecting random row values in postgres
On 2/24/07, Sumeet <[EMAIL PROTECTED]> wrote: Hi all, I'm trying to write a query to select random values from a set of 'GROUP BY' see the scenario below to understand the problem here (the actual problem cannot be discussed here so i'm taking an example scenario) Assume there is a table id | name | year_of_birth query: I want to select for each year_of_birth a random name. Dear Sumeet postgresql DISTINCT ON may be of help , but its not standard sql. regds mallah. tradein_clients=> SELECT * from temp.test; ++--+-+ | id | name | yob | ++--+-+ | 1 | A| 2 | | 2 | B| 2 | | 3 | C| 2 | | 4 | D| 1 | | 5 | E| 1 | | 6 | F| 1 | ++--+-+ (6 rows) tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.test order by yob,random(); ++--+-+ | id | name | yob | ++--+-+ | 5 | E| 1 | | 1 | A| 2 | ++--+-+ (2 rows) tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.test order by yob,random(); ++--+-+ | id | name | yob | ++--+-+ | 4 | D| 1 | | 1 | A| 2 | ++--+-+ (2 rows) --> so i do a group by year_of_birth, now i have a set of names, is there any function to select just one name from these set of names. The current approach i'm using to solve this problem is 1) getting these names in a single string using a custom function 'group_concat' 2) Convert the single string into an array 3) use postgresql random function to generate a random number 4) us the random number to select a element from the array previously created. The solution is there but it's kinda hack, is there any other better way of solving this problem. Thanks, Sumeet
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Yes, but if it was '2004-01-02 01:00:00'-'2004-01-01 00:00:00' it should
return 25:00:00, not 1 day 1:00.
I agree with Tom that this should be changed; I'm just arguing that we
might well need a backwards-compatibility solution for a while. At the
very least we'd need to make this change very clear to users.
On Tue, Feb 20, 2007 at 08:07:11PM -0500, Bruce Momjian wrote:
>
> One problem with removing justify_hours() is that this is going to
> return '24:00:00', rather than '1 day:
>
> test=> select '2004-01-02 00:00:00'::timestamptz - '2004-01-01
> 00:00:00'::timestamptz;
>?column?
> --
>24:00:00
> (1 row)
>
> ---
>
> Jim Nasby wrote:
> > On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
> > > regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
> > > 09:30:41'::timestamp);
> > > ?column?
> > > --
> > > 14 days 14:28:19
> > > (1 row)
> > >
> > > should be reporting '350:28:19' instead.
> > >
> > > This is a hack that was done to minimize the changes in the regression
> > > test expected outputs when we changed type interval from months/
> > > seconds
> > > to months/days/seconds. But I wonder whether it wasn't a dumb idea.
> > > It is certainly inconsistent, as noted in the code comments.
> > >
> > > I'm tempted to propose that we remove the justify_hours call, and tell
> > > anyone who really wants the old results to apply justify_hours() to
> > > the
> > > subtraction result for themselves. Not sure what the fallout would
> > > be,
> > > though.
> >
> > I suspect there's applications out there that are relying on that
> > being nicely formated for display purposes.
> >
> > I agree it should be removed, but we might need a form of backwards
> > compatibility for a version or two...
> > --
> > Jim Nasby[EMAIL PROTECTED]
> > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 3: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/docs/faq
>
> --
> Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us
> EnterpriseDB http://www.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] selecting random row values in postgres
Thanks Buddy, really appreciate ur help on this problem solved... Is there any way this query can be optimized...i'm running it on a huge table with joins - Sumeet On 2/23/07, Rajesh Kumar Mallah <[EMAIL PROTECTED]> wrote: On 2/24/07, Sumeet <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm trying to write a query to select random values from a set of 'GROUP > BY' > see the scenario below to understand the problem here (the actual > problem cannot be discussed here so i'm taking an example scenario) > > Assume there is a table > > id | name | year_of_birth > > query: I want to select for each year_of_birth a random name. Dear Sumeet postgresql DISTINCT ON may be of help , but its not standard sql. regds mallah. tradein_clients=> SELECT * from temp.test; ++--+-+ | id | name | yob | ++--+-+ | 1 | A| 2 | | 2 | B| 2 | | 3 | C| 2 | | 4 | D| 1 | | 5 | E| 1 | | 6 | F| 1 | ++--+-+ (6 rows) tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.testorder by yob,random(); ++--+-+ | id | name | yob | ++--+-+ | 5 | E| 1 | | 1 | A| 2 | ++--+-+ (2 rows) tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.testorder by yob,random(); ++--+-+ | id | name | yob | ++--+-+ | 4 | D| 1 | | 1 | A| 2 | ++--+-+ (2 rows) > > --> so i do a group by year_of_birth, now i have a set of names, is there > any function to select just one name from these set of names. > The current approach i'm using to solve this problem is > > 1) getting these names in a single string using a custom function > 'group_concat' > 2) Convert the single string into an array > 3) use postgresql random function to generate a random number > 4) us the random number to select a element from the array previously > created. > > The solution is there but it's kinda hack, is there any other better way of > solving this problem. > > > Thanks, > Sumeet -- Thanks, Sumeet Ambre Master of Information Science Candidate, Indiana University.
Re: [SQL] selecting random row values in postgres
On 2/24/07, Sumeet <[EMAIL PROTECTED]> wrote: got itI just figured out that i dont need the ORDER BY clause even the first row selected by the 'DISTINCT ON' would solve the problem. Dear Sumeet, if order by is not done there is no certainty about which row gets selected. usually same row keeps getting selected. but if you want a really random rows to come order by is required. if you need certainty about the particular row being selected order by a non random() column is required. regds mallah. Thanks for all you help -Sumeet. On 2/23/07, Sumeet <[EMAIL PROTECTED]> wrote: > > Thanks Buddy, really appreciate ur help on this > > problem solved... > > Is there any way this query can be optimized...i'm running it on a huge > table with joins > > - Sumeet > > > On 2/23/07, Rajesh Kumar Mallah <[EMAIL PROTECTED]> wrote: > > > > > > > > On 2/24/07, Sumeet <[EMAIL PROTECTED]> wrote: > > > Hi all, > > > > > > I'm trying to write a query to select random values from a set of > > 'GROUP > > > BY' > > > see the scenario below to understand the problem here (the > > actual > > > problem cannot be discussed here so i'm taking an example scenario) > > > > > > Assume there is a table > > > > > > id | name | year_of_birth > > > > > > query: I want to select for each year_of_birth a random name. > > > > Dear Sumeet > > > > postgresql DISTINCT ON may be of help , but its not standard sql. > > > > regds > > mallah. > > > > tradein_clients=> SELECT * from temp.test; > > ++--+-+ > > | id | name | yob | > > ++--+-+ > > | 1 | A| 2 | > > | 2 | B| 2 | > > | 3 | C| 2 | > > | 4 | D| 1 | > > | 5 | E| 1 | > > | 6 | F| 1 | > > ++--+-+ > > (6 rows) > > > > tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.testorder by yob,random(); > > ++--+-+ > > | id | name | yob | > > ++--+-+ > > | 5 | E| 1 | > > | 1 | A| 2 | > > ++--+-+ > > (2 rows) > > > > tradein_clients=> SELECT distinct on (yob) id,name,yob from temp.testorder by yob,random(); > > ++--+-+ > > | id | name | yob | > > ++--+-+ > > | 4 | D| 1 | > > | 1 | A| 2 | > > ++--+-+ > > (2 rows) > > > > > > > > > > > > > > --> so i do a group by year_of_birth, now i have a set of names, is > > there > > > any function to select just one name from these set of names. > > > The current approach i'm using to solve this problem is > > > > > > 1) getting these names in a single string using a custom function > > > 'group_concat' > > > 2) Convert the single string into an array > > > 3) use postgresql random function to generate a random number > > > 4) us the random number to select a element from the array > > previously > > > created. > > > > > > The solution is there but it's kinda hack, is there any other better > > way of > > > solving this problem. > > > > > > > > > Thanks, > > > Sumeet > > > > > > -- > Thanks, > Sumeet Ambre > Master of Information Science Candidate, > Indiana University. -- Thanks, Sumeet Ambre Master of Information Science Candidate, Indiana University.
Re: [SQL] selecting random row values in postgres
Sumeet wrote: Thanks Buddy, really appreciate ur help on this problem solved... Is there any way this query can be optimized...i'm running it on a huge table with joins ORDER BY rand() is rather slow on large datasets, since the db has to actually generate a random value for each row in the table, before being able use it to sort by. Preferable ways to do this include f.ex: SELECT max(id) FROM table; SELECT * FROM table WHERE id > [rand value between 0 and max(id)] ORDER BY id LIMIT 1; This means you need to execute 2 queries, and it can also be a good idea to somehow cache the number of rows/largest ID of the table, for quicker performence. You can find an interesting discussion on this topic at http://thedailywtf.com/Comments/Finding_Random_Rows.aspx (yeah, I know. thedayilywtf.com isn't normally what I'd use as a reference for anything :-) ) -- Tommy ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] selecting random row values in postgres
Tommy Gildseth wrote: Sumeet wrote: Thanks Buddy, really appreciate ur help on this problem solved... Is there any way this query can be optimized...i'm running it on a huge table with joins ORDER BY rand() is rather slow on large datasets, since the db has to actually generate a random value for each row in the table, before being able use it to sort by. Preferable ways to do this include f.ex: SELECT max(id) FROM table; SELECT * FROM table WHERE id > [rand value between 0 and max(id)] ORDER BY id LIMIT 1; I'd have thought that in most cases the slowness would be due more to the sort than the cost of generating the random numbers. Your pair of queries neatly avoid doing the sort, but it does raise the question, does it matter if not all rows have the same chance of being picked? As an extreme example in the above, if there are id's 1-100 and id = 10 in the table, the last one will almost always be picked. I wonder if the random aggregate that Josh Berkus wrote could be adapted to return a record type rather than a random value of a single column? Its big advantage is that it requires no sorting and all entries are equiprobable. Unfortunately I'm a bit short of time at this end of a Friday to do much but give you the URL: http://www.powerpostgresql.com/Random_Aggregate HTH, Geoff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
