Re: [GENERAL] Problem with complex outer join expression

Seems like the problem has something to do with date arithmetic. I can't seem to add an integer expression to a date constant. It works if I add a integer constant to a date constant, but that's not what I want. I tried cast(expression as integer) + date '2006-04-09' but I get 'ERROR: operator doe

Re: [GENERAL] Problem with complex outer join expression

On 4/26/06, chris smith <[EMAIL PROTECTED]> wrote: > Table joins can only be done against another table & field, I don't > think you can do it using an expression like this. That should all be > in the where clause. > > What do the timesheets and dummy_records tables look like? timesheets.weekendi

[GENERAL] Mailing list setup issue

It seems that all the postgresql mailing lists don't allow you to automatically reply to the list only the sender. I don't get this with all my other mailings list. Who I contact to have this rectified? Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au

Re: [GENERAL] Autovacuum Logging

I found this short discussion between Tom and Bruce, but that's about it for autovacuum logging. http://archives.postgresql.org/pgsql-general/2006-04/msg00489.php It just seems like the "processing database" log statement should be set to a lower level, since it just logs every time autovacu

Re: [GENERAL] [HACKERS] pg_dump

On Wed, Apr 26, 2006 at 11:52:43PM -0500, Jim C. Nasby wrote: > How was PostgreSQL installed? There's no reason I know of why pg_dump > shouldn't have been upgraded. How was PostgreSQL installed? If is was installed using some kind of package management, make sure the -client binaries were upgrade

Re: [GENERAL] Disk Failure Scenarios

On Wed, Apr 26, 2006 at 11:54:23PM -0400, Michael Artz wrote: > -Disk1: If the OS/Postgres install disk fails, its possible to > reinstall the OS and the same version of Postgres and point it at disk > 3 and everything should run, right? Only if that doesn't include $PGDATA > -Disk 2: If the tran

Re: [GENERAL] Autovacuum Logging

I believe 8.2 will have improved autovac logging. Take a look in the -hackers archives for more info. On Wed, Apr 26, 2006 at 10:47:26PM -0500, Will Reese wrote: > Is there a reason many of the most useful autovacuum.c elog > statements are set to DEBUG2? It seems to me that these should be >

Re: [GENERAL] [HACKERS] pg_dump

Moving to -general, where this belongs. How was PostgreSQL installed? There's no reason I know of why pg_dump shouldn't have been upgraded. On Thu, Apr 27, 2006 at 12:40:12PM +1000, Bob Shearer wrote: > Anyone, > > Hello our server had postgresql 7.4 when updated to 8.1.2 everything is > working

Re: [GENERAL] PostgreSQL 8.1 + PHP5.1.x/4.4.2 + Apache 2.0.55/1.3.34 PROBLEM!! PLEASE HELP

On 4/27/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Help! I was trying to make an installation of PHP 5.1.2 + Apache > 2.0.55 + PostgreSQL 8.1 on Windows 2003 Server, and I'm stuck. I > got PHP to work with Apache quite smoothly, so did I marry PHP with > PostgreSQL - scripts connecting to t

Re: [GENERAL] Autovacuum Logging

I found where I saw that EnterpriseDB has enabled autovacuum logging. Look at item number one under the section entitled "Changes in 2006-03-31 update:" http://www.enterprisedb.com/readme_dbserver.do Will Reese -- http://blog.rezra.com On Apr 26, 2006, at 10:47 PM, Will Reese wrote: Is th

[GENERAL] Disk Failure Scenarios

(Sorry if this gets posted twice ... forgot that the list doesn't like new, unregistered email addresses) I'm setting up PG, and am curious about the failure scenarios of Postgres with respect to crashed disks.  In a given Postgres installation across many disks, which sections of Postgres can fai

[GENERAL] Autovacuum Logging

Is there a reason many of the most useful autovacuum.c elog statements are set to DEBUG2? It seems to me that these should be set to LOG. I used autovacuum when it was a contrib module, and it failed after a month. To prevent major performance problems I went back to the daily vacuum.

[GENERAL] PostgreSQL 8.1 + PHP5.1.x/4.4.2 + Apache 2.0.55/1.3.34 PROBLEM!! PLEASE HELP

Help! I was trying to make an installation of PHP 5.1.2 + Apache 2.0.55 + PostgreSQL 8.1 on Windows 2003 Server, and I'm stuck. I got PHP to work with Apache quite smoothly, so did I marry PHP with PostgreSQL - scripts connecting to the database work fine from windows command line, except that the

Re: [GENERAL] Database Selection

After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Robert Treat) belched out: > On Tuesday 25 April 2006 01:46, IvoD wrote: >> My "sixth sense" tells me that PostgreSQL is better than MySQL, >> therefore for main app I prefer PostgreSQL; but I am in doubt to >> run only one db engine for

