Re: [SQL] Overlapping Ranges- Query Alternative
Hi, the following works: Create temp table ranges (Groups int, ColumnA int); Insert into ranges Values(2,45); Insert into ranges Values(3,15); Insert into ranges Values(4,25); Insert into ranges Values(5,35); Select Groups, Case when ColumnA between 0 and 19 then 0 when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10) when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10) when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10) when ColumnA>=50 then generate_series(20,50,10) end MinRange from ranges; --or even only Select Groups, CASE WHEN ColumnA < 20 then 0 ELSE generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange from ranges; Best, Andreas Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von Ozer, Pam Gesendet: Donnerstag, 11. November 2010 20:07 An: pgsql-sql@postgresql.org Betreff: [SQL] Overlapping Ranges- Query Alternative I have the following problem: Create temp table ranges (Groups int, ColumnA int); Insert into ranges Values(2,45); Select Groups, Case when ColumnA between 0 and 19 then 0 when ColumnA >=20 then 20 when ColumnA >=30 then 30 when ColumnA>=40 then 40 when ColumnA>=50 then 50 end MinRange from ranges Results: Groups minrange 2;20 What I want Is : One column can fall into multiple ranges. For example 45 >20, 30, and 40 so I want the following results 2;20 2;30 2;40 I know I could do a union with each range but is there any way to bring back all ranges in one query? I need to bring back the values in one column so having separate columns for each range is not an option. Thank you in advance for any help Pam Ozer
Re: [SQL] Overlapping Ranges- Query Alternative
or: Select Groups, generate_series FROM ranges JOIN generate_series(10,50,10) on ( ColumnA < generate_series) ORDER by Groups , generate_series ; regards, Marc Mamin From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Gaab Sent: Freitag, 12. November 2010 09:23 To: 'Ozer, Pam'; pgsql-sql@postgresql.org Subject: Re: [SQL] Overlapping Ranges- Query Alternative Hi, the following works: Create temp table ranges (Groups int, ColumnA int); Insert into ranges Values(2,45); Insert into ranges Values(3,15); Insert into ranges Values(4,25); Insert into ranges Values(5,35); Select Groups, Case when ColumnA between 0 and 19 then 0 when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10) when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10) when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10) when ColumnA>=50 then generate_series(20,50,10) end MinRange from ranges; --or even only Select Groups, CASE WHEN ColumnA < 20 then 0 ELSE generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange from ranges; Best, Andreas Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von Ozer, Pam Gesendet: Donnerstag, 11. November 2010 20:07 An: pgsql-sql@postgresql.org Betreff: [SQL] Overlapping Ranges- Query Alternative I have the following problem: Create temp table ranges (Groups int, ColumnA int); Insert into ranges Values(2,45); Select Groups, Case when ColumnA between 0 and 19 then 0 when ColumnA >=20 then 20 when ColumnA >=30 then 30 when ColumnA>=40 then 40 when ColumnA>=50 then 50 end MinRange from ranges Results: Groups minrange 2;20 What I want Is : One column can fall into multiple ranges. For example 45 >20, 30, and 40 so I want the following results 2;20 2;30 2;40 I know I could do a union with each range but is there any way to bring back all ranges in one query? I need to bring back the values in one column so having separate columns for each range is not an option. Thank you in advance for any help Pam Ozer
Re: [SQL] "slow lock" log in addition to log_min_duration_statement ?
On Wed, Nov 10, 2010 at 12:28:46PM -0800, Bryce Nesbitt wrote: > I have a cluster with log_min_duration_statement set to log slow > queries. Presently what I'm tracking down is almost certainly a lock > problem. Is there any analog of log_min_duration_statement for locks? > If there is a lock on a certain critical tables for more than a few > hundredths of a second I want to know. You could try log_lock_waits: http://www.postgresql.org/docs/8.3/interactive/runtime-config-logging.html#GUC-LOG-LOCK-WAITS It's defined in terms of deadlock_timeout, and reducing that to "a few hundredths of a second" like you're interested in might cause all kinds of load from the deadlock detector. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [SQL] Overlapping Ranges- Query Alternative
Thank you all for your suggestions. I will try each of these and see which one fits my situation best. From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Gaab Sent: Friday, November 12, 2010 12:23 AM To: Ozer, Pam; pgsql-sql@postgresql.org Subject: Re: [SQL] Overlapping Ranges- Query Alternative Hi, the following works: Create temp table ranges (Groups int, ColumnA int); Insert into ranges Values(2,45); Insert into ranges Values(3,15); Insert into ranges Values(4,25); Insert into ranges Values(5,35); Select Groups, Case when ColumnA between 0 and 19 then 0 when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10) when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10) when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10) when ColumnA>=50 then generate_series(20,50,10) end MinRange from ranges; --or even only Select Groups, CASE WHEN ColumnA < 20 then 0 ELSE generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange from ranges; Best, Andreas Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von Ozer, Pam Gesendet: Donnerstag, 11. November 2010 20:07 An: pgsql-sql@postgresql.org Betreff: [SQL] Overlapping Ranges- Query Alternative I have the following problem: Create temp table ranges (Groups int, ColumnA int); Insert into ranges Values(2,45); Select Groups, Case when ColumnA between 0 and 19 then 0 when ColumnA >=20 then 20 when ColumnA >=30 then 30 when ColumnA>=40 then 40 when ColumnA>=50 then 50 end MinRange from ranges Results: Groups minrange 2;20 What I want Is : One column can fall into multiple ranges. For example 45 >20, 30, and 40 so I want the following results 2;20 2;30 2;40 I know I could do a union with each range but is there any way to bring back all ranges in one query? I need to bring back the values in one column so having separate columns for each range is not an option. Thank you in advance for any help Pam Ozer
[SQL] psql -f COPY from STDIN
The following command works fine when pasing it to psql via the -c option: cat event.csv | \ psql -c "COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS ''" When executed from a file via -f, it does nothing (no error messages either): event.sql: COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '' cat event.csv | psql -f event.sql What's the problem? Many thanks in advance. -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql -f COPY from STDIN
On 11/12/2010 02:03 PM, Tarlika Elisabeth Schmitz wrote: The following command works fine when pasing it to psql via the -c option: cat event.csv | \ psql -c "COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS ''" When executed from a file via -f, it does nothing (no error messages either): event.sql: COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '' COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS ''; ^ cat event.csv | psql -f event.sql What's the problem? Many thanks in advance. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql -f COPY from STDIN
Tarlika Elisabeth Schmitz writes: > The following command works fine when pasing it to psql via the -c > option: > cat event.csv | \ > psql -c "COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL > AS ''" > When executed from a file via -f, it does nothing (no error messages > either): > event.sql: > COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '' > cat event.csv | psql -f event.sql I believe that psql's interpretation of "stdin" when reading from a file is that the COPY data is to come from that same file (look at the script produced by pg_dump for an example). So it reads to the end of the file, which is right away --- otherwise you'd get some incorrect-data errors. The data sourced from the cat command is never noticed at all. I think you can get the effect you're after using \copy ... from pstdin. See the psql man page. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
