Re: [GENERAL] SELECT question (splitting a field)

[Rodrigo De León]

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)

[Scott Marlowe]

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)

[Richard Huxton]

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 frozzledwamble);

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)

[Madison Kelly]

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)

[Madison Kelly]

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 frozzledwamble);

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)

[Richard Huxton]

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

Re: [GENERAL] Select question

[Madison Kelly]

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

[Madison Kelly]

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

Solved! Was: Re: [GENERAL] Select question

[Madison Kelly]

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

Re: [GENERAL] Select question

[Merlin Moncure]

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

[Michelle Konzack]

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=123456789url=http://www.postgresql.org/index.html

inline elements and already downloaded other links will bekome

http://webcache/show.php?date=123456789url=original_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

[Scott Marlowe]

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

[Alvaro Herrera]

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

[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.


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..... is there a way to do this?

[Rodrigo De León]

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

Re: [GENERAL] SELECT Question

[Bruno Wolff III]

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

[Alex]

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

[Kris Jurka]

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

[Manfred Koizar]

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

[Joe Conway]

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

[Richard Huxton]

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

[Alex]

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

[Stephan Szabo]

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

[Jeffrey Melloy]

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

[Robert B. Easter]

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

[Ian Turner]

-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-