Re: [GENERAL] Database Selection

Centuries ago, Nostradamus foresaw when "IvoD" <[EMAIL PROTECTED]> would write: >> [EMAIL PROTECTED] (Scott Marlowe) writes: >> > About the security thing. Security is a process, and you won't get >> > it from using two different database engines. >> >> I'd argue that security is an "emergent prop

Re: [GENERAL] Database Selection

On Tuesday 25 April 2006 01:46, IvoD wrote: > My "sixth sense" tells me that PostgreSQL is better than MySQL, > therefore for main app I prefer PostgreSQL; but I am in doubt to run > only one db engine for two databases. But my "inner space" tells me to > separate newsgroups system and company data

Re: [GENERAL] Problem with copy

Nevermind. It was a problem with the encoding type of the file. --- Eduardo Muñoz <[EMAIL PROTECTED]> escribió: > Hi, I'm trying to copy information from a csv file > using pgadminIII. I run the following command: > > copy cliente from '/path/file.csv' with csv; > > The command runs and i

Re: [GENERAL] Moving a data base between differnt OS

On Wed, Apr 26, 2006 at 03:24:19PM -0500, Bruno Wolff III wrote: > On Wed, Apr 26, 2006 at 21:33:32 +0200, > Holger Zwingmann <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I just realized that is is possible to do a (backup) copy of a database > > by copying the "root folder" within the same OS. >

Re: [GENERAL] Clustered table order is not preserved on insert

On Wed, Apr 26, 2006 at 11:05:09PM +0300, Andrus wrote: > >> I want to duplicate report so that id order is preserved. > > > > Tables aren't ordered by definition. > > From CLUSTER docs: > > "When a table is clustered, it is physically reordered based on the index > information. " Keep reading..

Re: [GENERAL] Database Selection

On Mon, Apr 24, 2006 at 10:46:50PM -0700, IvoD wrote: > My "sixth sense" tells me that PostgreSQL is better than MySQL, > therefore for main app I prefer PostgreSQL; but I am in doubt to run > only one db engine for two databases. But my "inner space" tells me to > separate newsgroups system and co

Re: [GENERAL] DatawareHousing

Take a look on sourceforge. See also bizgres. On Mon, Apr 24, 2006 at 04:14:46PM +0200, Wim Bertels wrote: > Hi, > > does anyone know a good opensource tool to do datawarehousing, > (instead of having to manage an mssql server and so on, > so just sticking to postgres or another good os-db.) > >

Re: [GENERAL] "save history" problem

On Mon, 24 Apr 2006, David F. Johnson wrote: ... could not save history to file "/usr/local/pgsql//.psql_history": Unknown error: 0 ... though I'm just now running it under Mac OS X Tiger (10.4.6). I'm guessing it's a Tiger issue but I don't know what to do about it. This was answered by Tom

[GENERAL] problem with unique text column

Hi, I have a problem with table which I use to store some variables. The table holds a primary key, a text column (unique) named 'text_key' and a text colum named 'text_value'. I query and update and insert into the table via appropriate store procedures, which usually work fine. This looks then

Re: [GENERAL] SQL Rule

Okay sorry i have to change the trigger from AFTER TO BEFORE CREATE TRIGGER trigger_sum BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE trigger_test(); TO ALBAN Our table definition are the same, so what? A primary key is a constraint. And >> CREATE O

Re: [GENERAL] SQL Rule

On April 26, 2006 02:35 pm, "Bert" <[EMAIL PROTECTED]> wrote: > CREATE TRIGGER trigger_sum > AFTER INSERT OR UPDATE > ON test > FOR EACH ROW > EXECUTE PROCEDURE trigger_test(); > > The inserting and updating is doing well, but its not summing up the a > and b and save it to

Re: [GENERAL] SQL Rule

Hi Kenneth Thats exactly what i want, because we are running more selects than inserts, and therefore a view is not the best way to go. But I still have a problem, I was doing like you told me. I have still the same table (without the rules definition) So I created a trigger function: CREATE FUNCT

