[SQL] conditional aggregates
Hi list, Can anyone advise me on creating an aggregate that would take additional parameter as a condition ? For example, say I have a table like this id;value 1;45 2;13 3;0 4;90 I'd like to do something like this SELECT min_some_cond_aggregate(value,0) FROM table to get the minimal value from table greater than 0, in this case 13. I can't do SELECT min(value) FROM table WHERE value > 0 as this will mess my other computations. My current solution involves a function operating on the output of array_accum from the docs, but I'm looking for more elegant solution. Is this possible at all ? I'm running 8.1. regards mk
Re: [SQL] conditional aggregates
Hello use a CASE statement http://www.postgresql.org/docs/7.4/static/functions-conditional.html Regards Pavel Stehule 2010/12/8 Marcin Krawczyk : > Hi list, > Can anyone advise me on creating an aggregate that would take additional > parameter as a condition ? For example, say I have a table like this > id;value > 1;45 > 2;13 > 3;0 > 4;90 > I'd like to do something like this > SELECT min_some_cond_aggregate(value,0) FROM table > to get the minimal value from table greater than 0, in this case 13. > I can't do SELECT min(value) FROM table WHERE value > 0 as this will mess my > other computations. My current solution involves a function operating on the > output of array_accum from the docs, but I'm looking for more elegant > solution. > Is this possible at all ? I'm running 8.1. > > regards > mk > -- 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] conditional aggregates
Yeah I know but I'm having problems creating sfunc fuction for the aggregate. regards mk 2010/12/8 Pavel Stehule > Hello > > use a CASE statement > > http://www.postgresql.org/docs/7.4/static/functions-conditional.html > > Regards > > Pavel Stehule > > 2010/12/8 Marcin Krawczyk : > > Hi list, > > Can anyone advise me on creating an aggregate that would take additional > > parameter as a condition ? For example, say I have a table like this > > id;value > > 1;45 > > 2;13 > > 3;0 > > 4;90 > > I'd like to do something like this > > SELECT min_some_cond_aggregate(value,0) FROM table > > to get the minimal value from table greater than 0, in this case 13. > > I can't do SELECT min(value) FROM table WHERE value > 0 as this will mess > my > > other computations. My current solution involves a function operating on > the > > output of array_accum from the docs, but I'm looking for more elegant > > solution. > > Is this possible at all ? I'm running 8.1. > > > > regards > > mk > > >
[SQL] Regular Expression Match Operator escape character
Hi, We're running PostgreSQL v8.2.3 on RHEL5. In some places in our application, we use Regular Expression Match Operator (~* => Matches regular expression, case insensitive) inside WHERE criteria. Example: SELECT ... FROM ... WHERE (SKILLS ~* '(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)C#(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)' OR SKILLS ~* '(^|\\^|\\||[^0-9|^a-z|^A-Z]|$).NET(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)') In this case, we're trying to search/match for either "C#" OR ".NET" in SKILLS column. My question here is, do I need to escape the characters "#" and "." here? Regards, Gnanam -- 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] conditional aggregates
something like ? Select min (case when X > 0 then X end) HTH, Marc Mamin From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Marcin Krawczyk Sent: Mittwoch, 8. Dezember 2010 14:20 To: Pavel Stehule Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] conditional aggregates Yeah I know but I'm having problems creating sfunc fuction for the aggregate. regards mk 2010/12/8 Pavel Stehule Hello use a CASE statement http://www.postgresql.org/docs/7.4/static/functions-conditional.html Regards Pavel Stehule 2010/12/8 Marcin Krawczyk : > Hi list, > Can anyone advise me on creating an aggregate that would take additional > parameter as a condition ? For example, say I have a table like this > id;value > 1;45 > 2;13 > 3;0 > 4;90 > I'd like to do something like this > SELECT min_some_cond_aggregate(value,0) FROM table > to get the minimal value from table greater than 0, in this case 13. > I can't do SELECT min(value) FROM table WHERE value > 0 as this will mess my > other computations. My current solution involves a function operating on the > output of array_accum from the docs, but I'm looking for more elegant > solution. > Is this possible at all ? I'm running 8.1. > > regards > mk >
Re: [SQL] concatenate question
Thanks so much to everyone for your responses. You've been very helpful. I'm running PostGres 8.4 and we're migrating our datawarehouse from Oracle 10.2. I guess datatype is implicitly cast in oracle under these circumstances: SQL> create table tony_test as select memberid||addeddate "data" from TMPSV_PARENT_MASTER where rownum < 5; Table created. SQL> desc tony_test Name Null?Type - data VARCHAR2(59) SQL> select * from tony_test; data --- 3812482212010-06-23 13:53:38 3812510902010-06-23 14:12:25 3812622482010-06-23 15:24:45 3812725152010-06-23 16:35:24 Thanks! On Tue, 2010-12-07 at 16:54 -0500, Peter Steinheuser wrote: > I don't know what Postgres version you're using but check out the doc > related to String Functions and Operators. > Cheers, >Peter > > > On Tue, Dec 7, 2010 at 4:47 PM, Tony Capobianco > wrote: > Ok, that worked. Why did I need to cast both as text though? > > plsql_dw=# select memberid::text||addeddate::text from > tmpsv_parent_master limit 5; > ?column? > -- > 4005941032010-11-16 19:32:17 > 4005941952010-11-16 19:33:29 > 4005942842010-11-16 19:34:32 > 4005943492010-11-16 19:35:22 > 4005943662010-11-16 19:35:37 > (5 rows) > > Thanks. > > On Tue, 2010-12-07 at 16:43 -0500, Peter Steinheuser wrote: > > I think the HINT is what you need to look at. > > > > Cast both columns to text. > > > > On Tue, Dec 7, 2010 at 4:37 PM, Tony Capobianco > > wrote: > > Here's my table: > > > >plsql_dw=# \d tmpsv_parent_master > > Table > "staging.tmpsv_parent_master" > > Column |Type > | > > Modifiers > > > > > +-+--- > > memberid | numeric > | > > addeddate | timestamp without time zone > | > > sourceid | numeric > | > > regcomplete| numeric(1,0) > | > > optoutdate | date > | > > bouncedate | date > | > > websiteid | numeric > | > > emailbounced | numeric(2,0) > | > > emailok| numeric(2,0) > | > > emailaddress | character varying(50) > | > > srcwebsiteid | numeric > | > > srcmemberid| numeric > | > > sitetype | character varying > | > > commissionpct | numeric > | > > pricepermember | numeric > | > > acceptrate | numeric(3,2) > | > > mktgcenterid | numeric > | > > label | character varying(32) > | > > > > > >Why won't this work? > >plsql_dw=# select memberid || addeddate from > > tmpsv_parent_master > > limit > >10; > >ERROR: operator does not exist: numeric || > timestamp > > without > > time zone > >LINE 1: select memberid || addeddate from > > tmpsv_parent_master > > limit ... > >^ > >HINT: No operator matches the given name and > argument > > type(s). > > You > >might need to add explicit type casts. > > > >Thanks. > > > > > > -- > > Sent via pgsql-sql mailing list > (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > > > > > > > -- > > Peter Steinheuser > > psteinheu...@myyearbook.com > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > > > -- > Peter Steinheuser > psteinheu...@myyearbook.com
Re: [SQL] conditional aggregates
Thanks, it working. pozdrowienia mk 2010/12/8 Marc Mamin > something like ? > > > > Select min (case when X > 0 then X end) > > > > > > HTH, > > > > Marc Mamin > > > > *From:* pgsql-sql-ow...@postgresql.org [mailto: > pgsql-sql-ow...@postgresql.org] *On Behalf Of *Marcin Krawczyk > *Sent:* Mittwoch, 8. Dezember 2010 14:20 > *To:* Pavel Stehule > *Cc:* pgsql-sql@postgresql.org > *Subject:* Re: [SQL] conditional aggregates > > > > Yeah I know but I'm having problems creating sfunc fuction for the > aggregate. > > > > > regards > mk > > 2010/12/8 Pavel Stehule > > Hello > > use a CASE statement > > http://www.postgresql.org/docs/7.4/static/functions-conditional.html > > Regards > > Pavel Stehule > > 2010/12/8 Marcin Krawczyk : > > > Hi list, > > Can anyone advise me on creating an aggregate that would take additional > > parameter as a condition ? For example, say I have a table like this > > id;value > > 1;45 > > 2;13 > > 3;0 > > 4;90 > > I'd like to do something like this > > SELECT min_some_cond_aggregate(value,0) FROM table > > to get the minimal value from table greater than 0, in this case 13. > > I can't do SELECT min(value) FROM table WHERE value > 0 as this will mess > my > > other computations. My current solution involves a function operating on > the > > output of array_accum from the docs, but I'm looking for more elegant > > solution. > > Is this possible at all ? I'm running 8.1. > > > > regards > > mk > > > > >
