Re: [GENERAL] median for postgresql 8.3

2010-11-16 Thread maarten
Hello Brent, well I said "using only the db" because this is a one time need and just wanted to avoid crufting around with calc, and doing 'manual' work. But that seems to be the fastest approach now. When it becomes a frequent need I'll probably end up doing what you suggest, or upgrade to 9.x,

Re: [GENERAL] where is pg_stat_activity (and others) in the documentation?

2010-11-16 Thread Willy-Bas Loos
> You can find out exactly what you're waiting for by correlating this to the > pg_locks table. > Grab the 'procpid' of your waiting query and run: > select * from pg_locks where pid= and granted='f'; > Notice, in the pg_locks table, the logic works slightly different... if > you're 'waiting'

Re: [GENERAL] strange row count estimates with conditions on multiple column

2010-11-16 Thread Tom Lane
Tomas Vondra writes: > Yes, I understand why MCV is not used in case of col_b, and I do > understand that the estimate may not be precise. But I'm wondering > what's a better estimate in such cases - 1, 5000, any constant, or > something related to a the histogram? It is doing it off the histogra

Re: [GENERAL] strange row count estimates with conditions on multiple column

2010-11-16 Thread Tomas Vondra
> BTW I think the default estimate used to be 1000, so it was changed in > one of the 8.x releases? Can you point me to the docs? I've even tried > to find that in the sources, but unsuccessfully. OK, I've found it right after submitting the e-mail. It's defined in selfuncs.h as DEFAULT_RANGE_INE

Re: [GENERAL] strange row count estimates with conditions on multiple column

2010-11-16 Thread Tomas Vondra
Dne 17.11.2010 05:22, Tom Lane napsal(a): > Tomas Vondra writes: >> Estimate for condition >>... WHERE (col_a BETWEEN 33 AND 33) >> is about 10k rows, which is quite precise. On the other side estimate >> for condition >>... WHERE (col_b BETWEEN 33 AND 33) >> is 1 row, which is very imprec

Re: [GENERAL] strange row count estimates with conditions on multiple column

2010-11-16 Thread Tom Lane
Tomas Vondra writes: > Estimate for condition >... WHERE (col_a BETWEEN 33 AND 33) > is about 10k rows, which is quite precise. On the other side estimate > for condition >... WHERE (col_b BETWEEN 33 AND 33) > is 1 row, which is very imprecise (actual value is about 5000). That's an artif

Re: [GENERAL] strange row count estimates with conditions on multiple column

2010-11-16 Thread Tomas Vondra
OK, thanks for the explanation. Cases (A), (B) and (D) are clear now. But I'm not sure about (C) ... Dne 17.11.2010 04:03, Tom Lane napsal(a): > Tomas Vondra writes: >> I'm not quite sure why (C) has an estimate of 1. > > It's smart enough to see that each of the clauses is a range constraint >

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Tom Lane
Man, the number of misunderstandings in this thread is staggering. Let me try to explain what the proposed feature will and will not do. 1. The system catalog entries for all tables will be wal-logged. So schema (DDL) will survive a crash. There wouldn't be any way to make it not do that, because

Re: [GENERAL] strange row count estimates with conditions on multiple column

