Re: [GENERAL] Count column with name 'count' returns multiple rows. Why?

On 2017-08-18 15:57:39 -0500, Justin Pryzby wrote: > On Fri, Aug 18, 2017 at 10:47:37PM +0200, Peter J. Holzer wrote: > > On 2017-08-18 06:37:15 -0500, Justin Pryzby wrote: > > > On Fri, Aug 18, 2017 at 01:01:45PM +0200, Rob Audenaerde wrote: > > > > Can anyone please explain this behaviour? > > >

Re: [GENERAL] Count column with name 'count' returns multiple rows. Why?

On Fri, Aug 18, 2017 at 1:47 PM, Peter J. Holzer wrote: > So apparently > ​ ​ > columnname open-parenthesis tablename closed-parenthesis is a specific > syntactic construct, but I can't find it documented anywhere. ​The documentation linked to speaks mainly in terms of "composite types". A tabl

Re: [GENERAL] Count column with name 'count' returns multiple rows. Why?

On Fri, Aug 18, 2017 at 10:47:37PM +0200, Peter J. Holzer wrote: > On 2017-08-18 06:37:15 -0500, Justin Pryzby wrote: > > On Fri, Aug 18, 2017 at 01:01:45PM +0200, Rob Audenaerde wrote: > > > I don't understand why this query: > > > > > >select count(base.*) from mytable base; > > > > > > doe

Re: [GENERAL] Count column with name 'count' returns multiple rows. Why?

On 2017-08-18 06:37:15 -0500, Justin Pryzby wrote: > On Fri, Aug 18, 2017 at 01:01:45PM +0200, Rob Audenaerde wrote: > > I don't understand why this query: > > > >select count(base.*) from mytable base; > > > > does return multiple rows. > > > >select count(1) from mytable base; > > > >

Re: [GENERAL] Count column with name 'count' returns multiple rows. Why?

On Fri, Aug 18, 2017 at 01:01:45PM +0200, Rob Audenaerde wrote: > I don't understand why this query: > >select count(base.*) from mytable base; > > does return multiple rows. > >select count(1) from mytable base; > > returns the proper count. > > There is a column with the name 'count'

[GENERAL] Count column with name 'count' returns multiple rows. Why?

Hi all, I don't understand why this query: select count(base.*) from mytable base; does return multiple rows. select count(1) from mytable base; returns the proper count. There is a column with the name 'count'. Can anyone please explain this behaviour? Steps to reproduce: create tab

Re: [GENERAL] count records in two different table joined by

On Fri, Jul 7, 2017 at 3:49 AM, Patrick B wrote: > I want this to work: > > WITH account_status AS ( > select > CASE > WHEN regdate = 1 THEN 'yes' > WHEN regdate = 2 THEN 'no' > end as status_a, > count(t2.id) as t2_count > from test1 as t1 > join test2 as t2 on t2.test1_id = t1.id > end

Re: [GENERAL] count records in two different table joined by

2017-07-07 22:32 GMT+12:00 Thomas Markus : > Hi, > > Am 07.07.17 um 12:16 schrieb Patrick B: > > Hi guys! > > I've got 2 tables, and I need to get some data between them. > > test1: > > WITH account_status AS ( > select > CASE > WHEN regdate = 1 THEN 'yes' > WHEN regdate = 2 THEN 'no' > from

Re: [GENERAL] count records in two different table joined by

Hi, Am 07.07.17 um 12:16 schrieb Patrick B: Hi guys! I've got 2 tables, and I need to get some data between them. test1: WITH account_status AS ( select CASE WHEN regdate = 1 THEN 'yes' WHEN regdate = 2 THEN 'no' from test1 end as status_a ) select

[GENERAL] count records in two different table joined by

Hi guys! I've got 2 tables, and I need to get some data between them. test1: WITH account_status AS ( select CASE WHEN regdate = 1 THEN 'yes' WHEN regdate = 2 THEN 'no' from test1 end as status_a ) select status_a from account_status group by status_a test2: WITH user_status AS ( sele

Re: [GENERAL] count case when - PG 9.2

if you want see account_status and the count()- try this: SELECT CASE WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 day')) THEN 'trial' WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37 day')) THEN 'paying' END as account_status,

