Sam Mason writes:
> I'm not sure what you're responding to here, but I'm pretty sure the OP
> wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT.
Yeah --- STRICT will prevent inlining. The function's expression
actually is strict, but the planner isn't smart enough about NULLIF
2009/8/2 Sam Mason :
> On Sun, Aug 02, 2009 at 06:03:11PM +0200, Pavel Stehule wrote:
>> 2009/8/2 Sam Mason :
>> > On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote:
>> >> 2009/8/2 Sam Mason :
>> >> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
>> >> >> There is parad
On Sun, Aug 02, 2009 at 06:03:11PM +0200, Pavel Stehule wrote:
> 2009/8/2 Sam Mason :
> > On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote:
> >> 2009/8/2 Sam Mason :
> >> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
> >> >> There is paradox - IMMUTABLE function brea
2009/8/2 Sam Mason :
> On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote:
>> 2009/8/2 Sam Mason :
>> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
>> >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug
>> >
>> > Not in any tests I've done.
>>
On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote:
> 2009/8/2 Sam Mason :
> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
> >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug
> >
> > Not in any tests I've done.
>
> I did it - and in this ca
2009/8/2 Sam Mason :
> On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
>> 2009/8/2 Sam Mason :
>> > On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists
>> > wrote:
>> >> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer
>> >> AS 'SELECT $1 /
On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
> 2009/8/2 Sam Mason :
> > On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists
> > wrote:
> >> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer
> >> AS 'SELECT $1 / NULLIF($2,0);'
> >> LANGUAG
2009/8/2 Sam Mason :
> On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote:
>> -- This routine creates an alterantive division operator
>> -- that doesn't throw an error on a divide by zero
>> -- but rather returns null
>>
>> CREATE OR REPLACE FUNCTION gtpb_divide(integer,
On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote:
> -- This routine creates an alterantive division operator
> -- that doesn't throw an error on a divide by zero
> -- but rather returns null
>
> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer
>
On 31 Jul 2009, at 19:49, Jeff Davis wrote:
Yes -- you can do what you want anyway. If you want it to be more
readable, you can redefine the division operator yourself -- it's
just a
function and operator that happens to be named "/" (although that
might
not be wise). Maybe you can call it "
On Fri, 31 Jul 2009 11:49:47 -0700 Jeff Davis wrote:
> "Find all store locations which have not achieved an average sale price
> of $100."
>
> SELECT store_name FROM sales WHERE totalsales/nsales < 100;
>
> The person running that query might be missing stores with no sales at
> all, and they migh
On Fri, 2009-07-31 at 18:27 +0100, Oliver Kohll - Mailing Lists wrote:
> Divide by zero errors have come up a couple of times on this list
> (once raised by me). I wonder if I could propose a feature for
> discussion. Could the result of a division by zero be treated as
> infinity or null, rather t
Hello,
Divide by zero errors have come up a couple of times on this list
(once raised by me). I wonder if I could propose a feature for
discussion. Could the result of a division by zero be treated as
infinity or null, rather than raising an error? Floating point types
already have the co
On 4 Jun 2009, at 13:11, Sam Mason wrote:
You need to take care of only one case here: denominator == 0; rest
of the
cases will be handled sanely by the database.
CASE WHEN packet_size = 0 THEN null ELSE cost/packet_size END as
unit_cost
Yes; or even shorter:
cost/nullif(packet_size,
On Thu, Jun 04, 2009 at 11:17:39AM -0400, Tom Lane wrote:
> Sam Mason writes:
> > I think that with 8.3 at least I'm going to carry on putting
> > IMMUTABLE on where I think it should be. Consider:
>
> there are corner cases where it's useful to have the function
> marked correctly rather than sl
Sam Mason writes:
> Hum, I think that with 8.3 at least I'm going to carry on putting
> IMMUTABLE on where I think it should be. Consider:
> CREATE FUNCTION fn_i(INTEGER) RETURNS BOOLEAN LANGUAGE SQL IMMUTABLE AS $$
> SELECT $1 < 1000 $$;
> CREATE FUNCTION fn_v(INTEGER) RETURNS BOOLEAN L
On Thu, Jun 04, 2009 at 10:34:31AM -0400, Tom Lane wrote:
> create function foo(int) returns int as
> $$ select coalesce($1, 42); $$ language sql strict;
>
> Because this function is declared STRICT, it must return null on null
> input. However, the contained expression would not act that way ---
Sam Mason writes:
> On Thu, Jun 04, 2009 at 09:48:17AM -0400, Tom Lane wrote:
>> Actually, if you're intending that a SQL function be inline-able then
>> it's best *not* to mark it as IMMUTABLE (nor STRICT either). If the
>> marking matches the behavior of the contained expression then it
>> does
On Thu, Jun 04, 2009 at 09:48:17AM -0400, Tom Lane wrote:
> Sam Mason writes:
> > If it's an SQL function and marked as IMMUTABLE it should (I believe
> > anyway) get inlined somewhere along the line and take no more overhead
> > than writing it out in full.
>
> Actually, if you're intending that
Sam Mason writes:
> If it's an SQL function and marked as IMMUTABLE it should (I believe
> anyway) get inlined somewhere along the line and take no more overhead
> than writing it out in full.
Actually, if you're intending that a SQL function be inline-able then
it's best *not* to mark it as IMMU
On Thu, Jun 04, 2009 at 12:12:09AM +0530, Gurjeet Singh wrote:
> On Wed, Jun 3, 2009 at 9:18 PM, Oliver Kohll wrote:
> > SELECT cost / pack_size AS unit_cost from products;
> >
> > Either variable could be null or zero.
> >
> > I don't want to write new functions, I'd rather keep it in plain SQL.
>
On Wed, Jun 3, 2009 at 9:18 PM, Oliver Kohll - Mailing Lists <
oliver.li...@gtwm.co.uk> wrote:
> Hello,
>
> We have a system that allows users to create views containing calculations
> but divisions by zero are commonly a problem.
>
> An simple example calculation in SQL would be
>
> SELECT cost /
Hello,
We have a system that allows users to create views containing
calculations but divisions by zero are commonly a problem.
An simple example calculation in SQL would be
SELECT cost / pack_size AS unit_cost from products;
Either variable could be null or zero.
I don't think there's a w
Bernard Grosperrin wrote:
Oisin
SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) /
(sold_parts_amount_dly + sold_labor_amount_dly) from sales where
(sold_parts_amount_dly + sold_labor_amount_dly)>0
Thanks for your answer.
The real request would be something like this:
SELE
Bernard Grosperrin wrote:
I wants to make a view giving me some statistics.
I am not sure to understand why something like this
SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) /
(sold_parts_amount_dly + sold_labor_amount_dly) from sales
give me a division by zero error?
If
I wants to make a view giving me some statistics.
I am not sure to understand why something like this
SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) /
(sold_parts_amount_dly + sold_labor_amount_dly) from sales
give me a division by zero error?
If that is not the way to go,
Bernard Grosperrin wrote:
> I wants to make a view giving me some statistics.
>
> I am not sure to understand why something like this
>
> SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) /
> (sold_parts_amount_dly + sold_labor_amount_dly) from sales
>
> give me a division by zer
t; <[EMAIL PROTECTED]>
To:
Sent: Saturday, April 16, 2005 9:15 AM
Subject: [GENERAL] Division by zero
>
> In other SQL programs a division by zero is solved by transforming the
> result to NULL. How can I make postgres have the same behaviour without
> using CASE ?
>
>
>
On Sat, Apr 16, 2005 at 10:15:55AM +0300, Costin Manda wrote:
>
> In other SQL programs a division by zero is solved by transforming the
> result to NULL.
The SQL standards state that "If the value of a divisor is zero,
then an exception condition is raised: data exception -- division
by zero."
In other SQL programs a division by zero is solved by transforming the
result to NULL. How can I make postgres have the same behaviour without
using CASE ?
---(end of broadcast)---
TIP 8: explain analyze is your friend
Greg Donald <[EMAIL PROTECTED]> writes:
> On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
> <[EMAIL PROTECTED]> wrote:
>> Add :
>> AND count(user_tasks.task_id) > 0 in the where clause.
> I get the error:
> aggregates not allowed in WHERE clause
You need to put it in HAVING, instead.
Note
On Wed, Sep 15, 2004 at 12:23:55PM -0500, Greg Donald wrote:
> On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
> <[EMAIL PROTECTED]> wrote:
> > Add :
> >
> > AND count(user_tasks.task_id) > 0 in the where clause.
>
> I get the error:
> aggregates not allowed in WHERE clause
HAVING count(us
Greg Donald wrote:
> I get the error:
> aggregates not allowed in WHERE clause
Try HAVING then.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http:
On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
<[EMAIL PROTECTED]> wrote:
> Add :
>
> AND count(user_tasks.task_id) > 0 in the where clause.
I get the error:
aggregates not allowed in WHERE clause
--
Greg Donald
http://gdconsultants.com/
http://destiney.com/
---
Maybe try something like this :
SELECT
task_id,
CASE
WHEN task_count = '0'
THEN '0'::int4
ELSE (task_duration *
task_duration_type /
task_count) as hours_allocated
END
FROM
(SELECT
task_id,
task_duration,
task_duration_type,
count(user_tasks.task_id) as task_count
FRO
Add :
AND count(user_tasks.task_id) > 0 in the where clause.
Greg Donald wrote:
Converting some MySQL code to work with Postgres here.
I have this query:
SELECT
tasks.task_id,
(tasks.task_duration * tasks.task_duration_type /
count(user_tasks.task_id)) as hours_allocated
FROM tasks
LEFT JOIN us
Converting some MySQL code to work with Postgres here.
I have this query:
SELECT
tasks.task_id,
(tasks.task_duration * tasks.task_duration_type /
count(user_tasks.task_id)) as hours_allocated
FROM tasks
LEFT JOIN user_tasks
ON tasks.task_id = user_tasks.task_id
WHERE tasks.task_milestone =
37 matches
Mail list logo