2010-11-16 Thread Tom Lane
Tomas Vondra writes: > Results from (A) and (B) seem strange to me because AFAIK there are no > multi-column statistics available, and accoring to this thread > http://archives.postgresql.org/pgsql-hackers/2009-03/msg00052.php > the single-column estimates are not multiplied (which would be OK o

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Glen Parker
On 11/16/2010 05:55 PM, Josh Berkus wrote: If you do wish to have the data tossed out for no good reason every so often, then there ought to be a separate attribute to control that. I'm really having trouble seeing how such behavior would be desirable enough to ever have the server do it for y

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Andy Colson
On 11/16/2010 07:55 PM, Josh Berkus wrote: If you do wish to have the data tossed out for no good reason every so often, then there ought to be a separate attribute to control that. I'm really having trouble seeing how such behavior would be desirable enough to ever have the server do it for y

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Josh Berkus
> I'd vote for backing up the schema of an unlogged table so it's there > on a restore. The schema is always there. What may or may not be there is the data. -- -- Josh Berkus PostgreSQL Experts Inc.

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Josh Berkus
> If you do wish to have the data tossed out for no good reason every so > often, then there ought to be a separate attribute to control that. I'm > really having trouble seeing how such behavior would be desirable enough > to ever have the server do it for you, on its terms rather than yours. I

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Glen Parker
On 11/16/2010 05:33 PM, Josh Berkus wrote: With the current patches, the data survives a restart just fine. Per -hackers, that's not guarenteed. Which is fine. If you choose to set a table to nonlogged, that implies that you accept the risk of corrupted data, or that you don't "get it",

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Scott Mead
On Tue, Nov 16, 2010 at 8:15 PM, Tom Lane wrote: > Scott Mead writes: > > +1 -- Is there a technical reason to do a TRUNCATE on restart? I'd feel > > better if I could just have unlogged tables that survive unless something > > like a power-outage etc... I'm in the exact same boat here, lots

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Scott Marlowe
On Tue, Nov 16, 2010 at 5:23 PM, Scott Ribe wrote: > On Nov 16, 2010, at 3:46 PM, Josh Berkus wrote: > >>  ...and will be truncated (emptied) on database restart. > > I think that's key. Anything that won't survive a database restart, I sure > don't expect to survive backup & restore. I'd vote f

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Andy Colson
On 11/16/2010 07:33 PM, Josh Berkus wrote: With the current patches, the data survives a restart just fine. Per -hackers, that's not guarenteed. Ah, I just read the thread on -hackers. And yea, my system had 24 hours to write/flush/etc before I'd restarted it moments ago as a test. I ha

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Josh Berkus
> With the current patches, the data survives a restart just fine. Per -hackers, that's not guarenteed. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pg

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Glen Parker
On 11/16/2010 05:15 PM, Tom Lane wrote: Keep in mind that these tables are *not* going to survive any type of backend crash. Maybe my perceptions are colored because I deal with Postgres bugs all the time, but I think of backend crashes as pretty common, certainly much more common than an OS-lev

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Andy Colson
On 11/16/2010 04:46 PM, Josh Berkus wrote: PostgreSQL 9.1 is likely to have, as a feature, the ability to create tables which are "unlogged", meaning that they are not added to the transaction log, and will be truncated (emptied) on database restart. Such tables are intended for highly volatile,

[GENERAL] strange row count estimates with conditions on multiple column

2010-11-16 Thread Tomas Vondra
Hi everyone, I've just noticed a strange behaviour when estimating row counts (I'm running 9.0.1). A small demonstration - let's create table with two columns, and fill it with data so that the columns are not independent: = --

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Tom Lane
Scott Mead writes: > +1 -- Is there a technical reason to do a TRUNCATE on restart? I'd feel > better if I could just have unlogged tables that survive unless something > like a power-outage etc... I'm in the exact same boat here, lots of big > logging tables that need to survive reboot, but ar

Re: [GENERAL] port warded (iptables) postgres