Re: [GENERAL] count case when - PG 9.2

2017-03-10 10:17 GMT+13:00 Yasin Sari : > if you want see account_status and the count()- try this: > > SELECT > > CASE > > WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 > day')) > > THEN 'trial' > > WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37 d

Re: [GENERAL] count case when - PG 9.2

On Thu, Mar 9, 2017 at 2:01 PM, Patrick B wrote: > > Could you please guys give me a query as an example? > > ​Maybe if you describe exactly how you want the output to appear. And maybe tee things up by writing a query that gets close and with some example data. Something that can be copy-paste

Re: [GENERAL] count case when - PG 9.2

2017-03-09 23:15 GMT+13:00 vinny : > On 2017-03-09 05:27, Patrick B wrote: > >> Hi guys. How can I count using 'CASE WHEN'? >> >> Example: >> >> SELECT >>> >>> CASE >>> >>> WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 day')) >>> >>> THEN 'trial' >>> >>> WH

Re: [GENERAL] count case when - PG 9.2

On 2017-03-09 05:27, Patrick B wrote: Hi guys. How can I count using 'CASE WHEN'? Example: SELECT CASE WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 day')) THEN 'trial' WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37 day')) THEN 'paying' END as accoun

Re: [GENERAL] count case when - PG 9.2

On Wednesday, March 8, 2017, Patrick B wrote: > Hi guys. How can I count using 'CASE WHEN'? > > Example: > > SELECT > > CASE > > WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 > day')) > > THEN 'trial' > > WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL

[GENERAL] count case when - PG 9.2

Hi guys. How can I count using 'CASE WHEN'? Example: SELECT CASE WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 day')) THEN 'trial' WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37 day')) THEN 'paying' END as account_status,

Re: [GENERAL] count(*) in binary mode returns 0

