[GENERAL] SELECT question
Hi, I have a bit string , 7 bits, every bit representing a day of the week. e.g. 1110011. Is there and easy way where I can translate/format that string in a query. I want to give the string back with a '-' for every 0 and the first char of the Day for every '1'. example 1100111 = SM--TFS. thanks for any suggestions Alex ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] SELECT Question
Hi, i want to calculate the price difference, change% of 2 price records. Is there an easy way to do that within one query, rather than writing a function? Example: company, price_date, price, change, change_perc compA, 20060203,100,,, compA, 20060202,100,,, compA, 20060201,100,,, for one, i would like to calculate for one date the changes (price, and percentage) to the previous day and update the record. i also want to be able to run that through the entire history to re-calculate the changes i.e. not only update one record but update all of these. Currently I am doing it with a function but I wonder if there is an easier and faster way. Any suggestion or help would be appreciated. Thanks in advance Alex ---(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
[GENERAL] SELECT question
* * Do not Cc: me, because I am on THIS list, if I write here.* * Keine Cc: an mich, bin auf DIESER Liste wenn ich hier schreibe. * * Ne me mettez pas en Cc:, je suis sur CETTE liste, si j'ecris ici. * * Hello, I have a very huge table of references from Websites (html, pics, ...) where the elements stored on a fileserver using sha384. Now the indextable hold all filenames and download dates but now I like to get a "snapshoot on a paticular day. How must I create the SELCT statement to get ALL files valid on a particular day? Note: There can be every time a new index.html for example but images change only once a month... So I need all elements valable on the paticular day which mean, I need to select that LAST version of the elements... Greetings Michelle Konzack -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSN LinuxMichi 0033/6/6192519367100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
[GENERAL] Select question
Hi all, I am pretty sure I've done this before, but I am drawing a blank on how I did it or even what commands I need. Missing the later makes it hard to search. :P I've got Postfix working using PostgreSQL as the backend on a small, simple test database where I have a simple table called 'users' with a column called 'usr_email' which holds, surprisingly, the user's email address (ie: '[EMAIL PROTECTED]'). To tell Postfix where the user's email inbox is (to write incoming email to) I tell it to do this query: SELECT substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@')||'/inbox' AS email_file FROM users WHERE usr_email='[EMAIL PROTECTED]'; Which returns: email_file - feneon.com/mkelly/inbox Now I want to move to a more complex database where the email name comes from 'users' -> 'usr_email' (ie: 'mkelly') and the domain suffix comes from 'domains' -> 'dom_name' (ie: 'test.com'). The problem is, I am limited to how I can tell Postfix to generate the query. Specifically, I can't (or don't know how to) tell Postfix to create a join or split the email address. I can only tell Postfix what table to query, what the SELECT field to use, and what column to do the WHERE on. So, my question, Can I create a 'virtual table' table (or some such) that would take something like?: SELECT email_file FROM virtual_table WHERE email_addy='[EMAIL PROTECTED]'; Where the email_addy can be split to create this query: SELECT b.dom_name||'/'||a.usr_email||'/inbox' AS email_file FROM users a, domains b WHERE a.usr_dom_id=b.dom_id AND a.usr_email='mkelly' AND b.dom_name='test.com'; Which would still return: email_file -- alteeve.com/mkelly/inbox I hope I got the question across well enough. :) Thanks all! Madi ---(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
[GENERAL] select question
Dear all, Is there a way I can select the top 50 rows from table, 51 - 100 rows from table etc (with order clause)? It is because I am writing a message board and I would like to create the prev/next button on different page. Many thanks. Best regards, Boris
Re: [GENERAL] SELECT Question
On Mon, 1 Sep 2003, Alex wrote: > Hi, > > I need to form a query where i can add some columns based on the result. > > > Table A > ColA, ColB > -- > 1 A > 2 B > 3 A > > Table B > ColC > > A > > If A exists if would like the result back as > 1 A OK > 2 B NG > 3 A OK > > Is it possible to replace the value in the query ? Maybe something like one of these: select cola, colb, case when not exists(select 1 from table_b where table_b.colc=table_a.colb) then 'NG' else 'OK' end from table_a; select cola, colb, case when colc is null then 'NG' else 'OK' end from table_a left outer join table_b on (table_a.colb=table_b.colc); select cola, colb, case when (select count(*) from table_b where table_b.colc=table_a.colb)=0 then 'NG' else 'OK' end from table_a; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SELECT Question
If I'm understanding you correctly, you can do something like: select cola, colb, exists (select 'x' from tableb where colc = colb) from tablea Since that has a subselect, you may get better performance with something like this: select cola, colb, case when colc is null then 'f' else 't' end as exists from table1 left join table2 on colb = colc; jmelloy=# create table table1(cola serial, colb char); NOTICE: CREATE TABLE will create implicit sequence 'table1_cola_seq' for SERIAL column 'table1.cola' CREATE TABLE jmelloy=# create table table2 (colc char); CREATE TABLE jmelloy=# insert into table1 (colb) values ('A'); INSERT 1551538 1 jmelloy=# insert into table1 (colb) values ('B'); INSERT 1551539 1 jmelloy=# insert into table1 (colb) values ('a'); INSERT 1551540 1 jmelloy=# insert into table2 values ('B'); INSERT 1551541 1 jmelloy=# select cola, colb, exists (select 'x' from table2 where colc = colb) from table1; cola | colb | ?column? --+--+-- 1 | A| f 2 | B| t 3 | a| f (3 rows) jmelloy=# select cola, colb, case when colc is null then 'f' else 't' end as exists from table1 left join table2 on colb = colc; cola | colb | exists --+--+ 1 | A| f 2 | B| t 3 | a| f (3 rows) On Sunday, August 31, 2003, at 12:03 PM, Alex wrote: Hi, I need to form a query where i can add some columns based on the result. Table A ColA, ColB -- 1 A 2 B 3 A Table B ColC A If A exists if would like the result back as 1 A OK 2 B NG 3 A OK Is it possible to replace the value in the query ? Thanks Alex ---(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 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: [GENERAL] SELECT Question
Jeffrey, second solution is a beauty... thanks a lot. Alex Jeffrey Melloy wrote: If I'm understanding you correctly, you can do something like: select cola, colb, exists (select 'x' from tableb where colc = colb) from tablea Since that has a subselect, you may get better performance with something like this: select cola, colb, case when colc is null then 'f' else 't' end as exists from table1 left join table2 on colb = colc; jmelloy=# create table table1(cola serial, colb char); NOTICE: CREATE TABLE will create implicit sequence 'table1_cola_seq' for SERIAL column 'table1.cola' CREATE TABLE jmelloy=# create table table2 (colc char); CREATE TABLE jmelloy=# insert into table1 (colb) values ('A'); INSERT 1551538 1 jmelloy=# insert into table1 (colb) values ('B'); INSERT 1551539 1 jmelloy=# insert into table1 (colb) values ('a'); INSERT 1551540 1 jmelloy=# insert into table2 values ('B'); INSERT 1551541 1 jmelloy=# select cola, colb, exists (select 'x' from table2 where colc = colb) from table1; cola | colb | ?column? --+--+-- 1 | A| f 2 | B| t 3 | a| f (3 rows) jmelloy=# select cola, colb, case when colc is null then 'f' else 't' end as exists from table1 left join table2 on colb = colc; cola | colb | exists --+--+ 1 | A| f 2 | B| t 3 | a| f (3 rows) On Sunday, August 31, 2003, at 12:03 PM, Alex wrote: Hi, I need to form a query where i can add some columns based on the result. Table A ColA, ColB -- 1 A 2 B 3 A Table B ColC A If A exists if would like the result back as 1 A OK 2 B NG 3 A OK Is it possible to replace the value in the query ? Thanks Alex ---(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 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT question
On Tuesday 04 November 2003 10:54, Alex wrote: > Hi, > > I have a bit string , 7 bits, every bit representing a day of the week. > e.g. 1110011. > Is there and easy way where I can translate/format that string in a query. > I want to give the string back with a '-' for every 0 and the first char > of the Day for every '1'. > example 1100111 = SM--TFS. You probably want a small function in plpgsql - see the procedural languages section of the manual for details. You might want to check the cookbook at http://techdocs.postgresql.org/ and see if there's similar code you can use as inspiration. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] SELECT Question
On Thu, 20 Nov 2003, Alex wrote: > >>Is there an easy way to write a select statement that returns me the > >>frist free number or any within the range of 200? > >>For example if 1-30, and 32-50 are occupied then i would like to fill in > >>the new entry with id 31. If you had a table with an id column and 200 rows 1-200 you could do SELECT MIN(idtab.id) FROM idtab LEFT JOIN realtab ON (idtab.id = realtab.id AND realtab.id IS NULL) A useful generic function would be one something like range(min,max) that would return a set of rows so you wouldn't have to actually have a table. Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SELECT Question
On Thu, 20 Nov 2003 16:52:37 +0900, Alex <[EMAIL PROTECTED]> wrote: Is there an easy way to write a select statement that returns me the frist free number or any within the range of 200? For example if 1-30, and 32-50 are occupied then i would like to fill in the new entry with id 31. Fortunately this is not the performance mailing list :-) First free number: SELECT max(t1.id) + 1 FROM t AS t1 INNER JOIN t AS t2 ON (t1.id < 200 AND t1.id < t2.id AND t2.id <= 200) GROUP BY t2.id HAVING max(t1.id) + 1 < t2.id ORDER BY t2.id LIMIT 1; Make sure that there is always a row with id=0 and a row with id=200. Any free number: SELECT id - 1 FROM t WHERE 1 < id AND id <= 200 AND NOT EXISTS (SELECT * FROM t AS t2 WHERE t2.id = t.id - 1) LIMIT 1; Always having a row with id=200 helps avoid unwanted corner cases. One more: SELECT coalesce(max(id), 0) + 1 FROM t WHERE id <= 200 AND id = (SELECT count(*) FROM t AS t2 WHERE t2.id <= t.id); This should work without any dummy rows. And it will not work, if id is not unique or there is any row with id < 1. Servus Manfred ---(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: [GENERAL] SELECT Question
Kris Jurka wrote: A useful generic function would be one something like range(min,max) that would return a set of rows so you wouldn't have to actually have a table. You mean like this? CREATE OR REPLACE FUNCTION test(int,int) RETURNS SETOF int AS ' BEGIN FOR i IN $1..$2 LOOP RETURN NEXT i; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql' STRICT IMMUTABLE; regression=# select * from test(4, 8); test -- 4 5 6 7 8 (5 rows) HTH, Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SELECT Question
All, thanks for the many suggestions Alex Manfred Koizar wrote: On Thu, 20 Nov 2003 16:52:37 +0900, Alex <[EMAIL PROTECTED]> wrote: Is there an easy way to write a select statement that returns me the frist free number or any within the range of 200? For example if 1-30, and 32-50 are occupied then i would like to fill in the new entry with id 31. Fortunately this is not the performance mailing list :-) First free number: SELECT max(t1.id) + 1 FROM t AS t1 INNER JOIN t AS t2 ON (t1.id < 200 AND t1.id < t2.id AND t2.id <= 200) GROUP BY t2.id HAVING max(t1.id) + 1 < t2.id ORDER BY t2.id LIMIT 1; Make sure that there is always a row with id=0 and a row with id=200. Any free number: SELECT id - 1 FROM t WHERE 1 < id AND id <= 200 AND NOT EXISTS (SELECT * FROM t AS t2 WHERE t2.id = t.id - 1) LIMIT 1; Always having a row with id=200 helps avoid unwanted corner cases. One more: SELECT coalesce(max(id), 0) + 1 FROM t WHERE id <= 200 AND id = (SELECT count(*) FROM t AS t2 WHERE t2.id <= t.id); This should work without any dummy rows. And it will not work, if id is not unique or there is any row with id < 1. Servus Manfred ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SELECT Question
On Sat, Mar 04, 2006 at 03:35:02 +1100, Alex <[EMAIL PROTECTED]> wrote: > Hi, > i want to calculate the price difference, change% of 2 price records. Is > there an easy way to do that within one query, rather than writing a > function? You can use a self join to do this. It won't be spectaculatly fast though. The idea is too join each record to the record with the largest date less than the date of the current record. Note that on record won't have a match. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SELECT question
On Aug 17, 2007, at 7:27 , Michelle Konzack wrote: * * Do not Cc: me, because I am on THIS list, if I write here.* You might want to consider changing your mailing list subscription settings to "eliminatecc", e.g., send email to [EMAIL PROTECTED] (not the list address!) with body set pgsql-general eliminatecc This should prevent the mailing list from sending you a second copy. I have a very huge table of references from Websites (html, pics, ...) where the elements stored on a fileserver using sha384. Now the indextable hold all filenames and download dates but now I like to get a "snapshoot on a paticular day. How must I create the SELCT statement to get ALL files valid on a particular day? Note: There can be every time a new index.html for example but images change only once a month... So I need all elements valable on the paticular day which mean, I need to select that LAST version of the elements... I think what you want is something like: SELECT DISTINCT ON (website_reference) website_reference, download_date, file_path FROM indextable WHERE download_date <= ? -- whatever date you're interested in ORDER BY website_reference, download_date DESC; This should return the most recent website_reference and its download_date that's earlier than the download_date specified in the WHERE clause. DISTINCT ON is a (very helpful) PostgreSQL extension. You can get similar results using a subquery; SELECT website_reference, download_date, file_path FROM indextable NATURAL JOIN ( SELECT website_reference, max(download_date) as download_date FROM indextable WHERE download_date <= ? GROUP BY website_reference ) most_recent_versions; This may return more than one row per website_reference if the website_reference has more than on file_path for a particular download_date. Does this help? If not, could you give a bit more of a concrete example? (Is is just me or have there been a lot of queries that can be solved using DISTINCT ON recently?) Michael Glaesemann grzm seespotcode net PGP.sig Description: This is a digitally signed message part
Re: [GENERAL] SELECT question
Am 2007-08-17 12:53:41, schrieb Michael Glaesemann: > > On Aug 17, 2007, at 7:27 , Michelle Konzack wrote: > > >* > >* Do not Cc: me, because I am on THIS list, if I write here.* > > You might want to consider changing your mailing list subscription > settings to "eliminatecc", e.g., send email to > [EMAIL PROTECTED] (not the list address!) with body > > set pgsql-general eliminatecc > > This should prevent the mailing list from sending you a second copy. Which mean, my "INBOX.ML_pgsql.general/" will never receive messages and break all threads where someone send me CC's... > I think what you want is something like: > > SELECT DISTINCT ON (website_reference) website_reference, > download_date, file_path > FROM indextable > WHERE download_date <= ? -- whatever date you're interested in > ORDER BY website_reference, download_date DESC; > > This should return the most recent website_reference and its > download_date that's earlier than the download_date specified in the > WHERE clause. > > DISTINCT ON is a (very helpful) PostgreSQL extension. You can get > similar results using a subquery; I have never used "DISTINCT ON" (it was not known to me) and was trying subqueries... :-/ > SELECT website_reference, download_date, file_path > FROM indextable > NATURAL JOIN ( > SELECT website_reference, max(download_date) as download_date > FROM indextable > WHERE download_date <= ? > GROUP BY website_reference > ) most_recent_versions; > > This may return more than one row per website_reference if the > website_reference has more than on file_path for a particular > download_date. > > Does this help? If not, could you give a bit more of a concrete example? I have an Enterprise which do researches :-) and I have a local cache of more then 150.000.000 URL's and its content (~8 TByte)... (I have hit over 2000 md5 collisons and now using sha384) Also I get per day nearly 100.000 new files... OK, HTML pages are downloaded and go into the first table like indextable FULL_URL, SHA384SUM and the second table content SERNUM (uniq), SHA384SUM (pri), LOCAL_PATH the saved file get as the name the SHA384 name If I open an HTML-URL with a specific date, it is parsed and the URL's inline are adapted to make it work from my database, e.g. http://www.postgresql.org/index.html will become http://webcache/show.php?date=123456789&url=http://www.postgresql.org/index.html inline elements and already downloaded other links will bekome http://webcache/show.php?date=123456789&url= Thanks to the PostgreSQL developers that they have created "tablespace" and "table partitioning" since searching in 150.000.000 ROW's is the hell. > (Is is just me or have there been a lot of queries that can be solved > using DISTINCT ON recently?) I do not know... Since when does "DISTINCT ON" exist? Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSN LinuxMichi 0033/6/6192519367100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] SELECT question
On 8/18/07, Michelle Konzack <[EMAIL PROTECTED]> wrote: > Am 2007-08-17 12:53:41, schrieb Michael Glaesemann: > > (Is is just me or have there been a lot of queries that can be solved > > using DISTINCT ON recently?) > > I do not know... Since when does "DISTINCT ON" exist? I have been lurking on this thread, don't have much to add, but distinct on has been around in postgresql for as long as I can remember. It's not SQL spec, so it's not portable. but it is awefully useful. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELECT question
Michelle Konzack wrote: > Am 2007-08-17 12:53:41, schrieb Michael Glaesemann: > > > > On Aug 17, 2007, at 7:27 , Michelle Konzack wrote: > > > > >* > > >* Do not Cc: me, because I am on THIS list, if I write here.* > > > > You might want to consider changing your mailing list subscription > > settings to "eliminatecc", e.g., send email to > > [EMAIL PROTECTED] (not the list address!) with body > > > > set pgsql-general eliminatecc > > > > This should prevent the mailing list from sending you a second copy. > > Which mean, my "INBOX.ML_pgsql.general/" will never receive > messages and break all threads where someone send me CC's... So have messages which have the list on CC go to that folder as well, :0: * [EMAIL PROTECTED] INBOX.ML_pgsql.general/ Another choice you have is to tell mutt that you're subscribed to the list, adding to .muttrc lists pgsql-general@postgresql.org subscribe pgsql-general@postgresql.org then it will generate the Mail-Followup-To: header, omitting your address, so you will only get it via Majordomo. (It will still fail sometimes if older MUAs don't honor that field). I found it annoying so I disabled it by setting set followup_to=no Another thing I did was removing of duplicates with procmail, :0 Wh: msgid.lock | formail -D 65536 $HOME/.msgid.cache so I don't get two copies when I'm on the list and on CC. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Management by consensus: I have decided; you concede. (Leonard Liu) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Select question
Woops, I wasn't careful enough when I wrote that email, sorry. The results showed my real domains instead of 'test.com'. I had different domains in the test and real DBs. Madison Kelly wrote: email_file - feneon.com/mkelly/inbox and email_file -- alteeve.com/mkelly/inbox *sigh* Should have shown: email_file --- test.com/mkelly/inbox I'll go get a coffee and wake up some more. :) Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Select question
On 8/30/07, Madison Kelly <[EMAIL PROTECTED]> wrote: > Hi all, > >I am pretty sure I've done this before, but I am drawing a blank on > how I did it or even what commands I need. Missing the later makes it > hard to search. :P > >I've got Postfix working using PostgreSQL as the backend on a small, > simple test database where I have a simple table called 'users' with a > column called 'usr_email' which holds, surprisingly, the user's email > address (ie: '[EMAIL PROTECTED]'). > >To tell Postfix where the user's email inbox is (to write incoming > email to) I tell it to do this query: > > SELECT > substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM > '(.*)@')||'/inbox' >The problem is, I am limited to how I can tell Postfix to generate > the query. Specifically, I can't (or don't know how to) tell Postfix to > create a join or split the email address. I can only tell Postfix what > table to query, what the SELECT field to use, and what column to do the > WHERE on. I seem to recall giving out a query about that in the IRC channel a while back...so if you got it from me, now I'll attempt to finish the job :-). If you can get postfix to look at a view, maybe you could CREATE VIEW email_v AS SELECT usr_email, dom_name, b.dom_name||'/'||a.usr_email||'/inbox' AS email_file FROM users a, domains b WHERE a.usr_dom_id=b.dom_id; AND a.usr_email='mkelly' AND b.dom_name='test.com'; and just select * from email_v where usr_email = 'mkelly' and dom_name = 'test.com'; merlin ---(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: [GENERAL] Select question
Merlin Moncure wrote: I seem to recall giving out a query about that in the IRC channel a while back...so if you got it from me, now I'll attempt to finish the job :-). If you can get postfix to look at a view, maybe you could CREATE VIEW email_v AS SELECT usr_email, dom_name, b.dom_name||'/'||a.usr_email||'/inbox' AS email_file FROM users a, domains b WHERE a.usr_dom_id=b.dom_id; AND a.usr_email='mkelly' AND b.dom_name='test.com'; and just select * from email_v where usr_email = 'mkelly' and dom_name = 'test.com'; merlin Hiya, Nope, wasn't me, but I was indeed able to solve the problem with a few (I posted the details in a follow up). It was pretty similar to your suggestion, so you were certainly onto something. :) Madi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] select question
* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [000729 10:57] wrote: > Dear all, > > Is there a way I can select the top 50 rows from table, 51 - 100 rows from > table etc (with order clause)? It is because I am writing a message board > and I would like to create the prev/next button on different page. I think you want to look at the OFFSET and LIMIT clauses explained in the documentation. -Alfred
Re: [GENERAL] select question
On Sat, 29 Jul 2000, [EMAIL PROTECTED] wrote: > Dear all, > > Is there a way I can select the top 50 rows from table, 51 - 100 rows from > table etc (with order clause)? It is because I am writing a message board > and I would like to create the prev/next button on different page. > > Many thanks. > > Best regards, > Boris A cursor might also work for you. Example: $offset = $pageno * $rowsperpage; BEGIN; DECLARE mycur CURSOR FOR SELECT * FROM mytable WHERE age > 20 ORDER BY name; FETCH FORWARD $offset FROM mycur; CLOSE mycur; END; I forget what the advantages/disadvantages are between CURSOR and LIMIT. I've used a CURSOR and it works fine for doing paging. One thing I'd still like to know, is what are the most efficient ways to get the count of rows in cursor? I guess a SELECT count(*) is the only way but seems that would be slow on large tables. Hmm, maybe SELECT INTO TEMPORARY TABLE with LIMIT is a good way, then you can do a SELECT count(*) on the temp table without scanning the whole larger table again. Anyone reading this having any comments on this? -- - Robert
Re: [GENERAL] select question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Is there a way I can select the top 50 rows from table, 51 - 100 rows from > table etc (with order clause)? It is because I am writing a message board > and I would like to create the prev/next button on different page. Look at the documentation for the 'limit' clause. Ian -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.1 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE5gyXxfn9ub9ZE1xoRAiIRAKCF4CCP3CGVVl+aY4jmdP+def2JYQCfRg8e zWP3OaPFXxr34n8FMSV4N4A= =33xl -END PGP SIGNATURE-
Solved! Was: Re: [GENERAL] Select question
Madison Kelly wrote: Hi all, I am pretty sure I've done this before, but I am drawing a blank on how I did it or even what commands I need. Missing the later makes it hard to search. :P I've got Postfix working using PostgreSQL as the backend on a small, simple test database where I have a simple table called 'users' with a column called 'usr_email' which holds, surprisingly, the user's email address (ie: '[EMAIL PROTECTED]'). To tell Postfix where the user's email inbox is (to write incoming email to) I tell it to do this query: SELECT substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM '(.*)@')||'/inbox' AS email_file FROM users WHERE usr_email='[EMAIL PROTECTED]'; Which returns: email_file - feneon.com/mkelly/inbox Now I want to move to a more complex database where the email name comes from 'users' -> 'usr_email' (ie: 'mkelly') and the domain suffix comes from 'domains' -> 'dom_name' (ie: 'test.com'). The problem is, I am limited to how I can tell Postfix to generate the query. Specifically, I can't (or don't know how to) tell Postfix to create a join or split the email address. I can only tell Postfix what table to query, what the SELECT field to use, and what column to do the WHERE on. So, my question, Can I create a 'virtual table' table (or some such) that would take something like?: SELECT email_file FROM virtual_table WHERE email_addy='[EMAIL PROTECTED]'; Where the email_addy can be split to create this query: SELECT b.dom_name||'/'||a.usr_email||'/inbox' AS email_file FROM users a, domains b WHERE a.usr_dom_id=b.dom_id AND a.usr_email='mkelly' AND b.dom_name='test.com'; Which would still return: email_file -- alteeve.com/mkelly/inbox I hope I got the question across well enough. :) Thanks all! Madi ---(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 I got the answer from an Ian Peterson from the GTALUG. Thought I'd post the answer here, "for the record". -=-=-=- CREATE VIEW email_file AS SELECT u.usr_email || '@' || d.dom_name AS email, d.dom_name || '/' || u.usr_email || '/inbox' AS file FROM users u JOIN domains d ON u.usr_dom_id=d.dom_id; -=-=-=- Which allows the query: -=-=-=- SELECT file FROM email_file WHERE email='[EMAIL PROTECTED]'; -=-=-=- To return: -=-=-=- file --- test.com/mkelly/inbox -=-=-=- Perfect! :) Madi ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] SELECT question (splitting a field)
Hi all, Hopefully a quick question... Why does: nmc=> SELECT 'Y' AS local FROM domains WHERE dom_name='test.com'; local --- Y (1 row) Work but: nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN ('[EMAIL PROTECTED]'); local --- (0 rows) Not work? I am sure I am missing something simple. :) Thanks!! Madi ---(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: [GENERAL] SELECT question (splitting a field)
On 9/4/07, Madison Kelly <[EMAIL PROTECTED]> wrote: >I am sure I am missing something simple. :) Yeah... '[EMAIL PROTECTED]' <> '@test.com' ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT question (splitting a field)
On 9/4/07, Madison Kelly <[EMAIL PROTECTED]> wrote: > Hi all, > >Hopefully a quick question... > >Why does: > > nmc=> SELECT 'Y' AS local FROM domains WHERE dom_name='test.com'; > local > --- > Y > (1 row) > >Work but: > > nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN > ('[EMAIL PROTECTED]'); > local > --- > (0 rows) > >Not work? because @dom_name isn't IN [EMAIL PROTECTED] I think you're looking for pattern matching. select 'Y' as local from domains where dom_name ilike '%test.com%' ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SELECT question (splitting a field)
Madison Kelly wrote: nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN ('[EMAIL PROTECTED]'); local --- (0 rows) Not work? I don't think IN does what you think it does. It's not a substring-test, but a set test: SELECT 1 WHERE 'x' IN ('a','b','c','x'); SELECT a FROM foo WHERE b IN (SELECT z FROM bar WHERE frozzled<>wamble); You could mess around with substring() and length() or I'd use LIKE. If it's just a domain you're looking for though, might be most efficient to strip the leading part off your value with regexp_replace(). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SELECT question (splitting a field)
Rodrigo De León wrote: On 9/4/07, Madison Kelly <[EMAIL PROTECTED]> wrote: I am sure I am missing something simple. :) Yeah... '[EMAIL PROTECTED]' <> '@test.com' Well now, don't I feel silly. *sigh* Thanks! Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT question (splitting a field)
Richard Huxton wrote: Madison Kelly wrote: nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN ('[EMAIL PROTECTED]'); local --- (0 rows) Not work? I don't think IN does what you think it does. It's not a substring-test, but a set test: SELECT 1 WHERE 'x' IN ('a','b','c','x'); SELECT a FROM foo WHERE b IN (SELECT z FROM bar WHERE frozzled<>wamble); You could mess around with substring() and length() or I'd use LIKE. If it's just a domain you're looking for though, might be most efficient to strip the leading part off your value with regexp_replace(). Yeah, that was my problem. I thought I was using the section following the '@'. =/ I've been using Postgres for a while now, but only recently getting into some of the fancier stuff. Until now, I've usually written the program using PgSQL so I could manipulate the data as I needed. Now I am using PgSQL as a backend for a few other applications so I am restricted to using PgSQL to manipulate the data. It's all left me feeling quite n00bish again. ;) I did figure out a query that worked: SELECT 'Y' AS local FROM domains d, users u WHERE u.usr_dom_id=d.dom_id AND u.usr_email||'@'||d.dom_name IN ('[EMAIL PROTECTED]'); Though this may not be the most efficient. In my case, the 'usr_email' is the LHS of the '@' sign and 'dom_name' is the domain name. If I wanted to use (I)LIKE, how would I have matched just the domain section of '[EMAIL PROTECTED]' in 'dom_name'? I'll go read up, now that I've got some key words to search the docs on. Thanks kindly! Madi ---(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: [GENERAL] SELECT question (splitting a field)
Madison Kelly wrote: SELECT 'Y' AS local FROM domains d, users u WHERE u.usr_dom_id=d.dom_id AND u.usr_email||'@'||d.dom_name IN ('[EMAIL PROTECTED]'); Though this may not be the most efficient. In my case, the 'usr_email' is the LHS of the '@' sign and 'dom_name' is the domain name. If I wanted to use (I)LIKE, how would I have matched just the domain section of '[EMAIL PROTECTED]' in 'dom_name'? Something like: SELECT ... FROM domains d WHERE ('%@' || d.dom_name) LIKE '[EMAIL PROTECTED]'; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Select question..... is there a way to do this?
Assume the following tables: Table ITEM (user text, subject text, number integer, changed timestamp); table SEEN (user text, number integer, lastviewed timestamp); Ok, now the data in the "SEEN" table will have one tuple for each user and number in the table ITEM which a user has viewed, and the last time they looked at it. It will also have one entry per user with a NULL number, which will be written to the table when the user scans the table and finds no new records (to keep the "SEEN" table from becoming exponentially large as otherwise it would require USER X ITEMs tuples!); that record marks the last time the user was there and there were no newer ITEMs. If I do the following query: select item.user, item.subject, item.number from item, seen where (item.user = seen.user) and (item.number = seen.number) and (item.changed > seen.lastviewed); I get all items which have a record in SEEN. So far so good. But what I want is all items which EITHER have (1) a record in SEEN which matches (and is older) OR which are NEWER than the SEEN record with a NULL "number". That I'm having trouble doing. The following does not do what I want: select item.user, item.subject, item.number from item, seen where (item.user = seen.user and item.number = seen.number and item.changed > seen.lastviewed) OR (item.user = seen.user and item.changed > seen.lastviewed and seen.number is null); That second query returns TWO entries for a SEEN record (which I can control out with "Select distinct") BUT it breaks in another nasty way - if I have an item that has a CHANGED time that is later than the null record, *IT GETS SELECTED EVEN IF IT HAS A DISTINCT RECORD*. That's no good. Ideas? (Attempted to use "AND NOT" as a conditional on the second clause to the OR and that didn't work; it excluded all of the NULL records) -- Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net %SPAMBLOCK-SYS: Matched [EMAIL PROTECTED], message ok ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Select question..... is there a way to do this?
On Aug 3, 11:17 pm, [EMAIL PROTECTED] (Karl Denninger) wrote: > Ideas? SELECT item.user, item.subject, item.number FROM item, seen WHERE item.user = seen.user AND item.number = seen.number AND item.changed > seen.lastviewed UNION SELECT item.user, item.subject, item.number FROM item, seen WHERE item.user = seen.user AND seen.number IS NULL AND item.changed > seen.lastviewed; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings