Re: [GENERAL] Memcached for Database server

Hi, 2011/5/18 Tatsuo Ishii : >> How do you handle statements that rely on current_timestamp, random(), >> etc? What about if their reliance is via a function? Is that just an >> understood limitation of the cache, that it'll cache even queries that >> don't really make sense to cache? > > Probably

Re: [GENERAL] Memcached for Database server

> How do you handle statements that rely on current_timestamp, random(), > etc? What about if their reliance is via a function? Is that just an > understood limitation of the cache, that it'll cache even queries that > don't really make sense to cache? Probably we should cache the result of a quer

Re: [GENERAL] Can't unsubscribe

On 05/17/2011 11:12 AM, Wells Oliver wrote: Sorry to pester the list with this, but I've unsubscribed @ the web interface and I'm still getting email. Can an admin help me out here? Did you get an email asking you to confirm your request to unsubscribe? - Wells -- Adrian Klaver adrian.kl

Re: [GENERAL] Can't unsubscribe

Wells Oliver wrote: > Sorry to pester the list with this, but I've unsubscribed @ the web interface > and I'm still getting email. Can an admin help me out here? List-Archive: List-Help: List-ID: List-Ow

Re: [GENERAL] re-install postgres/postGIS without Loosing data??

On 18/05/2011 7:13 AM, G. P. wrote: C:\Program Files\PostgreSQL\9.0\bin>pg_ctl.exe -U postgres restart pg_ctl: PID file "C:/Program Files/PostgreSQL/9.0/data/postmaster.pid" */2011-05-18 02:09:26 EEST LOG: database system was shut down at 2011-05-17 22:45:00 EEST/* */2011-05-18 02:09:36 EEST

Re: [GENERAL] Infinity dates in RoR was How to handle bogus nulls from ActiveRecord

On 18/05/2011 4:02 AM, James B. Byrne wrote: On Fri, May 13, 2011 13:04, James B. Byrne wrote: I have opened an issue for this with the ActiveRecord folks. https://github.com/rails/rails/issues/544 This has been addressed by the AR team and is committed to master. +-Infinity support for da

Re: [GENERAL] re-install postgres/postGIS without Loosing data??

Hi salah, I tried C:\Program Files\PostgreSQL\9.0\bin>pg_ctl.exe -U postgres restart pg_ctl: PID file "C:/Program Files/PostgreSQL/9.0/data/postmaster.pid" does not exist Is server running? starting server anyway server starting but i get the following error: 2011-05-18 02:09:26 EEST LOG: dat

Re: [GENERAL] Query to return every 1st Sat of a month between two dates

On Wed, May 11, 2011 at 10:22 AM, Alex - wrote: > Hi, > is there an easy way to return the date of every first Saturday of a month > in a data range i.e. 2011-2013 if you want a list of the first saturdays of every month and you're using at least 8.4: with q as (select d, lag(d) over ()

[GENERAL] re-install postgres/postGIS without Loosing data??

Hi, I have installed postres/postgis 9 in win7. I tried to edit pg_hba.conf not as postgres user and although i cancelled all changes I can start my postgres any more.. Now I am thinking of re-installing postgresql but for sure i dont loose my data ... Any ideas ? Thx George

Re: [GENERAL] Remove Modifiers on Table

Carlos Mennens wrote: > On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell wrote: >> That's because of what I just mentioned above. :-) It's not a type: it's >> just a shortcut. What you need to do instead is something like this: >> >> -- Create the sequence. >> create sequence users_id_seq; >>

[GENERAL] Infinity dates in RoR was How to handle bogus nulls from ActiveRecord

On Fri, May 13, 2011 13:04, James B. Byrne wrote: > > I have opened an issue for this with the ActiveRecord folks. > > https://github.com/rails/rails/issues/544 > This has been addressed by the AR team and is committed to master. +-Infinity support for dates is slated for general release with RoR

Re: [GENERAL] Remove Modifiers on Table

> Doesn't the SERIAL shortcut automatically do this on the fly? How > would I set this? > > ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('foo_seq_id'); If you have existing data, say with values 1, 2, 3, etc. and you set the column to start using a sequence nextval as default, unle

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 2:32 PM, Susan Cassidy wrote: > Don't forget to use setval to set the current value of the sequence to the > highest number used in the data already, so that the next insertion uses a > new, unused value. Doesn't the SERIAL shortcut automatically do this on the fly? How