imagene...@gmail.com wrote: > nfields: 1 > from_psql bytes_to_read:4 read:4 > host_order bytes_to_read:4 read:4 > conv_int ir:0 Note that count(*) produces a bigint (8 bytes), not an int (4 bytes). => select pg_typeof(coun

Re: [GENERAL] count(*) in binary mode returns 0

"imagene...@gmail.com" writes: > I have an implementation of binary format communication that is working > quite well but it is failing inexplicably on returning valid results for > the aggregate function count. It returns a correct result for instance for > the aggregate function max. > Here is

[GENERAL] count(*) in binary mode returns 0

I have an implementation of binary format communication that is working quite well but it is failing inexplicably on returning valid results for the aggregate function count. It returns a correct result for instance for the aggregate function max. Here is some debug information to illustrate that

Re: [GENERAL] Count of non-null values per table column

On Fri, Aug 14, 2015 at 9:17 PM, Ken Tanzer wrote: > On Fri, Aug 14, 2015 at 6:35 PM, David Nelson > wrote: > >> On Fri, Aug 14, 2015 at 10:00 AM, Tom Lane wrote: >> > >> > David Rowley writes: >> > > >> Tthat is the way I would do it for a table with a small number of >> columns, but these ha

Re: [GENERAL] Count of non-null values per table column

On Fri, Aug 14, 2015 at 6:35 PM, David Nelson wrote: > On Fri, Aug 14, 2015 at 10:00 AM, Tom Lane wrote: > > > > David Rowley writes: > > Tthat is the way I would do it for a table with a small number of columns, > but these have several dozen so this would get tedious. Although I just > real

Re: [GENERAL] Count of non-null values per table column

On Fri, Aug 14, 2015 at 10:00 AM, Tom Lane wrote: > > David Rowley writes: > > On 15 August 2015 at 02:32, David Nelson wrote: > >> Hello list,Apologies if this has been asked before. My search only > >> turned up ways to list the total non-null values for all columns as a > >> single number. I

Re: [GENERAL] Count of non-null values per table column

On Fri, Aug 14, 2015 at 9:59 AM, John McKown wrote: > > David, > > It still came through as junk. But I reconstructed it below > > === original message === > Apologies if this has been asked before. My search only turned up ways to list the total non-null values for all columns as a single number.

Re: [GENERAL] Count of non-null values per table column

David Rowley writes: > On 15 August 2015 at 02:32, David Nelson wrote: >> Hello list,Apologies if this has been asked before. My search only >> turned up ways to list the total non-null values for all columns as a >> single number. I want the count for each column by column. > I assume the table

Re: [GENERAL] Count of non-null values per table column

David, It still came through as junk. But I reconstructed it below === original message === Apologies if this has been asked before. My search only turned up ways to list the total non-null values for all columns as a single number. I want the count for each column by column. I have inherited a

Re: [GENERAL] Count of non-null values per table column

On 15 August 2015 at 02:32, David Nelson wrote: > Hello list,Apologies if this has been asked before. My search only > turned up ways to list the total non-null values for all columns as a > single number. I want the count for each column by column.I have > inherited a database consisting of two

Re: [GENERAL] Count of non-null values per table column

table's column of that same name, for all column names in the table.Thanks> From: dlnelson77...@outlook.com> To: pgsql-general@postgresql.org> Subject: [GENERAL] Count of non-null values per table column> Date: Fri, 14 Aug 2015 14:32:36 +

[GENERAL] Count of non-null values per table column

Hello list,Apologies if this has been asked before. My search only turned up ways to list the total non-null values for all columns as a single number. I want the count for each column by column.I have inherited a database consisting of two related huge monolithic tables that lack referential i

Re: [GENERAL] count distinct and group by

On 7 May 2015 at 11:54, Thomas Kellerer wrote: > Geoff Winkless schrieb am 07.05.2015 um 12:39: > > in Postgres (unlike MySQL) you can't order a list of values by a column > you haven't selected.​ > > Of course you can, just not when you are aggregating. > > ​Doh! I missed out that key clause :)

Re: [GENERAL] count distinct and group by

Geoff Winkless schrieb am 07.05.2015 um 12:39: > in Postgres (unlike MySQL) you can't order a list of values by a column you > haven't selected.​ Of course you can, just not when you are aggregating. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to you

Re: [GENERAL] count distinct and group by

On 7 May 2015 at 12:39, Magnus Hagander wrote: > On Thu, May 7, 2015 at 12:23 PM, Szymon Guz wrote: > >> Hi, >> I'm not sure why there is a reason for such behaviour. >> >> For this table: >> >> create table bg(id serial primary key, t text); >> >> This works: >> >> select count(id) from bg; >>

Re: [GENERAL] count distinct and group by

And this doesn't: select count(distinct id) from bg order by id; ERROR: column "bg.id " must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select count(distinct id) from bg order by id; Your result set will contain one row with the count of distinct i

Re: [GENERAL] count distinct and group by

On 7 May 2015 at 11:23, Szymon Guz wrote: > Hi, > I'm not sure why there is a reason for such behaviour. > > select count(distinct id) from bg order by id; > ERROR: column "bg.id" must appear in the GROUP BY clause or be used in > an aggregate function > LINE 1: select count(distinct id) from bg

Re: [GENERAL] count distinct and group by

On Thu, May 7, 2015 at 12:23 PM, Szymon Guz wrote: > Hi, > I'm not sure why there is a reason for such behaviour. > > For this table: > > create table bg(id serial primary key, t text); > > This works: > > select count(id) from bg; > > This works: > > select count(distinct id) from bg; > > And th

[GENERAL] count distinct and group by

Hi, I'm not sure why there is a reason for such behaviour. For this table: create table bg(id serial primary key, t text); This works: select count(id) from bg; This works: select count(distinct id) from bg; And this doesn't: select count(distinct id) from bg order by id; ERROR: column "bg

Re: [GENERAL] count distinct slow?

