Re: [SQL] Using case or if to return multiple rows
Hello
what is relation between t1 and t3 and t2 and t3? Which row from t3
specifies value? You cannot do it in plain SQL. SQL is well for set
operations. You can use plpgsql and SRF function.
-- table1 and table2 have to have same structure
CREATE OR REPLACE FUNCTION output_tab(date)
RETURNS SETOF table1 AS $$
DECLARE t1 table1; t2 table2;
BEGIN
IF $1 = '' THEN
FOR t1 IN SELECT * FROM table1 LOOP
RETURN NEXT t1;
END LOOP;
ELSE
FOR t2 IN SELECT * FROM table1 LOOP
RETURN NEXT t2;
END LOOP;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
and then
SELECT * FROM output_tab('x');
Regards
Pavel Stehule
2007/7/12, Ashish Karalkar <[EMAIL PROTECTED]>:
Hello all,
I want to select data from two diffrent table based on third tables column
somthing like:
select case when t3.date='' then
select * from table1
else
select * from table 2
from table3 t3 where t3.date='x'
Problem is that I have to do it in Plain SQL.
Is there a anyway.
Thanks in Advance
With egards
Ashish
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Using case or if to return multiple rows
On fim, 2007-07-12 at 12:15 +0530, Ashish Karalkar wrote: > I want to select data from two diffrent table based on third tables > column > somthing like: > > > select case when t3.date='' then > select * from table1 > else > select * from table 2 > from table3 t3 where t3.date='x' > > Problem is that I have to do it in Plain SQL. you problem is not quite clear. do you want to output all rows from table1 (or table2) for each row of table3? or does table2 only have one row with date='x' ? is '' the same date as 'x' in your example? if so, how can table2's columns be selected. are you looking for something like: select * from table1 where (select date from table3)='' UNION ALL select * from table2 where (select date from table3)<>'' this might work if table1 and table2 have compatible schemas, and table3 only has 1 row, and table3.date is NOT NULL if this is not what you are looking for, you need to be more clear. gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Using case or if to return multiple rows
> > > select case when t3.date='' then > select * from table1 > else > select * from table 2 > from table3 t3 where t3.date='x' > > Problem is that I have to do it in Plain SQL. you problem is not quite clear. do you want to output all rows from table1 (or table2) for each row of table3? or does table2 only have one row with date='x' ? is '' the same date as 'x' in your example? if so, how can table2's columns be selected. are you looking for something like: select * from table1 where (select date from table3)='' UNION ALL select * from table2 where (select date from table3)<>'' maybe little bit more readable form: select * from table1 where exists (select 1 from table3 where date = '') union all select * from table2 where exists (select 1 from table3 where date = ''); it's possible with one big disadvantage. This query will do seq scan both tables and it can be slow on big tables. Regards Pavel Stehule ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Converting from MS Access field aliases
Good morning, Oh joyous day! We are upgrading a legacy database system from MS Access to PostgreSQL! Yay! Ok, rejoicing over. Here's our issue and PLEASE point me to the right place if this has been discussed before. In MS Access one can reuse field aliases later in the same query. For example: SELECT field1 / 2 AS foo, field2 * 2 AS bar, foo + bar AS total WHERE foo < 12; The first two fields are fine, it's the third that's a problem. The database reports ERROR: column "foo" does not exist This type of situation is happening -many- times in well over fifty existing SELECT..INTO and INSERT INTO queries. The obvious solution here is to copy the code around to eliminate the need to reuse "foo" and "bar" in the query: SELECT field1 / 2 AS foo, field2 * 2 AS bar, (field1 / 2) + (field2 * 2) AS total WHERE (field1 / 2) < 12; But this is a bit ugly and cumbersome and in our case, not desirable since foo and bar get used many times in the remains of the query. To replace them with the formulae means that debugging is quite difficult and very prone to errors. Does anyone have suggestions on how to circumvent this in a more graceful manner? I mean I could probably find a way to do this with a couple of queries and some views, or maybe write a function (or more like 30 functions) to do the work, but both of those only add to the workload in an undesirable manner. :) To complicate matters, performance is a concern. We're operating on upwards of a billion records. Not all at the same time, but the goal is to run these a series of calculations will be done on all of the data. Thanks for any input that you might have. --Joel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Using case or if to return multiple rows
"Pavel Stehule" <[EMAIL PROTECTED]> writes: > it's possible with one big disadvantage. This query will do seq scan > both tables and it can be slow on big tables. No, it should be reasonably OK, because if the added condition doesn't involve the tables being scanned it'll be turned into a one-time filter. As an example using the regression database: regression=# explain select * from tenk1 where 1 in (select f1 from int4_tbl) union all select * from tenk1 where 1 not in (select f1 from int4_tbl); QUERY PLAN - Append (cost=1.06..1118.13 rows=2 width=244) -> Result (cost=1.06..459.06 rows=1 width=244) One-Time Filter: (hashed subplan) -> Seq Scan on tenk1 (cost=0.00..458.00 rows=1 width=244) SubPlan -> Seq Scan on int4_tbl (cost=0.00..1.05 rows=5 width=4) -> Result (cost=1.06..459.06 rows=1 width=244) One-Time Filter: (NOT (hashed subplan)) -> Seq Scan on tenk1 (cost=0.00..458.00 rows=1 width=244) SubPlan -> Seq Scan on int4_tbl (cost=0.00..1.05 rows=5 width=4) (11 rows) The probe into int4_tbl will happen twice (but not more than that) and whichever tenk1 scan gets discarded won't happen at all. So unless the test condition itself is horribly expensive this should work as well as you could expect. regards, tom lane ---(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] Converting from MS Access field aliases
> Good morning, > > Oh joyous day! We are upgrading a legacy database system from MS > Access to PostgreSQL! Yay! > > Ok, rejoicing over. Here's our issue and PLEASE point me to the right > place if this has been discussed before. > > In MS Access one can reuse field aliases later in the same query. For > example: > >SELECT field1 / 2 AS foo, > field2 * 2 AS bar, > foo + bar AS total >WHERE foo < 12; > > The first two fields are fine, it's the third that's a problem. The > database reports > >ERROR: column "foo" does not exist > > This type of situation is happening -many- times in well over fifty > existing SELECT..INTO and INSERT INTO queries. The obvious solution > here is to copy the code around to eliminate the need to reuse "foo" > and "bar" in the query: > >SELECT field1 / 2 AS foo, > field2 * 2 AS bar, > (field1 / 2) + (field2 * 2) AS total >WHERE (field1 / 2) < 12; > > But this is a bit ugly and cumbersome and in our case, not desirable > since foo and bar get used many times in the remains of the query. To > replace them with the formulae means that debugging is quite > difficult and very prone to errors. > > Does anyone have suggestions on how to circumvent this in a more > graceful manner? I mean I could probably find a way to do this with a > couple of queries and some views, or maybe write a function (or more > like 30 functions) to do the work, but both of those only add to the > workload in an undesirable manner. :) > > To complicate matters, performance is a concern. We're operating on > upwards of a billion records. Not all at the same time, but the goal > is to run these a series of calculations will be done on all of the > data. > > Thanks for any input that you might have. > > --Joel Are you able to restructure your queries to be something like...? SELECT t2.foo + t2.bar FROM ( SELECT field1 / 2 AS foo, field2 * 2 AS bar FROM table1 t1 WHERE foo < 12 ) AS t2 PG allows sub-clauses and statements in the FROM clause, as well as in the WHERE & SELECT clauses. Not sure how these will perform on a billion rows, so a few EXPLAIN ANALYSE outputs might help tune the queries some more if you have them. The results should be semantically comparable to the MS Access queries though (I think). Nick ---(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] Converting from MS Access field aliases
>SELECT field1 / 2 AS foo, > field2 * 2 AS bar, > foo + bar AS total >WHERE foo < 12; > > The first two fields are fine, it's the third that's a problem. The > database reports > >ERROR: column "foo" does not exist > First, I think it would be great if this worked - like the alias to an update table added in 8.2 - saves a lot of typing and makes queries much more readable. Second, only way I see is to set this on top of a view generating your foo and bar aliases, and go from there. Compared to rewriting the expression each time, it has the advantages of better syntax in the end and might yield better performance as well. Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz ---(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] Converting from MS Access field aliases
chester c young <[EMAIL PROTECTED]> writes: >> SELECT field1 / 2 AS foo, >> field2 * 2 AS bar, >> foo + bar AS total >> WHERE foo < 12; > First, I think it would be great if this worked - like the alias to an > update table added in 8.2 - saves a lot of typing and makes queries > much more readable. This is not an "extension", it is *directly* contrary to both the letter and spirit of the SQL standard. I can hardly believe that M$ did that ... oh, actually, I can entirely believe it. The OP has a serious problem of vendor lockin now, and that's exactly what M$ wants. regards, tom lane ---(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] Converting from MS Access field aliases
On Jul 12, 2007, at 1:23 PM, Tom Lane wrote: chester c young <[EMAIL PROTECTED]> writes: SELECT field1 / 2 AS foo, field2 * 2 AS bar, foo + bar AS total WHERE foo < 12; First, I think it would be great if this worked - like the alias to an update table added in 8.2 - saves a lot of typing and makes queries much more readable. This is not an "extension", it is *directly* contrary to both the letter and spirit of the SQL standard. I can hardly believe that M$ did that ... oh, actually, I can entirely believe it. The OP has a serious problem of vendor lockin now, and that's exactly what M$ wants. regards, tom lane Hear hear! What's really screwy is what I found when I hooked access into my PostgreSQL database using pgsqlODBC (I know, it's an abomination) and I logged the statements that PostgreSQL was processing. In MS Access this query: SELECT foo AS bar, bar * 2 AS gleep FROM table; became this in PostgreSQL's logs SELECT foo FROM table; Vewwy Intewesting! I think Microsoft and ODBC might be making extra work for themselves (obviously they are since they are allowing these aliases) Or maybe not. Maybe I can glean something from this. Who knows. --Joel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Converting from MS Access field aliases
Joel Richard <[EMAIL PROTECTED]> writes: > What's really screwy is what I found when I hooked access into my > PostgreSQL database using pgsqlODBC (I know, it's an abomination) and > I logged the statements that PostgreSQL was processing. In MS Access > this query: >SELECT foo AS bar, bar * 2 AS gleep FROM table; > became this in PostgreSQL's logs >SELECT foo FROM table; > Vewwy Intewesting! Yeah, that *is* interesting --- it means Access is trying to do all the arithmetic for itself. I guess this means that you could keep using the statements unchanged if you wanted to use Access as a frontend to Postgres. Of course you're trying to get away from that, but this might at least give you a chance of fixing things incrementally instead of having to fix everything all at once. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Converting from MS Access field aliases
Joel, To avoid ms access from managing the query, use a pass-through query. Then access will send the raw sql statment to psql and psql will return just the results to access. It will speed things up a bit too for large datasets. Phillip allen Sent via BlackBerry by AT&T -Original Message- From: Joel Richard <[EMAIL PROTECTED]> Date: Thu, 12 Jul 2007 13:36:05 To:Tom Lane <[EMAIL PROTECTED]> Cc:sql pgsql Subject: Re: [SQL] Converting from MS Access field aliases On Jul 12, 2007, at 1:23 PM, Tom Lane wrote: > chester c young <[EMAIL PROTECTED]> writes: >>> SELECT field1 / 2 AS foo, >>> field2 * 2 AS bar, >>> foo + bar AS total >>> WHERE foo < 12; > >> First, I think it would be great if this worked - like the alias >> to an >> update table added in 8.2 - saves a lot of typing and makes queries >> much more readable. > > This is not an "extension", it is *directly* contrary to both the > letter > and spirit of the SQL standard. I can hardly believe that M$ did that > ... oh, actually, I can entirely believe it. The OP has a serious > problem of vendor lockin now, and that's exactly what M$ wants. > > regards, tom lane Hear hear! What's really screwy is what I found when I hooked access into my PostgreSQL database using pgsqlODBC (I know, it's an abomination) and I logged the statements that PostgreSQL was processing. In MS Access this query: SELECT foo AS bar, bar * 2 AS gleep FROM table; became this in PostgreSQL's logs SELECT foo FROM table; Vewwy Intewesting! I think Microsoft and ODBC might be making extra work for themselves (obviously they are since they are allowing these aliases) Or maybe not. Maybe I can glean something from this. Who knows. --Joel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] Converting from MS Access field aliases
On Thu, 2007-07-12 at 13:23 -0400, Tom Lane wrote: > chester c young <[EMAIL PROTECTED]> writes: > >> SELECT field1 / 2 AS foo, > >> field2 * 2 AS bar, > >> foo + bar AS total > >> WHERE foo < 12; > > First, I think it would be great if this worked - like the alias to an > > update table added in 8.2 - saves a lot of typing and makes queries > > much more readable. > This is not an "extension", it is *directly* contrary to both the letter > and spirit of the SQL standard. I can hardly believe that M$ did that > ... oh, actually, I can entirely believe it. The OP has a serious > problem of vendor lockin now, and that's exactly what M$ wants. "AS" works in Informix, and I believe, in DB2 as well. So it is at least pretty common; I'm not saying it is correct. Since Informix predates M$-SQL they at least didn't invent it. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Query Plan
Hi,
I have a complicated view joining 3 tables.
Here are the run times:
LOG: duration: 3380.672 ms statement: explain analyze SELECT * from
vtradeblock where FirmClearingid = 'FIRMA' and status = 1;
LOG: duration: 3784.152 ms statement: explain analyze SELECT * from
vtradeblock where date_trunc('day',tradedate) = '20070703';
LOG: duration: 19631.958 ms statement: EXPLAIN ANALYZE SELECT * from
vtradeblock where FirmClearingid = 'ALLIANCE' and status = 1 and
date_trunc('day', tradedate) = '20070703';
When I run the query with combination of FirmClearingID & status the run
times are approx 3700ms.
But when I add tradedate ie date_trunc('day', tradedate) = '20070703' the
run time becomes a horrendous 19631.958 ms.
I am displaying the query plan from Explain Analyze:
Query 1 (without date_trunc)
Hash Left Join (cost=103783.42..104890.34 rows=154 width=992) (actual
time=3744.962..3781.749 rows=149 loops=1)
Hash Cond: (t.blockid = a.blockid)
-> Nested Loop (cost=17359.18..18450.32 rows=154 width=912) (actual
time=840.575..842.620 rows=149 loops=1)
-> HashAggregate (cost=17359.18..17366.50 rows=154 width=356)
(actual time=840.489..841.018 rows=149 loops=1)
-> Seq Scan on tradetbl (cost=0.00..17309.26 rows=1536
width=356) (actual time=828.272..838.045 rows=406 loops=1)
Filter: (date_trunc('day'::text,
(date_trunc('day'::text, exectime) +
'12:00:00'::interval)) = '2007-07-03
00:00:00'::timestamp without time zone)
-> Index Scan using tradeblocktbl_pkey on tradeblocktbl tr
(cost=0.00..7.02 rows=1 width=618) (actual time=0.006..0.007
rows=1 loops=149)
Index Cond: (t.blockid = tr.recid)
-> Hash (cost=86423.74..86423.74 rows=200 width=84) (actual
time=2873.368..2873.368 rows=118694 loops=1)
-> Subquery Scan a (cost=0.00..86423.74 rows=200 width=84)
(actual time=0.363..2643.006 rows=118694 loops=1)
-> GroupAggregate (cost=0.00..86421.74 rows=200 width=32)
(actual time=0.359..2461.812 rows=118694 loops=1)
-> GroupAggregate (cost=0.00..85417.24 rows=4
width=12) (actual time=0.279..1825.833 rows=118696
loops=1)
-> Index Scan using k_alloctbl_blockid_status
on alloctbl (cost=0.00..73516.91 rows=588590
width=12) (actual time=0.069..871.672
rows=588590 loops=1)
Total runtime: 3782.349 ms
(14 rows)
Query Plan 2 (with date trunc)
Nested Loop Left Join (cost=4269.91..90708.79 rows=1 width=992) (actual
time=2455.184..19629.407 rows=8 loops=1)
Join Filter: (t.blockid = a.blockid)
Filter: (CASE WHEN ((a.qty_ready = 0) AND (a.qty_submitted = 0)) THEN 0
WHEN ((a.qty_ready = 0) AND (a.qty_submitted = t.netshares)) THEN 1
WHEN (a.qty_ready > 0) THEN 2 WHEN ((a.qty_submitted > 0) AND
(a.qty_ready = 0) AND (a.qty_submitted < t.netshares)) THEN 3 ELSE 0
END = 1)
-> Nested Loop (cost=4269.91..4278.01 rows=1 width=912) (actual
time=28.149..28.674 rows=8 loops=1)
-> HashAggregate (cost=4269.91..4269.96 rows=1 width=356)
(actual time=28.073..28.151 rows=8 loops=1)
-> Bitmap Heap Scan on tradetbl (cost=31.56..4269.65
rows=8 width=356) (actual time=27.193..27.878 rows=20
loops=1)
Recheck Cond: ((firmclearingid)::text = 'FIRMA'::text)
Filter: (date_trunc('day'::text,
(date_trunc('day'::text, exectime) +
'12:00:00'::interval)) = '2007-07-03
00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on k_tradetbl_firmclearingid
(cost=0.00..31.56 rows=1536 width=0) (actual
time=1.201..1.201 rows=2643 loops=1)
Index Cond: ((firmclearingid)::text =
'FIRMA'::text)
-> Index Scan using tradeblocktbl_pkey on tradeblocktbl tr
(cost=0.00..8.03 rows=1 width=618) (actual time=0.052..0.055
rows=1 loops=8)
Index Cond: (t.blockid = tr.recid)
-> GroupAggregate (cost=0.00..86421.74 rows=200 width=32) (actual
time=0.300..2363.811 rows=118694 loops=8)
-> GroupAggregate (cost=0.00..85417.24 rows=4 width=12)
(actual time=0.244..1766.637 rows=118696 loops=8)
-> Index Scan using k_alloctbl_blockid_status on alloctbl
(cost=0.00..73516.91 rows=588590 width=12) (actual
time=0.060..854.834 rows=588590 loops=8)
Total runtime: 19629.777 ms
(16 rows)
The Trade table is being indexed by symbol, side, firmclearingid.
Even when i add an index to the date, ie date_trunc, it is not helping.
Any suggestions?
Thanks,
Radhika
--
It is all a matter of perspective. You choose your view by choosing where
to stand.
Larry Wall
---
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Converting from MS Access field aliases
"Adam Tauno Williams" <[EMAIL PROTECTED]> writes: > "AS" works in Informix, and I believe, in DB2 as well. So it is at > least pretty common; I'm not saying it is correct. Since Informix > predates M$-SQL they at least didn't invent it. AS works in Postgres too. But the defined aliases are only in the resulting output columns, not in scope for the where clause. In fact the expressions used aren't even evaluated for rows which don't match the where clause which is pretty important if those expressions are subqueries or volatile functions. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Query Plan
"Radhika Sambamurti" <[EMAIL PROTECTED]> writes:
> When I run the query with combination of FirmClearingID & status the run
> times are approx 3700ms.
> But when I add tradedate ie date_trunc('day', tradedate) = '20070703' the
> run time becomes a horrendous 19631.958 ms.
I'm not really able to make heads or tails of your plans without the query.
But any where clause of the form date_trunc('...',col)='...' will always
generate crappy plans. And it looks like you already have expressions of that
form in the view even in the one which you label "without date_trunc".
Instead try to write a where clause that the database can understand the end
points of. Something like (col >= '20070703' and col < '20070704'). That's
something the database can apply a normal index to and also something it can
have a chance at guessing how many rows will fit.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Converting from MS Access field aliases
Adam Tauno Williams <[EMAIL PROTECTED]> writes: > On Thu, 2007-07-12 at 13:23 -0400, Tom Lane wrote: >> chester c young <[EMAIL PROTECTED]> writes: >>> SELECT field1 / 2 AS foo, >>> field2 * 2 AS bar, >>> foo + bar AS total >>> WHERE foo < 12; >> This is not an "extension", it is *directly* contrary to both the letter >> and spirit of the SQL standard. > "AS" works in Informix, and I believe, in DB2 as well. I can't speak to Informix, but I will bet a good lunch that it does not work like that in DB2. DB2 is one of the more nearly standard-conformant databases on the planet. (Or possibly it's the other way around ... there seem to be a lot of IBMers on the committee.) regards, tom lane ---(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] Converting from MS Access field aliases
Tom Lane skrev: > This is not an "extension", it is *directly* contrary to both the letter > and spirit of the SQL standard. I can hardly believe that M$ did that > ... oh, actually, I can entirely believe it. The OP has a serious > problem of vendor lockin now, and that's exactly what M$ wants. Don't attribute to malice what can be adequately explained by incompetence. Nis ---(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
