Re: [SQL] Updating one table with data from another
On 2009-08-18, drew wrote: > Hey all, > There are two things I need to do: > 1. Update existing rows with new data > 2. Append new rows > > I need to update only some of the fields table1 with data from > table2. These tables have the exact same fields. > > So here's what I have currently for appending new rows (rows where CID > does not currently exist in table1, but have been added to table2): > INSERT INTO table1 (field1, field2, ...) > SELECT field1, field2, ... > FROM table2 > WHERE NOT EXISTS (SELECT CID FROM table1); > > > But something is wrong with the logic there and I'm not quite getting > it. the where clause is wrong. WHERE NOT EXISTS (SELECT 1 FROM table1 where table1.cid=table2.cid); or WHERE NOT cid IN (SELECT CID FROM table1); -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Advice returning data as an array
I have the following query that works the way I need: SELECT '20090812' AS date, sum(in_oct) AS bytes, sum(in_pkt) AS pkts, 'appl' AS name, ARRAY['appl'] AS fields, CAST((LEAST(src_port, dst_port)) AS varchar) as fieldVal FROM flow_history.flows_20090812 GROUP BY fieldVal ORDER BY bytes DESC LIMIT 20; My issue is that I need to get the fieldVal to return as an array and be fieldVal[1]. Any suggestions? Thanks, Woody -- iGLASS Networks www.iglass.net
[SQL] using case to select 'which version to use'
Hi all. I have 2 functions , which returns the same columns, but with tottaly different logics. I want to wrap it with a third function, who will receive a boolean argument to decide wich sub-function have to use. I want to use case, so i dont have to use pl/pgsql. This is a non-working example: CREATE OR REPLACE FUNCTION get_oferta(varchar, varchar, varchar, bool, OUT cod varchar , OUT description varchar) returns setof record security definer as $$ select * from case $4 when true then (select * from get_oferta_from_a($1, $2, $3)) else (select * from get_oferta_from_b($1, $2, $3)) end; $$ language sql; Can i use case for a case like this? Thanks! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using case to select 'which version to use'
On Thu, Aug 20, 2009 at 10:17 AM, Gerardo Herzig wrote: > Hi all. I have 2 functions , which returns the same columns, but with > tottaly different logics. I want to wrap it with a third function, who > will receive a boolean argument to decide wich sub-function have to use. > > I want to use case, so i dont have to use pl/pgsql. > > This is a non-working example: > > > CREATE OR REPLACE FUNCTION get_oferta(varchar, varchar, varchar, bool, > OUT cod varchar , OUT description varchar) > returns setof record > security definer > as > $$ > select * from case $4 when true then > (select * from get_oferta_from_a($1, $2, $3)) > else > (select * from get_oferta_from_b($1, $2, $3)) > end; > $$ language sql; > > Can i use case for a case like this? > > Thanks! > Gerardo Try: SELECT * FROM get_oferta_from_a ($1, $2, $3) WHERE $4 UNION ALL SELECT * FROM get_oferta_from_b ($1, $2, $3) WHERE NOT $4; -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Rewrite without correlated subqueries
Hi All, I'm having some trouble wrapping my head around the syntax to rewrite a query using correlated subqueries, to using outer joins etc. The query: SELECT ps.userid, SUM( ps.hits ) as numhits FROM primarystats AS ps INNER JOIN camp ON camp.id = ps.idcamp INNER JOIN sites ON sites.id = ps.idsite WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE sitescategory.idsites = ps.idsites ) AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE sitescategory.idsites = ps.idsites ) GROUP BY ps.userid; Because I am rewriting this query to use Greenplum, I cannot use correlated subqueries (they are not currently supported). Can anyone suggest a version that will garner the same results? I tried with OUTER JOINS and some IS NULLs, but I couldn't get it right. Thanks! bricklen
Re: [SQL] Rewrite without correlated subqueries
Try putting your subqueries into temporary tables, first, inside a BEGIN ... COMMIT block. But your subqueries would produce the negative, i.e., everything except where sitescategory.idsites = ps.idsites. Then reference these temp tables in your query with inner or outer joins as appropriate. Your new query would not include the ... IN ( ) syntax... Mark bricklen wrote: Hi All, I'm having some trouble wrapping my head around the syntax to rewrite a query using correlated subqueries, to using outer joins etc. The query: SELECT ps.userid, SUM( ps.hits ) as numhits FROM primarystats AS ps INNER JOIN camp ON camp.id = ps.idcamp INNER JOIN sites ON sites.id = ps.idsite WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE sitescategory.idsites = ps.idsites ) AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE sitescategory.idsites = ps.idsites ) GROUP BY ps.userid; Because I am rewriting this query to use Greenplum, I cannot use correlated subqueries (they are not currently supported). Can anyone suggest a version that will garner the same results? I tried with OUTER JOINS and some IS NULLs, but I couldn't get it right. Thanks! bricklen
Re: [SQL] Rewrite without correlated subqueries
Interesting idea. Preferably this operation could be done in straight SQL in a single transaction, to fit in with the way our application works, but if that's not possible I may need to go the temporary table route. On Thu, Aug 20, 2009 at 1:40 PM, Mark Fenbers wrote: > Try putting your subqueries into temporary tables, first, inside a BEGIN > ... COMMIT block. But your subqueries would produce the negative, i.e., > everything except where sitescategory.idsites = ps.idsites. Then reference > these temp tables in your query with inner or outer joins as appropriate. > Your new query would not include the ... IN ( ) syntax... > > Mark > > > bricklen wrote: > > Hi All, > > I'm having some trouble wrapping my head around the syntax to rewrite a > query using correlated subqueries, to using outer joins etc. > > The query: > > SELECT ps.userid, >SUM( ps.hits ) as numhits > FROM primarystats AS ps > INNER JOIN camp ON camp.id = ps.idcamp > INNER JOIN sites ON sites.id = ps.idsite > WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE > sitescategory.idsites = ps.idsites ) > AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE > sitescategory.idsites = ps.idsites ) > GROUP BY ps.userid; > > Because I am rewriting this query to use Greenplum, I cannot use correlated > subqueries (they are not currently supported). > > Can anyone suggest a version that will garner the same results? I tried > with OUTER JOINS and some IS NULLs, but I couldn't get it right. > > Thanks! > > bricklen > > >
Re: [SQL] Rewrite without correlated subqueries
On Thu, Aug 20, 2009 at 3:16 PM, bricklen wrote: > Interesting idea. Preferably this operation could be done in straight SQL in > a single transaction, to fit in with the way our application works, but if > that's not possible I may need to go the temporary table route. Temp tables can be included in a transaction, and they're not visible to other connections. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rewrite without correlated subqueries
On Thu, Aug 20, 2009 at 2:59 PM, Scott Marlowe wrote: > On Thu, Aug 20, 2009 at 3:16 PM, bricklen wrote: > > Interesting idea. Preferably this operation could be done in straight SQL > in > > a single transaction, to fit in with the way our application works, but > if > > that's not possible I may need to go the temporary table route. > > Temp tables can be included in a transaction, and they're not visible > to other connections. > Yeah I know, but I was thinking more of replacing this query with vanilla SQL. Maybe that's just not be feasible.
Re[SQL] write multiple joins...
Hey Folks, I have a query that seems like it will never end. I'm hoping you can help me rewrite it. I have 4 tables that contain information about house sales within the state (sale, taxes, property, buyer). Each table has an 'id' field that links each record. I am just trying to flatten these files so I will have all of the information in a single file. Below is my attempt to do so. Is there a more efficient way to rewrite the joins? select * from sale, taxes, property, buyer where sale.id = taxes.id and sale.id = property.id and sale.id = buyer.id I'm using postgresql 8.3 on a Windows 7 machine. Thank You, -Tripp -- View this message in context: http://www.nabble.com/Rewrite-multiple-joins...-tp25073534p25073534.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