[GENERAL] Problem with copy

Hi, I'm trying to copy information from a csv file using pgadminIII. I run the following command: copy cliente from '/path/file.csv' with csv; The command runs and i get no output. I thought the process could be taking a lot to complete, but i don't think so. I'm trying with just a few records an

Re: [GENERAL] Clustered table order is not preserved on insert

> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of Andrus > Sent: Wednesday, April 26, 2006 1:05 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Clustered table order is not preserved on insert > > >> I want to duplicate re

Re: [GENERAL] Clustered table order is not preserved on insert

Andrus wrote: I want to duplicate report so that id order is preserved. Tables aren't ordered by definition. From CLUSTER docs: "When a table is clustered, it is physically reordered based on the index information. " And what has that got to do with the order of SELECTs or INSERTs? Physi

Re: [GENERAL] Moving a data base between differnt OS

On Wed, Apr 26, 2006 at 21:33:32 +0200, Holger Zwingmann <[EMAIL PROTECTED]> wrote: > Hi, > > I just realized that is is possible to do a (backup) copy of a database > by copying the "root folder" within the same OS. There are some other conditions as well. Postgres needs to be shut down when y

Re: [GENERAL] Clustered table order is not preserved on insert

why don't you just (not tested) insert into report (col1,col2,col3) SELECT col1,col2,col3 FROM t2 order by id This should get the row into report in id order, you need to put in the correct column names -- Original Message --- From: "Andrus" <[EMAIL PROTECTED]> To: pgsql-general

RES: [GENERAL] Moving a data base between differnt OS

Hi Holger, No, it's not possible. The files are dependent upon OS. You can do Dump/Restore. Regards, Renato Cramer Analista de Sistemas Depto. de Desenvolvimento Domínio Sistemas Ltda. >-Mensagem original- >De: Holger Zwingmann [mailto:[EMAIL PROTECTED] >Enviada em: quarta-feira, 26 de

Re: [GENERAL] Clustered table order is not preserved on insert

>> I want to duplicate report so that id order is preserved. > > Tables aren't ordered by definition. >From CLUSTER docs: "When a table is clustered, it is physically reordered based on the index information. " > If you want to get results back in a particular order use ORDER BY, > possibly wrap

Re: [GENERAL] Clustered table order is not preserved on insert

> You are never guaranteed any order in a result set unless you use > ORDER BY in the query. I cannot use order by since postgres must generate new values for id column. For this case, id column must not exist in insertable table. > Because PG treats UPDATE as DELETE + INSERT, > the table orderi

[GENERAL] Moving a data base between differnt OS

Hi, I just realized that is is possible to do a (backup) copy of a database by copying the "root folder" within the same OS. Now, I am wondering if there is a way to copy a data base from one OS to another in the same way. Would it possible copy a (small) data base from Linux to MS Windows by sim

Re: [GENERAL] Clustered table order is not preserved on insert

Andrus wrote: I have table of reports CREATE TABLE report ( ReportName CHAR(5) not null check (reportname<>''), < a lot of other fields >, id serial primary key ) I want to duplicate report so that id order is preserved. Tables aren't ordered by definition. If you want to get results back in

Re: [GENERAL] Clustered table order is not preserved on insert

"Andrus" <[EMAIL PROTECTED]> writes: > Why postgres 8.1.3 changes order ? > How to preserve order in newr without adding extra field to report table ? You are never guaranteed any order in a result set unless you use ORDER BY in the query. Because PG treats UPDATE as DELETE + INSERT, the table o

[GENERAL] Clustered table order is not preserved on insert

I have table of reports CREATE TABLE report ( ReportName CHAR(5) not null check (reportname<>''), < a lot of other fields >, id serial primary key ) I want to duplicate report so that id order is preserved. BEGIN; CREATE temp TABLE tempreport AS SELECT * FROM report WHERE reportname=

Re: [GENERAL] pg_dump -t <> pg_restore -t

alexandre - aldeia digital wrote: Hi, If I do: pg_dump -Fc -t TABLE database > table-custom.sql The pg_dump returns the DDL of the table, the data and the DDL for indexes and PK. If I use -s, only the structure is returned but it's include all elements. But if I do: pg_dump -Fc database > ba