Roger Pack writes: > As a note, I ran into the following today (doing a select distinct is fast, > doing a count distinct is significantly slower?) The planner appears to prefer hash aggregation for the variants of your query wherein the DISTINCT becomes a separate plan step. This is evidently a

[GENERAL] count distinct slow?

Hello. As a note, I ran into the following today (doing a select distinct is fast, doing a count distinct is significantly slower?) assume a table "issue" with a COLUMN nodename character varying(64);, 7.5M rows... select distinct substring(nodename from 1 for 9) from issue; -- 5.8s select co

Re: [GENERAL] count on cascading deletes

Eildert Groeneveld wrote > Dear All > > prior to issuing a cascading delete in an interactive program > I would like to retrieve from Postgresql what is involved in the > particular delete, so that this can be printed to the console > and the user can be asked: > >This is what your delete wou

Re: [GENERAL] count on cascading deletes

On 09/05/2014 09:49 AM, Eildert Groeneveld wrote: Dear All prior to issuing a cascading delete in an interactive program I would like to retrieve from Postgresql what is involved in the particular delete, so that this can be printed to the console and the user can be asked: This is what you

Re: [GENERAL] count on cascading deletes

Eildert Groeneveld wrote: > prior to issuing a cascading delete in an interactive program > I would like to retrieve from Postgresql what is involved in the > particular delete, so that this can be printed to the console > and the user can be asked: > > This is what your delete would do in the

[GENERAL] count on cascading deletes

Dear All prior to issuing a cascading delete in an interactive program I would like to retrieve from Postgresql what is involved in the particular delete, so that this can be printed to the console and the user can be asked: This is what your delete would do in the database: deleting panel

Re: [GENERAL] Count of records in a row

Ingenious! I actually think, however, there was a subtle bug in, though I see you fixed it. The line: - row_number() over () as d needs to be: - row_number() over (order by i asc) as d I discovered this when working your code into my application. I got very, very wei

Re: [GENERAL] Count of records in a row

On 2013-10-24 17:09, Robert James wrote: On 10/22/13, Elliot wrote: It looks like you already found a solution, but here's one with a CTE. I cobbled this together from an older query I had for doing something similar, for which I unfortunately lost the original source of this approach. Also, th

Re: [GENERAL] Count of records in a row

On 10/22/13, Elliot wrote: > It looks like you already found a solution, but here's one with a CTE. I > cobbled this together from an older query I had for doing something > similar, for which I unfortunately lost the original source of this > approach. Also, this implies that there is something t

Re: [GENERAL] Count of records in a row

Ok thanks for this precision Merlin. Seems like aggregates are way more powerful than I thought. Obviously I need a lot more reading about custom aggregates before fully understanding it. Elliot's query is pure SQL so obviously very cool ! It could be improved at the margin, and aggregates/funct

Re: [GENERAL] Count of records in a row

> 2013/10/22 Merlin Moncure >> > With a standard loop, I loop n times, and each times I only need the >> > current >> > row plus the previous row which I put in memory, thus O(n). >> >> For posterity, the above is incorrect. Since the aggregate is ordered >> through the window function, it gets e

Re: [GENERAL] Count of records in a row

Wow, this is an excellent discussion - and I must admit, a bit beyond my abilities. Is there a consensus as to the best approach to adopt? Is Elliot's the best? On 10/22/13, Rémi Cura wrote: > OK, > just out of pure curiosity, > is it always the case or is it due to this particular aggregate? >

Re: [GENERAL] Count of records in a row

OK, just out of pure curiosity, is it always the case or is it due to this particular aggregate? Cheers, Rémi-C 2013/10/22 Merlin Moncure > On Tue, Oct 22, 2013 at 9:43 AM, Rémi Cura wrote: > > Thanks again for the precision ! > > > > I still don't understand perfectly. We call the aggregate

Re: [GENERAL] Count of records in a row

On Tue, Oct 22, 2013 at 9:43 AM, Rémi Cura wrote: > Thanks again for the precision ! > > I still don't understand perfectly. We call the aggregate n times, and each > time we compute the aggregate, using (potentially) n rows, thus becoming (at > most) O(n*n). > > With a standard loop, I loop n tim