Re: [GENERAL] Remove Modifiers on Table

Don't forget to use setval to set the current value of the sequence to the highest number used in the data already, so that the next insertion uses a new, unused value. Susan Cassidy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: ht

Re: [GENERAL] Remove Modifiers on Table

On 05/17/2011 11:29 AM, Carlos Mennens wrote: On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell wrote: That's because of what I just mentioned above. :-) It's not a type: it's just a shortcut. What you need to do instead is something like this: -- Create the sequence. create sequence user

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell wrote: > That's because of what I just mentioned above. :-) It's not a type: it's > just a shortcut. What you need to do instead is something like this: > >  -- Create the sequence. >  create sequence users_id_seq; > >  -- Tell the column to pull

Re: [GENERAL] Remove Modifiers on Table

On 17/05/2011 19:07, Carlos Mennens wrote: On Tue, May 17, 2011 at 12:57 PM, Carlos Mennens wrote: On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell wrote: Yes, that's exactly right - SERIAL does it all for you. The mistake some people make, on the other hand, is thinking that SERIAL is a

[GENERAL] Can't unsubscribe

Sorry to pester the list with this, but I've unsubscribed @ the web interface and I'm still getting email. Can an admin help me out here? - Wells -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-g

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 12:57 PM, Carlos Mennens wrote: > On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell wrote: >> Yes, that's exactly right - SERIAL does it all for you. The mistake some >> people make, on the other hand, is thinking that SERIAL is a type in its own >> right - it's not, it

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell wrote: > Yes, that's exactly right - SERIAL does it all for you. The mistake some > people make, on the other hand, is thinking that SERIAL is a type in its own > right - it's not, it just does all those steps automatically. This information you

Re: [GENERAL] Remove Modifiers on Table

On 17/05/2011 17:35, Carlos Mennens wrote: On Tue, May 17, 2011 at 12:12 PM, Raymond O'Donnell wrote: Well, the SERIAL pseudo-type creates the sequence, associates it with the column, and sets a DEFAULT on the column which executes the nextval() function on the sequence - all in one fell swoop.

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 12:12 PM, Raymond O'Donnell wrote: > Well, the SERIAL pseudo-type creates the sequence, associates it with the > column, and sets a DEFAULT on the column which executes the nextval() > function on the sequence - all in one fell swoop. Read all about it here: > > http://www.

Re: [GENERAL] Remove Modifiers on Table

On 17/05/2011 16:26, Carlos Mennens wrote: On Tue, May 17, 2011 at 11:22 AM, Jaime Casanova wrote: in postgres is as easy as CREATE TABLE test( id SERIAL PRIMARY KEY); hey! it's even less keystrokes! I don't understand how this command above is associated with being able to auto increment

Re: [GENERAL] Granting privileges on all tables,sequences , views, procedures

Ah - my apologies, I realized you will need to add a function for exec (which may also require plpgsql language added to your database). On Tue, May 17, 2011 at 12:00 AM, Christopher Opena wrote: > Per 8.0 documentation, in order to ALTER a table (including rename), you > have to be the owner of

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 11:22 AM, Jaime Casanova wrote: > in postgres is as easy as > > CREATE TABLE test( >  id SERIAL PRIMARY KEY); > > hey! it's even less keystrokes! I don't understand how this command above is associated with being able to auto increment the 'id' column. Sorry I'm still lear

Re: [GENERAL] Remove Modifiers on Table

On Tue, May 17, 2011 at 10:14 AM, Carlos Mennens wrote: > > Yes that worked perfect! I'm just curious if I have 20 tables and then > want all the 'id' columns to be auto incrementing , that means I have > to have 20 listed sequences for all 20 unique tables? yes > Seems very > cluttered and mess

Re: [GENERAL] Remove Modifiers on Table

On Mon, May 16, 2011 at 4:58 PM, Bosco Rama wrote: > If you are truly intent on removing the sequence you'll need to do the > following: > >   alter sequence users_seq_id owned by NONE >   alter table users alter column id drop default >   drop sequence users_seq_id Yes that worked perfect! I'm j

[GENERAL] Adapting existing extensions to use CREATE EXTENSION

Hi folks, I've been looking at converting my existing "debversion" datatype extension to use the proper "CREATE EXTENSION" facility for 9.1, while also being backward compatible with 8.4 and 9.0. My initial work on the conversion is here: http://people.debian.org/~rleigh/postgresql-debversion_1.0

