Re: [GENERAL] maximum count of contiguous years

2009-09-04 Thread gorsa
thanks tim. will read up on rank() and pl/pgsql. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Creating Superuser and password

2009-09-04 Thread Vikram Patil
Hey Folks, We are trying to integrate postgres with our product. Integrating installation process with our installer, so for setting up password for superuser there is an option in initdb to prompt a person who is running initdb for password. Or other option is to provide password from

Re: [GENERAL] Full-Text Searching: to_tsquery() vs. plainto_tsquery()

2009-09-04 Thread Oleg Bartunov
APseudoUtopia, you invented your own query language, so you should write your own function, which transforms user's query to ::tsquery, if to_tsquery() and plainto_tsquery() doesn't satisfy your input language. Notice, that phrase search will be able only in 8.5 version. Oleg On Fri, 4 Sep

Re: [GENERAL] query speed question

2009-09-04 Thread Christopher Condit
> > > > I have two tables that are georeferenced (although in this case > I'm > > > not using PostGIS) that I need to join. > > > > A ( lat | lon | depth | value) > > > > |A| = 1,100,000 > > > > > > > > B ( lat | lon | attributes) > > > > |B| = 14,000,000 > > > > > > > > A is a special case because

[GENERAL] Full-Text Searching: to_tsquery() vs. plainto_tsquery()

2009-09-04 Thread APseudoUtopia
Hello, I'm working on setting up a full-text search for some descriptions stored in my database. I'm running pg 8.4. >From what I can tell, there are two functions. One function, to_tsquery(), requires that boolean operators (&, |, !) are between every word. The other function, plainto_tsquery()

Re: [GENERAL] where clause question

2009-09-04 Thread Martin Gainty
you'll need to create an alias beforehand SELECT foo.foo_id, foo.name FROM foo, (SELECT * FROM foo, bar WHERE ...) bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de c

Re: [GENERAL] strange error occurs when adding index

2009-09-04 Thread Dimitri Fontaine
hubert depesz lubaczewski writes: > On Fri, Sep 04, 2009 at 05:42:31PM +0800, Juan Backson wrote: >> When I tried to add the following index, I get some strange error. Does >> anyone know what these errors mean and how to fix it? >> Here is the index query: >> create index idx_product_items_digi

Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries

2009-09-04 Thread William Temperley
Tom Lane writes: > Just out of curiosity, does anyone know of any ORM anywhere that doesn't > suck? They seem to be uniformly awful, at least in terms of their > interfaces to SQL databases. If there were some we could recommend, > maybe people would be less stuck with these bogus legacy archite

Re: [GENERAL] pg_ctl with unix domain socket?

2009-09-04 Thread Tom Lane
Josef Wolf writes: >> pg_ctl -Ddb -o "-h '' -k `pwd`/db" -l postgreslog start > This works, but when I add the -w option, it waits all the 60 seconds. I don't believe pg_ctl is smart enough to dredge the -k option out of -o and figure out that it has to look there for the socket. In general I'd

Re: [GENERAL] Create language PLPERL error

2009-09-04 Thread Alvaro Herrera
Shakil Shaikh wrote: > I tried installing this but am now getting the following error when > trying to install plperl: > > ERROR: could not access file "$libdir/plperl": No such file or directory > > Apparently this means that the version of Postgresql I have wasn't > compiled with support for

Re: [GENERAL] Create language PLPERL error

2009-09-04 Thread Shakil Shaikh
From: "Alvaro Herrera" Remove that, and install them from Martin Pitt's repository: https://launchpad.net/~pitti/+archive/postgresql The one-click installer does not integrate well with the platform. Avoid using them. Hi, thanks for the tip. I tried installing this but am now getting the fo

[GENERAL] where clause question

2009-09-04 Thread Scott Frankel
Hello, Is it possible to perform selects in a where clause of a statement? Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I'm looking for a way to recast it so that the select and from clauses refer

Re: [GENERAL] where clause question

2009-09-04 Thread David Fetter
On Fri, Sep 04, 2009 at 06:47:24AM -0700, Scott Frankel wrote: > > Hello, > > Is it possible to perform selects in a where clause of a statement? > > Given a statement as follows: > > SELECT foo.foo_id, foo.name > FROM foo, bar > WHERE foo.bar_id = bar.bar_id > AND bar.name = 'marti

Re: [GENERAL] Moving avg using SQL

2009-09-04 Thread Jeff Davis
On Fri, 2009-09-04 at 08:03 +0200, A. Kretschmer wrote: > Which version do you have? Since 8.4 we have CTE aka windowing > functions, a simple axample: Minor terminology correction: CTE stands for Common Table Expression, i.e. WITH [RECURSIVE]. Regards, Jeff Davis -- Sent via pgsql-ge

[GENERAL] Verifying a PITR

2009-09-04 Thread james bardin
What would be the best way to verify that a PITR came up with *all* the expected data? This is mostly for a controlled failover, where I manually bring down the primary server, and shouldn't ever lose a transaction. If I need to use something like txid_current(), how do I ensure that it's the last

Re: [GENERAL] where clause question

2009-09-04 Thread Merlin Moncure
On Fri, Sep 4, 2009 at 9:47 AM, Scott Frankel wrote: > > Hello, > > Is it possible to perform selects in a where clause of a statement? > > Given a statement as follows: > >    SELECT foo.foo_id, foo.name >    FROM foo, bar >    WHERE foo.bar_id = bar.bar_id >    AND bar.name = 'martini'; > > I'm l

Re: [GENERAL] Add Large Object support to database programmatically

2009-09-04 Thread acordner
Wow, do I feel stupid now! After creating the database, 15 tables and a trigger using the same exact process, I somehow overlooked adding the spaces to the front of each line. %-| Thanks, that worked great! I guess I stared at the code too long to see it. acordner wrote: > > I have been worki

Re: [GENERAL] where clause question

2009-09-04 Thread Alban Hertroys
On 4 Sep 2009, at 15:47, Scott Frankel wrote: Hello, Is it possible to perform selects in a where clause of a statement? Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I've explored the "where exis

Re: [GENERAL] comment on constraint

2009-09-04 Thread Tim Landscheidt
Andreas Kretschmer wrote: > There is a question in the german pg-forum: > It is possible to add a comment on a constraint, but \dd doesn't display > that comment. There is also a old question in this mailing-list without > an answer: > http://archives.postgresql.org/pgsql-general/2003-07/msg0144

[GENERAL] comment on constraint

2009-09-04 Thread Andreas Kretschmer
Hi, There is a question in the german pg-forum: It is possible to add a comment on a constraint, but \dd doesn't display that comment. There is also a old question in this mailing-list without an answer: http://archives.postgresql.org/pgsql-general/2003-07/msg01448.php I think, this is a bug, is

Re: [GENERAL] Got could not truncate directory "pg_multixact/offsets": apparent wraparound

2009-09-04 Thread Alvaro Herrera
Gordon Shannon escribió: > > Hello, running 8.4 on Centos. Been running production for 6 months. Never > saw this message until tonight: > > LOG: could not truncate directory "pg_multixact/offsets": apparent > wraparound My caffeing level is too low yet to know for sure, but I think this is

Re: [GENERAL] PL/Perl 64-bit and sending emails

2009-09-04 Thread Dimitri Fontaine
Hi, Steve Atkins writes: > On Sep 3, 2009, at 11:30 AM, Mark Lubratt wrote: >> Or, does someone know of another way to get the >> backend to send an email? > > Have a queue table in the database you put your emails into and an external > process that polls the table, sends the email and deletes

[GENERAL] where clause question

2009-09-04 Thread Scott Frankel
Hello, Is it possible to perform selects in a where clause of a statement? Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I'm looking for a way to recast it so that the select and from clauses r

Re: [GENERAL] strange error occurs when adding index

2009-09-04 Thread hubert depesz lubaczewski
On Fri, Sep 04, 2009 at 05:42:31PM +0800, Juan Backson wrote: > When I tried to add the following index, I get some strange error. Does > anyone know what these errors mean and how to fix it? > Here is the index query: > create index idx_product_items_digits on product_items using gist (digits > g

Re: [GENERAL] query speed question

2009-09-04 Thread Bill Moran
In response to Christopher Condit : > > > I have two tables that are georeferenced (although in this case I'm > > not using PostGIS) that I need to join. > > > A ( lat | lon | depth | value) > > > |A| = 1,100,000 > > > > > > B ( lat | lon | attributes) > > > |B| = 14,000,000 > > > > > > A is a spe

Re: [GENERAL] query speed question

2009-09-04 Thread Alban Hertroys
On 3 Sep 2009, at 23:11, Christopher Condit wrote: I have two tables that are georeferenced (although in this case I'm not using PostGIS) that I need to join. A ( lat | lon | depth | value) |A| = 1,100,000 B ( lat | lon | attributes) |B| = 14,000,000 A is a special case because the lat / lon

[GENERAL] strange error occurs when adding index

2009-09-04 Thread Juan Backson
Hi, When I tried to add the following index, I get some strange error. Does anyone know what these errors mean and how to fix it? Here is the index query: create index idx_product_items_digits on product_items using gist (digits gist_prefix_range_ops,product_id) Here is the error: NOTICE: __p

Re: [GENERAL] Need help in copying a table from one database to other

2009-09-04 Thread A. Kretschmer
In response to Rekha Ravi Pai : > Hi, > I want to copy a table say employeedetails from employee > database to library database. Can you help me giving the > detailed steps to do this. pg_dump -t | psql In you case: pg_dump -t employeedetails employee | psql library Regards, Andreas -- Andr

Re: [GENERAL] easy task: concurrent select-updates

2009-09-04 Thread Nickolay
Andy Colson wrote: Kevin McConnell wrote: I think you could also do something roughly similar in a statement by using a RETURNING clause on the update, such as: update msg set busy = true where id = (select min(id) from msg where busy = false) returning *; I had thought of that, but you'd

[GENERAL] Need help in copying a table from one database to other

2009-09-04 Thread Rekha Ravi Pai
Hi, I want to copy a table say employeedetails from employee database to library database. Can you help me giving the detailed steps to do this. Thanks And Regards, Rekha. -- Rekha Pai Senior Software Consultant

Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries

2009-09-04 Thread Dimitri Fontaine
Hi, Tom Lane writes: > Just out of curiosity, does anyone know of any ORM anywhere that doesn't > suck? They seem to be uniformly awful, at least in terms of their > interfaces to SQL databases. If there were some we could recommend, > maybe people would be less stuck with these bogus legacy ar