Re: [GENERAL] Count of records in a row

Hmm exactly what I was thinking ! Thank you a lot, I spend many hours thinking about this and this solution is very nice. Cheers, Rémi-C 2013/10/22 Merlin Moncure > On Tue, Oct 22, 2013 at 10:01 AM, Elliot > wrote: > > On 2013-10-21 20:38, Robert James wrote: > >> > >> I have a table of even

Re: [GENERAL] Count of records in a row

On Tue, Oct 22, 2013 at 10:01 AM, Elliot wrote: > On 2013-10-21 20:38, Robert James wrote: >> >> I have a table of event_id, event_time. Many times, several events >> happen in a row. I'd like a query which replaces all of those events >> with a single record, showing the count. >> >> Eg: Take A

Re: [GENERAL] Count of records in a row

On 2013-10-21 20:38, Robert James wrote: I have a table of event_id, event_time. Many times, several events happen in a row. I'd like a query which replaces all of those events with a single record, showing the count. Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1; D,1; A,

Re: [GENERAL] Count of records in a row

Thanks again for the precision ! I still don't understand perfectly. We call the aggregate n times, and each time we compute the aggregate, using (potentially) n rows, thus becoming (at most) O(n*n). With a standard loop, I loop n times, and each times I only need the current row plus the previou

Re: [GENERAL] Count of records in a row

On Tue, Oct 22, 2013 at 9:09 AM, Rémi Cura wrote: > > Thanks for this good example Merlin ! > > I didn't know you could use variable inside custom aggregates, and this > allow to solve the problem! > > In my own problem I couldn't use aggregates because > _as it output at most one row, it would ha

Re: [GENERAL] Count of records in a row