Re: [GENERAL] ordering of join using ON expression = any (array)

On Tue, May 17, 2011 at 3:06 AM, Gerhard Hintermayer wrote: > Hi, > is there a way to sort the joined tuples in the way they are in a the joined > array ? BTW I'm using 8.4 (_I_ know I should upgrade, but management thinks > we shouldn't :-( ) > I'd like to join 2 tables based on a column, where t

Re: [GENERAL] find the greatest, pick it up and group by

Hi The method you're using is functionally correct and quite efficient if a little on the verbose side. Other non-postgres variants of SQL have a "DECODE" function which comes in very handy. I dont believe postgres has any equivalent. (Postgres decode() does something entirely differnt). I often

Re: [GENERAL] How do we combine and return results from multiple queries in a loop?

Okay, as it turns out. the query was in fact running as expected (i.e. concatenating results from RETURN QUERY). I just had a horribly wrong initial query with which to loop...wow I feel stupid for raising all the fuss. Thanks again, everyone, for all your help! On Mon, May 16, 2011 at 10:17 PM, B

Re: [GENERAL] Granting privileges on all tables,sequences , views, procedures

2011/5/17 Adarsh Sharma : > Dear all, > > Today I grant a user all privileges to all tables in  a database by > > grant all privileges on   svo_data_social to neha ;              grant all > privileges on   svo_phrases to neha ; > .. . . > . > ... > > Now i find it very uncomfort   to grant

Re: [GENERAL] each (hstore)

On Tue, May 17, 2011 at 12:27:08PM +0100, Tarlika Elisabeth Schmitz wrote: > On Pavel's blog, I found this statement, which does just what I need: > > select (each(hstore(foo))).* from foo; > > > Excuse the daft question, but could, please, you explain what ".*" does? each(hstore) is a function

[GENERAL] each (hstore)

On Pavel's blog, I found this statement, which does just what I need: select (each(hstore(foo))).* from foo; Excuse the daft question, but could, please, you explain what ".*" does? -- Best Regards, Tarlika Elisabeth Schmitz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.o

Re: [GENERAL] Memcached for Database server

2011/5/17 Craig Ringer : > On 05/17/2011 01:38 PM, Satoshi Nagayasu wrote: >> >> Hi, >> >> 2011/05/17 14:31, Adarsh Sharma wrote: >>> >>> Rick Genter wrote: On May 16, 2011, at 10:09 PM, Adarsh Sharma wrote: > Dear all, > > I need to research on Memcache in the next

Re: [GENERAL] ordering of join using ON expression = any (array)

Sorry, I'm using 8.1, not 8.4. On Tue, May 17, 2011 at 10:06 AM, Gerhard Hintermayer < gerhard.hinterma...@gmail.com> wrote: > Hi, > is there a way to sort the joined tuples in the way they are in a the > joined array ? BTW I'm using 8.4 (_I_ know I should upgrade, but management > thinks we shou

Re: [GENERAL] Memcached for Database server

On 05/17/2011 01:31 PM, Adarsh Sharma wrote: At what stage we need memcached & what is the purpose of using it. You might not need it. Depends on the nature of your app, its performance requirements, how strict it is about always getting consistent & current data, and how much money you have

Re: [GENERAL] Memcached for Database server

On 05/17/2011 01:38 PM, Satoshi Nagayasu wrote: Hi, 2011/05/17 14:31, Adarsh Sharma wrote: Rick Genter wrote: On May 16, 2011, at 10:09 PM, Adarsh Sharma wrote: Dear all, I need to research on Memcache in the next few days. What I want to know is it worth to have memcahed enable in our My

[GENERAL] ordering of join using ON expression = any (array)

Hi, is there a way to sort the joined tuples in the way they are in a the joined array ? BTW I'm using 8.4 (_I_ know I should upgrade, but management thinks we shouldn't :-( ) I'd like to join 2 tables based on a column, where the column is an array in one table, but I still need to keep the order

Re: [GENERAL] Granting privileges on all tables,sequences , views, procedures

Per 8.0 documentation, in order to ALTER a table (including rename), you have to be the owner of a table: "You must own the table to use ALTER TABLE; except for ALTER TABLE OWNER, which may only be executed by a superuser." http://www.postgresql.org/docs/8.0/static/sql-altertable.html If you want