2010-11-16 Thread Scott Mead
Make sure that listen_addresses='' or '*' By default, the server only listens on unix sockets. --Scott On Tue, Nov 16, 2010 at 6:41 PM, zhong ming wu wrote: > Hello > > I have successfully used iptables to direct ports for other services. > For example I run apache on 8443 on 127.0.0.1 and

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Scott Mead
On Tue, Nov 16, 2010 at 7:21 PM, Glen Parker wrote: > On 11/16/2010 03:24 PM, Karsten Hilbert wrote: > >> PostgreSQL 9.1 is likely to have, as a feature, the ability to create >>> tables which are "unlogged", meaning that they are not added to the >>> transaction log, and will be truncated (empti

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Josh Berkus
> This is a link to a read-only spreadsheet for me. You're correct. Darn those Google unreadable links! https://spreadsheets.google.com/viewform?formkey=dDFnT2VKNC1FQ0pQNmJGS2dWMTNYMEE6MQ That should work. -- -- Josh Berkus

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Glen Parker
On 11/16/2010 03:24 PM, Karsten Hilbert wrote: PostgreSQL 9.1 is likely to have, as a feature, the ability to create tables which are "unlogged", meaning that they are not added to the transaction log, and will be truncated (emptied) on database restart. Such tables are intended for highly volati

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Scott Ribe
On Nov 16, 2010, at 3:46 PM, Josh Berkus wrote: > ...and will be truncated (emptied) on database restart. I think that's key. Anything that won't survive a database restart, I sure don't expect to survive backup & restore. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Jorge Arévalo
On Wed, Nov 17, 2010 at 1:13 AM, Tom Lane wrote: > =?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: >> Yes, it takes the table names from another query (select table_names >> from other_table), and the fields names from a XML file. Can the query >> which fetch the table names cause a memory problem? Seem

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Tom Lane
=?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: > Yes, it takes the table names from another query (select table_names > from other_table), and the fields names from a XML file. Can the query > which fetch the table names cause a memory problem? Seems weird Well, we were already in pretty-weird territor

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Jorge Arévalo
On Wed, Nov 17, 2010 at 1:00 AM, Tom Lane wrote: > =?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: >> Well, the query works if executed in psql client. Seems to be a Python >> problem. I'll execute the rest of the loop queries in the client, just >> in case. > >> But in case is a Python problem, why doe

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Tom Lane
=?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: > Well, the query works if executed in psql client. Seems to be a Python > problem. I'll execute the rest of the loop queries in the client, just > in case. > But in case is a Python problem, why does it affect the postgres > process, causing a crash? Is

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Jorge Arévalo
2010/11/17 Jorge Arévalo : > On Wed, Nov 17, 2010 at 12:14 AM, Tom Lane wrote: >> =?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: >>> On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane wrote: Can you boil it down to a self-contained test case? >> >>> Before executing the queries, the app reads a small XML

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Derrick Rice
On Tue, Nov 16, 2010 at 5:46 PM, Josh Berkus wrote: > > Survey is here: > > https://spreadsheets.google.com/ccc?key=0AoeuP3g2YZsFdDFnT2VKNC1FQ0pQNmJGS2dWMTNYMEE&hl=en&authkey=CISbwuYD This is a link to a read-only spreadsheet for me. Derrick

[GENERAL] port warded (iptables) postgres

2010-11-16 Thread zhong ming wu
Hello I have successfully used iptables to direct ports for other services. For example I run apache on 8443 on 127.0.0.1 and use iptable to direct traffic to public ip on 443. Trying the same with postgresql does not seem to work properly. I was wondering whether anyone has successfully used ip

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Karsten Hilbert
> PostgreSQL 9.1 is likely to have, as a feature, the ability to create > tables which are "unlogged", meaning that they are not added to the > transaction log, and will be truncated (emptied) on database restart. > Such tables are intended for highly volatile, but not very valuable, > data, such a

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Jorge Arévalo
On Wed, Nov 17, 2010 at 12:14 AM, Tom Lane wrote: > =?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: >> On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane wrote: >>> Can you boil it down to a self-contained test case? > >> Before executing the queries, the app reads a small XML file, to get >> names and paths,

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Tom Lane
=?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: > On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane wrote: >> Can you boil it down to a self-contained test case? > Before executing the queries, the app reads a small XML file, to get > names and paths, basically. I could simply execute the query in the > conso

Re: [GENERAL] AfterTriggerSaveEvent() called outside of query

2010-11-16 Thread Tom Lane
Vick Khera writes: > On Tue, Nov 16, 2010 at 12:40 PM, Tom Lane wrote: >> The system will not normally allow cascade actions to be deferred >> ... did you manually munge the pg_trigger entries?  If you managed >> to provoke this purely through DDL commands, that would be a bug, >> and I'd like to

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Jorge Arévalo
On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane wrote: > =?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: >> I'm getting an error message in my Python script: > >> pg.ProgrammingError: ERROR:  out of memory >> DETAIL:  Failed on request of size 16. > > That's pretty odd because the memory map you provided isn

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Elliot Chance
On 17/11/2010, at 6:22 AM, Stephen Cook wrote: > On 11/16/2010 10:51 AM, Magnus Hagander wrote: >> What I'm more interested in is still a word from the people who would >> actually *use* a forum on how this would be better than sites like >> Nabble and Gmane. > > I'm one of those. I'm subscribed

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Tom Lane
=?ISO-8859-1?Q?Jorge_Ar=E9valo?= writes: > I'm getting an error message in my Python script: > pg.ProgrammingError: ERROR: out of memory > DETAIL: Failed on request of size 16. That's pretty odd because the memory map you provided isn't showing any particular signs of bloat. I wonder whether

[GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

2010-11-16 Thread Josh Berkus
Folks, Please help us resolve a discussion on -hackers. PostgreSQL 9.1 is likely to have, as a feature, the ability to create tables which are "unlogged", meaning that they are not added to the transaction log, and will be truncated (emptied) on database restart. Such tables are intended for high

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Jorge Arévalo
2010/11/16 Andy Colson : > On 11/16/2010 3:55 PM, Jorge Arévalo wrote: >> >> Hello, >> >> I'm getting an error message in my Python script: >> >> pg.ProgrammingError: ERROR:  out of memory >> DETAIL:  Failed on request of size 16. >> >> The query that caused the error was: >> >> UPDATE table1 SET s

Re: [GENERAL] function problems

2010-11-16 Thread Christine Penner
ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function "subjectdisplay" line 7 at SQL statement ** Error ** ERROR: query has no destination for result data SQL state: 42601 Hint: If

Re: [GENERAL] function problems

2010-11-16 Thread Raymond O'Donnell
On 16/11/2010 21:27, Christine Penner wrote: Select S_TRAINING_TITLE as Display from System Limit 1; Looking again at your function, I think this (and other similar lines) should be select s_training_title into display - i.e. "into" instead of "as". HTH, Ray. -- Raymond O'Donnell

Re: [GENERAL] function problems

2010-11-16 Thread Christine Penner
I am returning one value (text). Each of the selects in the function should also return only one value. At 02:06 PM 16/11/2010, you wrote: On 11/16/2010 3:57 PM, Christine Penner wrote: I have seen other functions (written by others) that do this. So I assume its ok. I'm open to suggestions th

Re: [GENERAL] function problems

2010-11-16 Thread Raymond O'Donnell
On 16/11/2010 21:57, Christine Penner wrote: I have seen other functions (written by others) that do this. So I assume its ok. I'm open to suggestions though. As long as it works. OK, fair enough. What error are you getting? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent

Re: [GENERAL] Programming error: Out of Memory

2010-11-16 Thread Andy Colson
On 11/16/2010 3:55 PM, Jorge Arévalo wrote: Hello, I'm getting an error message in my Python script: pg.ProgrammingError: ERROR: out of memory DETAIL: Failed on request of size 16. The query that caused the error was: UPDATE table1 SET string = (SELECT string FROM table2 WHERE ST_Centroid(t

Re: [GENERAL] function problems

2010-11-16 Thread Andy Colson
On 11/16/2010 3:57 PM, Christine Penner wrote: I have seen other functions (written by others) that do this. So I assume its ok. I'm open to suggestions though. As long as it works. At 01:54 PM 16/11/2010, you wrote: On 16/11/2010 21:27, Christine Penner wrote: create or replace function Subj

[GENERAL] Programming error: Out of Memory

2010-11-16 Thread Jorge Arévalo
Hello, I'm getting an error message in my Python script: pg.ProgrammingError: ERROR: out of memory DETAIL: Failed on request of size 16. The query that caused the error was: UPDATE table1 SET string = (SELECT string FROM table2 WHERE ST_Centroid(table1.wkb_geometry) && table2. the_geom AND (S

Re: [GENERAL] function problems

2010-11-16 Thread Christine Penner
I have seen other functions (written by others) that do this. So I assume its ok. I'm open to suggestions though. As long as it works. At 01:54 PM 16/11/2010, you wrote: On 16/11/2010 21:27, Christine Penner wrote: create or replace function SubjectDisplay(IN subj varchar,IN meetCode numeric

Re: [GENERAL] function problems

2010-11-16 Thread Raymond O'Donnell
On 16/11/2010 21:27, Christine Penner wrote: create or replace function SubjectDisplay(IN subj varchar,IN meetCode numeric,IN meetTrWP integer,OUT Display varchar) returns varchar as I'm not certain about this, but is it a mistake to mix OUT parameters and RETURNS? Ray. -- Raymond O'Donn

Re: [GENERAL] Counting boolean values (how many true, how many false)

2010-11-16 Thread Nicklas Avén
If you want to use the boolean approach I would just (as suggested earlier) cast to integer and sum. Like: SELECT sum(good::int) as good, count(good)-sum(good::int) as "not good" and so on I thing the boolean approach seems reasonable if good, nice and fair is three separaty judgements as I und

[GENERAL] function problems

2010-11-16 Thread Christine Penner
I am trying to write a postgres function and I'm getting errors when the parameter sub is empty (it wont ever be null) and meetCode has a number. This is the function code: create or replace function SubjectDisplay(IN subj varchar,IN meetCode numeric,IN meetTrWP integer,OUT Display varchar)

Re: [GENERAL] AfterTriggerSaveEvent() called outside of query

2010-11-16 Thread Vick Khera
On Tue, Nov 16, 2010 at 12:40 PM, Tom Lane wrote: > The system will not normally allow cascade actions to be deferred > ... did you manually munge the pg_trigger entries?  If you managed > to provoke this purely through DDL commands, that would be a bug, > and I'd like to see how you did it. > Ba

Re: [GENERAL] median for postgresql 8.3

2010-11-16 Thread Brent Wood
Hi Maarten, The best way I know of to do this is not to do statistical queries "in" the DB at all, but use a stats capability embedded in your database, so they still appear to the user to be done in the db. I don't see how you can easily get the functionality you want without user defined func

Re: [GENERAL] How to select a postgresql table inside Oracle

2010-11-16 Thread Peter Geoghegan
On 16 November 2010 20:35, Dann Corbit wrote: > Oracle from PostgreSQL: > > http://www.sqlmag.com/article/sql-server/setting-up-an-oracle-linked-server49687.aspx > > > > PostgreSQL from Oracle: > > http://www.postgresql.org/docs/current/static/dblink.html > > > > Or perhaps I do not understand you

Re: [GENERAL] ipv4 data type does not allow to use % assubnet mask delimiter

2010-11-16 Thread Tom Lane
Peter Eisentraut writes: > On mån, 2010-11-15 at 11:06 +0200, Andrus wrote: >>> Possibly someday the standard will actually standardize the things, >>> and then maybe we can work with them usefully ... >> From http://tools.ietf.org/html/rfc4007#section-11.2 >> so this is clearly standardized. >

Re: [GENERAL] How to select a postgresql table inside Oracle

2010-11-16 Thread Dann Corbit
Oracle from PostgreSQL: http://www.sqlmag.com/article/sql-server/setting-up-an-oracle-linked-server49687.aspx PostgreSQL from Oracle: http://www.postgresql.org/docs/current/static/dblink.html Or perhaps I do not understand your question. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-g

Re: [GENERAL] How to select a postgresql table inside Oracle

2010-11-16 Thread Pavel Stehule
Hello 2010/11/16 Hu, William : > Hi, I have a need to select some data in a Oracle database from a Postgresql > table.  I know how to do this among Oracle instances, eg.  Select * from > ta...@another_instance. > > > > Are there similar approach without replicate the database tables? > with littl

Re: [GENERAL] How to select a postgresql table inside Oracle

2010-11-16 Thread Peter Geoghegan
On 16 November 2010 20:09, Hu, William wrote: > Hi, I have a need to select some data in a Oracle database from a Postgresql > table.  I know how to do this among Oracle instances, eg.  Select * from > ta...@another_instance. > > > > Are there similar approach without replicate the database tables

[GENERAL] How to select a postgresql table inside Oracle

2010-11-16 Thread Hu, William
Hi, I have a need to select some data in a Oracle database from a Postgresql table. I know how to do this among Oracle instances, eg. Select * from ta...@another_instance. Are there similar approach without replicate the database tables? Thanks in advance.

Re: [GENERAL] ipv4 data type does not allow to use % assubnet mask delimiter

2010-11-16 Thread Peter Eisentraut
On mån, 2010-11-15 at 11:06 +0200, Andrus wrote: > > Possibly someday the standard will actually standardize the things, > > and then maybe we can work with them usefully ... > > >From http://tools.ietf.org/html/rfc4007#section-11.2 > > implementation SHOULD >support the following format: >

Re: [GENERAL] Counting boolean values (how many true, how many false)

2010-11-16 Thread Richard Broersma
On Tue, Nov 16, 2010 at 11:32 AM, Alexander Farber wrote: > sum(case when good then 1 else 0 end) as good, > sum(case when not good then 1 else 0 end) as "not good", > sum(case when fair then 1 else 0 end) as fair, > sum(case when not fair then 1 else 0 end) as "not fair", > sum(case when nice the

Re: [GENERAL] Counting boolean values (how many true, how many false)

2010-11-16 Thread Alexander Farber
Thank you all, I've ended up with the following. But I really wonder if using boolean in my case (trying to offer players a web from with 3 x 2 radio buttons to rate each other) is really the best choice - since it feels a bit awkward (and maybe slow?) # create table pref_rep (

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Stephen Cook
On 11/16/2010 10:51 AM, Magnus Hagander wrote: What I'm more interested in is still a word from the people who would actually *use* a forum on how this would be better than sites like Nabble and Gmane. I'm one of those. I'm subscribed to these mailing lists simply because it is the only way I

Re: [GENERAL] escape character for 'psql -c ' command

2010-11-16 Thread Derrick Rice
Please include the list when replying. On Tue, Nov 16, 2010 at 11:45 AM, Gary Fu wrote: > Short answer: for simple commands, you can use shell-escaping of a >> double-quoted string. >> >> psql -c "\\copy \"Table\" from 'text file'" >> >> > This works on sh, but I tried this syntax on tcsh, it fa

Re: [GENERAL] I want to create a read only database for a specified user.

2010-11-16 Thread Chris Barnes
Thanks Greg, just what I asked for! Chris > From: g...@turnstep.com > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] I want to create a read only database for a specified > user. > Date: Tue, 16 Nov 2010 18:10:33 + > > > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > >

Re: [GENERAL] I want to create a read only database for a specified user.

2010-11-16 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I now want to have this database or all tables capable of read-only. ALTER DATABASE foo SET default_transaction_read_only = true; Easy to work around, but may be good enough for your purposes. - -- Greg Sabino Mullane g...@turnstep.com End

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Craig Ringer
On 17/11/2010 12:01 AM, Michael Gould wrote: Personally I don't care what kind of "forum" interface is used. I just don't like the email because while I like to follow the forum, I spend a lot of time out of the office and I don't like to have to download all of that mail just to keep up. I'd m

Re: [GENERAL] escape character for 'psql -c ' command

2010-11-16 Thread Gary Fu
On 11/15/10 21:10, Derrick Rice wrote: Short answer: for simple commands, you can use shell-escaping of a double-quoted string. psql -c "\\copy \"Table\" from 'text file'" This works on sh, but I tried this syntax on tcsh, it fails: 11:38am 72 g...@modular:~/sybase2postgres> psql -c "\\copy

[GENERAL] I want to create a read only database for a specified user.

2010-11-16 Thread Chris Barnes
Hello, I have a postgres user, that was used to create a database with some 1500+ tables. I now want to have this database or all tables capable of read-only. Is there an easy way of doing this? I am running 8.4.4. on Linux Centos 5 Chris Barnes Recognia Inc.

Re: [GENERAL] AfterTriggerSaveEvent() called outside of query

2010-11-16 Thread Tom Lane
Vick Khera writes: > The code looks basically like this, for each owner_id ($oid) targeted > for the purge: > BEGIN; > SET LOCAL synchronous_commit TO OFF; > SET CONSTRAINTS ALL DEFERRED; > foreach table (owner_log, ... user_list, invoices, ... ) > DELETE FROM $table WHERE owner_id=$oid > UPDA

Re: [GENERAL] median for postgresql 8.3

2010-11-16 Thread Pavel Stehule
Hello see http://okbob.blogspot.com/2009/11/aggregate-function-median-in-postgresql.html Regards Pavel Stehule 2010/11/16 maarten : > Hello everyone, > > I was doing some analysis of data to find average delays between some > timestamp values etc... > When the number of rows the average is com

[GENERAL] median for postgresql 8.3

2010-11-16 Thread maarten
Hello everyone, I was doing some analysis of data to find average delays between some timestamp values etc... When the number of rows the average is computed over is small, this can give distorted values. So I've obviously added a count column to see if the average represents much data. However,

[GENERAL] AfterTriggerSaveEvent() called outside of query

2010-11-16 Thread Vick Khera
I'm running a script I have for cleaning out some old data. It iterates over a handful of tables and deletes any data referencing a row in an "owners" table. Basically, it enforces the equivalent of the cascade delete of the data without actually deleting the master row. The code looks basically

Re: [GENERAL] Counting boolean values (how many true, how many false)

2010-11-16 Thread André Fernandes
> Date: Tue, 16 Nov 2010 17:23:16 +0100 > Subject: [GENERAL] Counting boolean values (how many true, how many false) > From: alexander.far...@gmail.com > To: pgsql-general@postgresql.org > > Hello, > > if I have this table with 3 boolean columns: > > # \d pref_rate > Table "p

Re: [GENERAL] Counting boolean values (how many true, how many false)

2010-11-16 Thread Thom Brown
On 16 November 2010 17:02, Thom Brown wrote: > On 16 November 2010 16:49, maarten wrote: >> Hi, >> >> sum doesn't like booleans, but it does like integers so: >> sum(boolval::int) solves that problem for you. >> >> SELECT id,sum(good::int + fair::int + nice::int) would get you a total >> of the t

Re: [GENERAL] Counting boolean values (how many true, how many false)

2010-11-16 Thread Thom Brown
On 16 November 2010 16:49, maarten wrote: > Hi, > > sum doesn't like booleans, but it does like integers so: > sum(boolval::int) solves that problem for you. > > SELECT id,sum(good::int + fair::int + nice::int) would get you a total > of the three for each row > > good luck, > Maarten Or, if you

Re: [GENERAL] Counting boolean values (how many true, how many false)

2010-11-16 Thread maarten
Hi, sum doesn't like booleans, but it does like integers so: sum(boolval::int) solves that problem for you. SELECT id,sum(good::int + fair::int + nice::int) would get you a total of the three for each row good luck, Maarten On Tue, 2010-11-16 at 17:23 +0100, Alexander Farber wrote: > Hello,

Re: [GENERAL] Counting boolean values (how many true, how many false)

2010-11-16 Thread Adrian Klaver
On Tuesday 16 November 2010 8:23:16 am Alexander Farber wrote: > Hello, > > if I have this table with 3 boolean columns: > > # \d pref_rate > Table "public.pref_rep" >Column |Type | Modifiers > +-+-

[GENERAL] Counting boolean values (how many true, how many false)

2010-11-16 Thread Alexander Farber
Hello, if I have this table with 3 boolean columns: # \d pref_rate Table "public.pref_rep" Column |Type | Modifiers +-+--- id | character varying(32) | author | character varyin

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Michael Gould
Personally I don't care what kind of "forum" interface is used. I just don't like the email because while I like to follow the forum, I spend a lot of time out of the office and I don't like to have to download all of that mail just to keep up. I'd much rather use something that I can access from

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Magnus Hagander
On Tue, Nov 16, 2010 at 16:40, Craig Ringer wrote: > On 11/16/2010 08:43 PM, Elliot Chance wrote: >> >> Alrighty, here are the revised plans for beta3: >> >> --- Changes / Fixes >> >> * Without a doubt everyone one wants the forums to match the mailing >> lists. I personally think that this defeat

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Magnus Hagander
On Tue, Nov 16, 2010 at 13:43, Elliot Chance wrote: > Alrighty, here are the revised plans for beta3: > > * The second largest issue is that of the email address to assign to emails > created by forum posts. I don't know the internals of how the mailing list > software works, but some thoughts;

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Craig Ringer
On 11/16/2010 08:43 PM, Elliot Chance wrote: Alrighty, here are the revised plans for beta3: --- Changes / Fixes * Without a doubt everyone one wants the forums to match the mailing lists. I personally think that this defeats the whole purpose of a forum, but i'm here to do what the community

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Elliot Chance
Alrighty, here are the revised plans for beta3: --- Changes / Fixes * Without a doubt everyone one wants the forums to match the mailing lists. I personally think that this defeats the whole purpose of a forum, but i'm here to do what the community thinks is best. The extra forums that are not

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Willy-Bas Loos
Hi, Something isn't going right: http://forums.postgresql.com.au/viewtopic.php?f=30&t=96 Contains 2 issues. I participated in both, but my reply to the second issue is not included in the forum. If you need me to show you the emails or whatever, just ask. btw: great stuff! :) Cheers, WBL On T

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Magnus Hagander
On Tue, Nov 16, 2010 at 10:59, J. Roeleveld wrote: > On Tuesday 16 November 2010 10:30:05 Magnus Hagander wrote: >> On Tue, Nov 16, 2010 at 03:45, Elliot Chance wrote: >> > I have made some major changes "beta2" >> >> >> >> > Extra thoughts; >> > >> > It would not be practical for the forums to

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Magnus Hagander
On Tue, Nov 16, 2010 at 11:11, Thom Brown wrote: > On 16 November 2010 09:30, Magnus Hagander wrote: >> >> On Tue, Nov 16, 2010 at 03:45, Elliot Chance wrote: >> > I have made some major changes "beta2" >> >> >> > Extra thoughts; >> > >> > It would not be practical for the forums to create a du

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread J. Roeleveld
On Tuesday 16 November 2010 10:30:05 Magnus Hagander wrote: > On Tue, Nov 16, 2010 at 03:45, Elliot Chance wrote: > > I have made some major changes "beta2" > > > > > Extra thoughts; > > > > It would not be practical for the forums to create a dummy mailing list > > email address per person or

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Thom Brown
On 16 November 2010 09:30, Magnus Hagander wrote: > > On Tue, Nov 16, 2010 at 03:45, Elliot Chance wrote: > > I have made some major changes "beta2" > > > > Extra thoughts; > > > > It would not be practical for the forums to create a dummy mailing list > > email address per person or forum, > >

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Magnus Hagander
On Tue, Nov 16, 2010 at 03:45, Elliot Chance wrote: > I have made some major changes "beta2" > Extra thoughts; > > It would not be practical for the forums to create a dummy mailing list email > address per person or forum, Why? It doesn't have to be actual mailboxes, but it needs to be a deli

Re: [GENERAL] Postgres forums ... take 2

2010-11-16 Thread Magnus Hagander
On Tue, Nov 16, 2010 at 07:00, Elliot Chance wrote: > On 16/11/2010, at 2:01 PM, Craig Ringer wrote: >> Have you been in touch with the Pg list admins to make sure they're cool >> with this? > > At this point its a good idea, who is the best person(s) to contact? I want > to make sure anything I