[GENERAL] pg_dump -t <> pg_restore -t

Hi, If I do: pg_dump -Fc -t TABLE database > table-custom.sql The pg_dump returns the DDL of the table, the data and the DDL for indexes and PK. If I use -s, only the structure is returned but it's include all elements. But if I do: pg_dump -Fc database > backup-custom.file pg_restore -Fc -t T

Re: [GENERAL] PL/Ruby, INOUT parameters / SETOF record

"Philippe Lang" <[EMAIL PROTECTED]> writes: > CREATE FUNCTION perl_test(a inout integer, b inout integer, r1 out integer, > r2 out integer) SETOF record AS > How would you translate that in PL/Ruby? I think you wouldn't :-( --- AFAICT from the documentation, plruby doesn't know about OUT paramet

Re: [GENERAL] Problem with complex outer join expression

"Chris Velevitch" <[EMAIL PROTECTED]> writes: > select dummy_records.sequence_nr,timesheets.weekending,timesheets.timesheet_id > from dummy_records > ,left outer join timesheets > on (timesheets.weekending = ('2006-04-09' + (integer > dummy_records.sequence_nr-1)*7))) > where dummy

Re: [GENERAL] Ordering of records in group by not possible

Alban Hertroys <[EMAIL PROTECTED]> writes: > Ordering the data before aggregating should do the trick though, but > you'll need to order your records in the subquery. Something like this, > I think: > Select a, aggregate(b) > from ( > select a, b > from c > order by a,b > ) d >

Re: [GENERAL] evaluating equation stored in a string

SunWuKung wrote: However I have a problem with this cast. I don't know how to handle brackets in the equation, since SELECT 35/124 ::numeric --0.28 while SELECT (35/124) ::numeric -- 0 The brackets aren't your problem, your numbers aren't as accurate as the result you require. An eval()

Re: [GENERAL] Ordering of records in group by not possible

Chris Kratz wrote: On Wednesday 26 April 2006 10:30 am, you wrote: Hello Alban, The point is that the aggregates we are working on in our application are order sensitive. One common example of order sensitive aggregates are the first and last aggregate functions found in some other databases.

Re: [GENERAL] evaluating equation stored in a string

Hi : How about creating a view that converts integer into numeric? create view as select ::numeric as , select ::numeric as ... and then query the view instead. This way your formula doesn't need to use cast anymore. ludwig lim --- SunWuKung <[EMAIL PROTECTED]> wrote: > I have equations s

Re: [GENERAL] Ordering of records in group by not possible

On Wednesday 26 April 2006 11:19 am, Ludwig Isaac Lim wrote: > Hi: > > You could order by "column 2" if you want to order on the > > results on > > your aggregate: > > Select a, aggregate(b) > > from c > > group by a > > order by a,2 > > another alternative is : > >select a , ag

Re: [GENERAL] Question about postgresql.conf memory settings

The Documentation is correct. In your case it means 32768 KB. Please read the postgres documentation for more info. From Postgres Documentation : "maintenance_work_mem (integer): Specifies the maximum amount of memory to be used in maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TA

Re: [GENERAL] Ordering of records in group by not possible

On Wednesday 26 April 2006 10:30 am, you wrote: > Chris Kratz wrote: > > Hello all, > > > > I wanted to verify what we are seeing. > > > > Select a, aggregate(b) > > from c > > group by a > > order by a,b > > That's a rather odd query... Values in b aren't available to order by, > as they have been

Re: [GENERAL] Ordering of records in group by not possible

Hi: > You could order by "column 2" if you want to order on the > results on > your aggregate: > Select a, aggregate(b) > from c > group by a > order by a,2 > another alternative is : select a , aggregrate(b) as from c group by a order by a, e.g. s

[GENERAL] evaluating equation stored in a string

I have equations stored in strings and I would need to evaluate them in pgsql - get the result. Maybe there is an eval() function but I couldn't find it. The only way I found was to use something like this: calcstring_arg:= 'SELECT ' || calcstring_arg || ' ::numeric AS outparam'; FOR tmp IN EXECU

[GENERAL] Question about postgresql.conf memory settings

Hi, I am a little confused on some of the memory setting in the postgresql.conf for example: maintenance_work_mem = 32768# min 1024, size in KB is the 32768 bytes or KB The comment says size is in KB, so I would assume this is actually 32768 * 1024 or is it really 32k? If it is 32k sho

