[GENERAL] Calculate a quotient for a count of boolean values (true or false)

2013-06-11 Thread Alexander Farber
Hello! In a PostgreSQL 8.4.13 why doesn't this please deliver a floating value (a quotient between 0 and 1): select id, count(nullif(nice, false)) - count(nullif(nice, true)) / count(nice) as rating from pref_rep where nice is not null group by id ; id

Re: [GENERAL] Calculate a quotient for a count of boolean values (true or false)

2013-06-11 Thread Alban Hertroys
Because you're calculating a - (b/c) instead of (a-b)/c On 11 June 2013 11:51, Alexander Farber wrote: > Hello! > > In a PostgreSQL 8.4.13 why doesn't this please > deliver a floating value (a quotient between 0 and 1): > > select > id, > count(nullif(nice, false)) - count(null

Re: [GENERAL] Calculate a quotient for a count of boolean values (true or false)

2013-06-11 Thread Alexander Farber
Hello, On Tue, Jun 11, 2013 at 11:59 AM, Alban Hertroys wrote: > Because you're calculating a - (b/c) instead of (a-b)/c > On 11 June 2013 11:51, Alexander Farber wrote: >> http://stackoverflow.com/questions/17040692/calculate-a-quotient-in-one-table-and-store-it-in-another-table right, sorry!

Re: [GENERAL] Calculate a quotient for a count of boolean values (true or false)

2013-06-11 Thread Albe Laurenz
Alexander Farber wrote: > In a PostgreSQL 8.4.13 why doesn't this please > deliver a floating value (a quotient between 0 and 1): > > select > id, > count(nullif(nice, false)) - count(nullif(nice, true)) / > count(nice) as rating > from pref_rep where nice is not null >

Re: [GENERAL] databse version

2013-06-11 Thread Luca Ferrari
On Mon, Jun 10, 2013 at 9:52 AM, Philipp Kraus wrote: > Hello, > > I'm creating a database and I have got a table with a "version" field. Not sure, but if the version field is something like the version row indicator used by some frameworks (e.g., Hibernate), then you are going to place the updat

Re: [GENERAL] Calculate a quotient for a count of boolean values (true or false)

2013-06-11 Thread Vik Fearing
Whoops, forgot to keep it on the list. On 06/11/2013 11:51 AM, Alexander Farber wrote: > Hello! > > In a PostgreSQL 8.4.13 why doesn't this please > deliver a floating value (a quotient between 0 and 1): You should upgrade to 8.4.17. > select > id, > count(nullif(nice, false))

Re: [GENERAL] databse version

2013-06-11 Thread Philipp Kraus
On 2013-06-11 00:42:59 +0200, John R Pierce said: On 6/10/2013 12:52 AM, Philipp Kraus wrote: I'm creating a database and I have got a table with a "version" field. Can I update on structure changes (DDL) like create / update table increment this field automatically? I would like to create a

Re: [GENERAL] Segmentation fault with core dump

2013-06-11 Thread Hiroshi Inoue
Hi, (2013/05/09 1:39), Joshua Berry wrote: | I'm using PG 9.1.9 with a client application using various versions of the | pgsqlODBC driver on Windows. Cursors are used heavily, as well as some pretty | heavy trigger queries on db writes which update several materialized views. | | The server has

Re: [GENERAL] Segmentation fault with core dump

2013-06-11 Thread Tom Lane
Hiroshi Inoue writes: > It's also better to fix the crash at backend side. Yeah, definitely. Do we have a self-contained test case for this? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: h

Re: [GENERAL] INSERT RETURNING with values other than inserted ones.

2013-06-11 Thread David Johnston
Aleksandr Furmanov wrote > Thanks, > However I am not just replicating data from 'a' to 'b', I provided > simplified example, in reality table 'b' keeps data which are going to be > merged into 'a', some rows will be updated, some added. There is some > other work has to be done on 'b' before mergi

[GENERAL] casting tsrange to tstzrange doesn't seem to work?

2013-06-11 Thread Joe Van Dyk
# select tsrange(null)::tstzrange; ERROR: cannot cast type tsrange to tstzrange LINE 1: select tsrange(null)::tstzrange; Is this expected? select null::timestamp::timestamptz; works fine.

[GENERAL] PostgreSQL Presentation at SELF 2013

2013-06-11 Thread Don Parris
Hi all, I am a member of the Charlotte Linux User Group and did a presentation on PostgreSQL at the SouthEast LinuxFest this past weekend. Our LUG table was right next to the PostgreSQL table this year, and I am guessing some of you will find the presentation of some interest. My discussion focu

[GENERAL] Really poor gist index performance with tstzrange types

2013-06-11 Thread Joe Van Dyk
Am I doing something silly? Or is the row-estimation for gist indexes not even close in this case? https://gist.github.com/joevandyk/503cc3d836ee5d101224/raw/c6fc53b2da06849d3d04effbd1c147fc36124245/gistfile1.txtor code below: -- This is not running inside a transaction. drop table if exists f;

Re: [GENERAL] Really poor gist index performance with tstzrange types

2013-06-11 Thread Tom Lane
Joe Van Dyk writes: > Am I doing something silly? Or is the row-estimation for gist indexes not > even close in this case? 9.2 didn't have any logic for estimating range << conditions. I see reasonable estimates for this case in HEAD, though, presumably thanks to work by Alexander Korotkov.

Re: [GENERAL] Really poor gist index performance with tstzrange types

2013-06-11 Thread Sergey Konoplev
On Tue, Jun 11, 2013 at 5:13 PM, Tom Lane wrote: > Joe Van Dyk writes: >> Am I doing something silly? Or is the row-estimation for gist indexes not >> even close in this case? > > 9.2 didn't have any logic for estimating range << conditions. I see > reasonable estimates for this case in HEAD, th

Re: [GENERAL] Segmentation fault with core dump

2013-06-11 Thread Inoue, Hiroshi
(2013/06/12 0:03), Tom Lane wrote: > Hiroshi Inoue writes: >> It's also better to fix the crash at backend side. > > Yeah, definitely. Do we have a self-contained test case for this? Unfortunately no. I'm testing with a modified psqlodbc driver. The simplest way may be as follows. 1. Implement