Thanks for this good example Merlin ! I didn't know you could use variable inside custom aggregates, and this allow to solve the problem! In my own problem I couldn't use aggregates because _as it output at most one row, it would have mean a lots of useless computation (as in this example I guess

Re: [GENERAL] Count of records in a row

On Tue, Oct 22, 2013 at 8:41 AM, Rémi Cura wrote: > héhé, > nice snipping Merlin ! > > I guess you are almost there, output is still wrong (should be) ( >> Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1; >> D,1; A,2; D,2; B,1; C,2 > ) > > I don't understand enough to make the

Re: [GENERAL] Count of records in a row

héhé, nice snipping Merlin ! I guess you are almost there, output is still wrong (should be) ( > Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1; > D,1; A,2; D,2; B,1; C,2 ) I don't understand enough to make the modifications =) Cheers, Rémi-C 2013/10/22 hubert depesz luba

Re: [GENERAL] Count of records in a row

On pon, paź 21, 2013 at 08:38:52 -0400, Robert James wrote: > I have a table of event_id, event_time. Many times, several events > happen in a row. I'd like a query which replaces all of those events > with a single record, showing the count. > > Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return:

Re: [GENERAL] Count of records in a row

On Tue, Oct 22, 2013 at 8:16 AM, Rémi Cura wrote: > Hey, > when using a for you implicitly use a cursor (I think), > so this is the same, use FOR if you like it more. > It should be *very* fast to write ! > > As I wrote, relational algebra can handle it, but it is not practically > feasible : > >

Re: [GENERAL] Count of records in a row

Hey, when using a for you implicitly use a cursor (I think), so this is the same, use FOR if you like it more. It should be *very* fast to write ! As I wrote, relational algebra can handle it, but it is not practically feasible : If you just execute 3 times the query I wrote, you will have your a

Re: [GENERAL] Count of records in a row

On 10/22/13, Rémi Cura wrote: > But it is immensely easier and sometimes mandatory to use instead > a plpgsql function using cursor (or cursors). > > It would be something like that in plpgsql : > > cursor on table of letter ordered > accum = 0; > loop on rows of table ordered > > if letter = prev

Re: [GENERAL] Count of records in a row

Hey, I tried something very similar to compute generalized union of numeric range (which was innapropriate, anyway). My conclusion were that it's not possible using windows function as you need either a memory (windows function are not allowed in update) or iterations to propagate information (win

Re: [GENERAL] Count of records in a row

Robert James wrote > I have a table of event_id, event_time. Many times, several events > happen in a row. I'd like a query which replaces all of those events > with a single record, showing the count. > > Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1; > D,1; A,2; D,2; B,1;

[GENERAL] Count of records in a row

I have a table of event_id, event_time. Many times, several events happen in a row. I'd like a query which replaces all of those events with a single record, showing the count. Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1; D,1; A,2; D,2; B,1; C,2 How can I do that? --

[GENERAL] count number of concurrent requests

Hi, I have the following table: dwh=> \d events Table "public.events" Column |Type | Modifiers --+-+--- datetime | timestamp without time zone | request_duration | integer

Re: [GENERAL] count (DISTINCT field) OVER ()

Tarlika Elisabeth Schmitz, 10.11.2011 11:24: SELECT id, name, delta, sum(case when rn = 1 then rn else null end) over() as distinct_id_count FROM ( SELECT id, name, similarity(name, 'Tooneyvara') as delta, row_number() OVER(partition by id) AS rn

Re: [GENERAL] count (DISTINCT field) OVER ()

On Thu, 10 Nov 2011 10:02:36 +0100 Thomas Kellerer wrote: >Tarlika Elisabeth Schmitz, 10.11.2011 00:52: >> I would like to implement the equivalent of "count (DISTINCT id) >> OVER ()": >> >>[...] >> >> produces result: >> id, name, delta, cnt >> 1787 Toomyvara 0.5 4 >> 1787 Toomevara

Re: [GENERAL] count (DISTINCT field) OVER ()

Tarlika Elisabeth Schmitz, 10.11.2011 00:52: I would like to implement the equivalent of "count (DISTINCT field) OVER ()": SELECT id, name, similarity(name, 'Tooneyvara') as delta, count (id) OVER() AS cnt FROM vtown WHERE similarity(name, 'Tooneyvara')> 0.1 ORDER BY del

Re: [GENERAL] count (DISTINCT field) OVER ()

On Nov 9, 2011, at 18:52, Tarlika Elisabeth Schmitz wrote: > I would like to implement the equivalent of "count (DISTINCT field) OVER > ()": > > > SELECT > id, name, similarity(name, 'Tooneyvara') as delta, > count (id) OVER() AS cnt > FROM vtown > WHERE > similarity(name, 'Tooneyva

[GENERAL] count (DISTINCT field) OVER ()

I would like to implement the equivalent of "count (DISTINCT field) OVER ()": SELECT id, name, similarity(name, 'Tooneyvara') as delta, count (id) OVER() AS cnt FROM vtown WHERE similarity(name, 'Tooneyvara') > 0.1 ORDER BY delta DESC produces result: 1787Toomyvara 0

Re: [GENERAL] Count for pagination

Any suggestions on how to get the count of all records that could be returned We use a window function to get the total # of records within each of our paginated queries: SELECT ... ,COUNT(*) OVER() fullRowCount FROM ... WHERE ... ORDER BY ... LIMIT ... OFFSET ...; While there is a cost

Re: [GENERAL] Count for pagination

* Jason Long (ja...@octgsoftware.com) wrote: > The main search screen of my application has pagination. http://www.depesz.com/index.php/2007/08/29/better-results-paging-in-postgresql-82/ Thanks, Stephen signature.asc Description: Digital signature

[GENERAL] Count for pagination

The main search screen of my application has pagination. I am basically running 3 queries with the same where clause. 1. Totals for the entire results(not just the number of rows on the first page) a. <300 ms 2. Subset of the total records for one page. a. 1-2 sec 3. Count of the tot

[GENERAL] Count for pagination

The main search screen of my application has pagination. I am basically running 3 queries with the same where clause. 1. Totals for the entire results(not just the number of rows on the first page) a. <300 ms 2. Subset of the total records on that page. a. 1-2 sec 3. Count of the tot

Re: [GENERAL] Count actual transaction per minute?

/10, hubert depesz lubaczewski wrote: From: hubert depesz lubaczewski Subject: Re: [GENERAL] Count actual transaction per minute? To: "Melvin Davidson" Cc: pgsql-general@postgresql.org Date: Wednesday, May 12, 2010, 11:20 AM sure. run: select sum(xact_commit + xact_roll

Re: [GENERAL] Count actual transaction per minute?

On May 12, 2010, at 9:12 AM, Melvin Davidson wrote: > Can anyone tell me how to measure _actual_ transactions per minute on a > PostgreSQL server. I am not talking about using pgbench, as I am not > interested in determining what is possible, but rather the actual count of > queries / transacti

Re: [GENERAL] Count actual transaction per minute?

On Wed, May 12, 2010 at 09:12:43AM -0700, Melvin Davidson wrote: > Can anyone tell me how to measure _actual_ transactions per minute on a > PostgreSQL server. I am not talking about using pgbench, as I am not > interested in determining what is possible, but rather the actual count of > queries

[GENERAL] Count actual transaction per minute?

pgsql-general@postgresql.org Can anyone tell me how to measure _actual_ transactions per minute on a PostgreSQL server. I am not talking about using pgbench, as I am not interested in determining what is possible, but rather the actual count of queries / transactions being sent to the server.

Re: [GENERAL] count with high allocation

-- Forwarded message -- From: paulo matadr Date: 2010/4/7 Subject: Res: [GENERAL] count with high allocation To: pgsql-general@postgresql.org shared_buffer is too large. It is good for server with 64GB RAM. It can be about 1/2 RAM for dedicated server. PostgreSQL allocate shared

Res: [GENERAL] count with high allocation

:24 Assunto: Re: [GENERAL] count with high allocation 2010/4/7 paulo matadr : > with > set enable_hashagg to off , I give the same allocation. ok, then problem will be other. what is result of: show shared_buffers; show work_mem; Regards Pavel Stehule > > _

Re: [GENERAL] count with high allocation

dr > Cc: Tom Lane ; GENERAL > Enviadas: Quarta-feira, 7 de Abril de 2010 12:10:23 > Assunto: Re: [GENERAL] count with high allocation > > please, EXPLAIN ANALYZE > > and try to execute > > set enable_hashagg to off before as second variant. It have to take less > memor

Re: [GENERAL] count with high allocation

On Wed, Apr 7, 2010 at 10:41 AM, paulo matadr wrote: > with > set enable_hashagg to off , I give the same allocation. So what is your setting for shared_buffers? Cause this looks pretty normal to me. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Res: [GENERAL] count with high allocation

with set enable_hashagg to off , I give the same allocation. De: Pavel Stehule Para: paulo matadr Cc: Tom Lane ; GENERAL Enviadas: Quarta-feira, 7 de Abril de 2010 12:10:23 Assunto: Re: [GENERAL] count with high allocation please, EXPLAIN ANALYZE and try

Re: [GENERAL] count with high allocation

4033) SELECT > ________ > De: Tom Lane > Para: paulo matadr > Cc: GENERAL > Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27 > Assunto: Re: [GENERAL] count with high allocation > > paulo matadr writes: >> Monitoring "top" in

Res: [GENERAL] count with high allocation

_ De: Tom Lane Para: paulo matadr Cc: GENERAL Enviadas: Quarta-feira, 7 de Abril de 2010 11:31:27 Assunto: Re: [GENERAL] count with high allocation paulo matadr writes: > Monitoring "top" in database server , i could noticed an query with > reserved 8GB on physic

Re: [GENERAL] count with high allocation

paulo matadr writes: > Monitoring "top" in database server , i could noticed an query with > reserved 8GB on physical memory. > select count(field) from big_table 1 inner join big_table2... > There is the possibility of using another function with less memory > allocation? > Is there a way

Re: [GENERAL] count with high allocation

On Wed, Apr 7, 2010 at 8:11 AM, paulo matadr wrote: > Hi all, > Monitoring "top" in database server  , i could  noticed an query with > reserved  8GB on physical memory. You are likely seeing the SHR and VIRT columns saying that. Lemme guess, you've got 8G of shared memory allocated to pgsql? I

Re: [GENERAL] count with high allocation

Hello 2010/4/7 paulo matadr : > Hi all, > Monitoring "top" in database server  , i could  noticed an query with > reserved  8GB on physical memory. > > select count(field) from big_table  1 inner join big_table2... > > There is the possibility of using another function with less memory > allocatio

[GENERAL] count with high allocation

Hi all, Monitoring "top" in database server , i could noticed an query with reserved 8GB on physical memory. select count(field) from big_table 1 inner join big_table2... There is the possibility of using another function with less memory allocation? Is there a way to limit the memory usage

Re: [GENERAL] count function alternative in postgres

Merlin Moncure writes: > On Sat, Apr 3, 2010 at 3:02 PM, Scott Bailey wrote: >> SELECT COUNT( DISTINCT row(col1, col2, col3) ) FROM foo > very clever! This is similar to how I use rowtypes to get around the > single column restrictions on function calls in the select field list. Cute, but note

Re: [GENERAL] count function alternative in postgres

On Sat, Apr 3, 2010 at 3:02 PM, Scott Bailey wrote: > junaid malik wrote: >> >> Is there any alternative of mysql function COUNT(DISTINCT expr, >> [expr...]) in postgres. We get error if we >> >> write count like this count(distinct profile.id, profile.name, >> profile.age) but it works well in my

Re: [GENERAL] count function alternative in postgres

junaid malik wrote: Is there any alternative of mysql function COUNT(DISTINCT expr, [expr...]) in postgres. We get error if we write count like this count(distinct profile.id, profile.name, profile.age) but it works well in mysql. Reference url is given below http://dev.mysql.com/doc/refman/5.

Re: [GENERAL] count function alternative in postgres

junaid malik writes: > Is there any alternative of mysql function COUNT(DISTINCT expr, > [expr...]) in postgres. We get error if we The SQL-standard way to do that would be select count(*) from (select distinct expr,expr,... from ...) as ss; COUNT with multiple arguments is not anywhere in the

[GENERAL] count function alternative in postgres

Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in postgres. We get error if we write count like this count(distinct profile.id, profile.name, profile.age) but it works well in mysql. Reference url is given below http://dev.mysql.com/doc/refman/5.1/en/group-by-functi

[GENERAL] count function alternative in postgres

Is there any alternative of mysql function COUNT(DISTINCT expr, [expr...]) in postgres. We get error if we write count like this count(distinct profile.id, profile.name, profile.age) but it works well in mysql. Reference url is given below http://dev.mysql.com/doc/refman/5.1/en/group-by-function

Re: [GENERAL] Count occurrences of pattern in string

dario@libero.it wrote: > Hello, > > I'm looking for a function to count the occurrences of a pattern in a > string. E.g. something like: > > fun_count_pattern('fooXblaX', 'X') > > which would > return 2 (I.e. pattern 'X' found 2 times in string 'fooXblaX'). How about: test=*# select le

[GENERAL] Count occurrences of pattern in string

Hello, I'm looking for a function to count the occurrences of a pattern in a string. E.g. something like: fun_count_pattern('fooXblaX', 'X') which would return 2 (I.e. pattern 'X' found 2 times in string 'fooXblaX'). I could write my own function for this (probably using plpython?) but I was

Re: [GENERAL] count of query results in for loop

Sim Zacks wrote: > Is there any way to (without a separate count query first) to know the > number of rows returned in a: > > for x in select ... Loop > > ... > > End Loop > > structure? No. Not before you exit the loop. Yours, Laurenz Albe -- Sent via pgsql-general mailing list

[GENERAL] count of query results in for loop

Is there any way to (without a separate count query first) to know the number of rows returned in a: for x in select ... Loop ... End Loop structure? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql

  1   2   3   >