Re: [GENERAL] Ordering of records in group by not possible

Chris Kratz wrote: Hello all, I wanted to verify what we are seeing. Select a, aggregate(b) from c group by a order by a,b That's a rather odd query... Values in b aren't available to order by, as they have been aggregated. There is no relation to the values in b and the values in your resu

Re: [GENERAL] Ordering of records in group by not possible

On Wed, Apr 26, 2006 at 09:19:41AM -0400, Chris Kratz wrote: > Hello all, > > I wanted to verify what we are seeing. > Select a, aggregate(b) > from c > group by a > order by a,b > > Is not accepted by postgres. This will only work if you order by a. But, > this means that the records that ar

[GENERAL] Ordering of records in group by not possible

Hello all, I wanted to verify what we are seeing. Select a, aggregate(b) from c group by a order by a,b Is not accepted by postgres. This will only work if you order by a. But, this means that the records that are grouped are processed in no apparent order. We have some custom aggregate fun

[GENERAL] Linux Journal article - Open source baseball stats web site will use PostgreSQL

http://www.linuxjournal.com/article/8986 Play Ball: Introducing Fungoes By Mat Kovach on Thu, 2006-04-20 01:00. Webmaster Follow along as a life-long baseball fan turns his hobby into an open-source bas

[GENERAL] PL/Ruby, INOUT parameters / SETOF record

Hi, I am testing PL/Perl under Postgresql 8.1.3, and I habe the following test procedure: -- CREATE FUNCTION perl_test(a inout integer, b inout integer, r1 out integer, r2 out integer) SETOF record AS ' my ($a, $b) = @_; $r1 = $a + $b; $r2 = $a * $b; return_next {a

Re: [GENERAL] Having problems with a 25 million row table on 8.1.3

On Tue, 2006-04-25 at 15:10 -0500, Tony Caduto wrote: > I have a client who has a 25 million row table that is used to keep > work_mem = 10240# min 64, size in KB Try putting work_mem a lot higher to improve the sort speed. -- Simon Riggs EnterpriseDB

Re: [GENERAL] Automatically assuming a specific role after connecting

Richard Huxton wrote: Florian G. Pflug wrote: Do you see any other way via which I could archive my desired result? (Apart from modifying every client in existence) Could you modify pgpool to act as a wrapper for this? Hm.. should be possible.. I'll look into this. Thanks for the hint. gree

Re: [GENERAL] SQL Rule

Bert wrote: Hi list I have a table construction like the one seen below, when i am updating or inserting i get a recurion, logical. But how to manage it that the rule is just doing it one time. Or is it possible to do the sum of a and b in an other way? CREATE TABLE test ( a int2, b int2,

Re: [GENERAL] Cascade constraint renames?

Hannes Dorbath wrote: How to rename PK constraints that are referenced in various tables, without dropping down the entire dependency tree and recreate everything (at least all FKs) afterwards? Dump-edit-reload is not an option, as the database size is way to huge.. :/ Not sure you can. Ther

[GENERAL] Cascade constraint renames?

How to rename PK constraints that are referenced in various tables, without dropping down the entire dependency tree and recreate everything (at least all FKs) afterwards? Dump-edit-reload is not an option, as the database size is way to huge.. :/ Thanks! -- Regards, Hannes Dorbath -

Re: [GENERAL] how can I check the error status??

Luis Alberto Pérez Paz wrote: Hi everybody! I'm migrating some sybase store procedures to postgres functions, I almost have found all that I need, however I dont know how to check the error status of the last transaction. I need to find in postgres the equivalent to the sybase @@error global var

Re: [GENERAL] Please comment on the following OpenFTS/tsearch2 issues!

1. While tsearch2 provides fairly complete boolean search expression support with AND - &, OR - |, NOT - !, and grouping - (), OpenFTS appears to only have support for ANDing search terms. Is there some reason it hasn't been extended to support full tsearch2 search expressions? Has anyone modified

Re: [GENERAL] Problem with complex outer join expression

On 4/26/06, Chris Velevitch <[EMAIL PROTECTED]> wrote: > I'm using 7.4.5 on win XP Pro SP1. > > I'm getting:- > > ERROR: syntax error at or near "(" at character 155 > > from the query:- > > select dummy_records.sequence_nr,timesheets.weekending,timesheets.timesheet_id > from dummy_records >