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 to grant ownership of every table in a database to a specific
user, you should be able to:
SELECT exec('alter table '||table_name||' OWNER TO {user}') FROM
information_schema.tables WHERE table_schema='{schema}'
{user} = the user you want to grant to (looks like 'neha' in your case)
{schema} = schema search path for your database (probably 'public' if you
haven't changed it)
HTH,
-Chris.
On Mon, May 16, 2011 at 9:41 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote:
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 privileges one by one table.
Is there a simple way to grant privileges on all tables, views, sequences
etc in a database.
Also , after granting all privileges , rename command is not granted.
Is it impossible to grant alter drop privileges to user which is granted
to only the user who created the object.
What is ALL privileges comprised of ?
Thanks
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[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 of tuples as they were originally in the array. What I now get is e.g. if the array contains A,B,C , I get rows B, C and A, but I'd like to get one row containing A, the B, then C My query is: select * from produkt inner join (select a_nr,komp as p_code from r_mi_sfm where a_nr=20110) as auftrag on (produkt.p_code = any(auftrag.p_code)); and explain says: Nested Loop (cost=201.83..2656.51 rows=26992 width=98) Join Filter: (inner.p_code = ANY (outer.komp)) - Index Scan using idx_r_mi_sfm_a_nr on r_mi_sfm (cost=0.00..25.39 rows=7 width=58) Index Cond: (a_nr = 20110) - Materialize (cost=201.83..278.95 rows=7712 width=40) - Seq Scan on produkt (cost=0.00..194.12 rows=7712 width=40) thanks for any input Gerhard
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 Mysql/ Postgres Production Servers. We have databases from 20 to 230 GB and it's not the OLTP just a simple OLAP where data is fetched and stored in some meaningful format. What are benefits why we used memcahed? What are the bottlenecks to meet? You need to read about memcached. Memcached is not something you enable. You have to program to it. Thanks Rick, just one question.. At what stage we need memcached what is the purpose of using it. I just want to know whether it is worth to use memcahced or not as per our requirements. I just built a software to enable query caching for PostgreSQL with using memcached, which adds a proxy layer. http://pgsnaga.blogspot.com/2011/03/postgresql-query-cache-pqc.html Much like with memcached, it looks like you still have to handle your own cache invalidation with your cache daemon, and it can return outdated or inconsistent results. Your examples clearly show that. It'd be nice if the google code front page clearly pointed out that it's not a fully transparent cache in that it can return stale or inconsistent data and the app has to be aware of that. 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? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 to throw at hardware. As for what the purpose of using it is: Read the documentation. http://code.google.com/p/memcached/wiki/FAQ memcached works best in environments where some data changes rarely and is queried extremely frequently. To use memcached, you must modify your software to check memcached for that data before querying postgresql for it. You must also modify your software to clear the memcached copy of the data when it changes the data in postgresql, otherwise you might get outdated copies of the data from memcached. Even then, you have to be very careful about managing the cache and avoiding race conditions if you store anything in memcached that you can't afford to have a bit out of date. In general, it's best for caching frequently queried things that don't change very often, don't change at all within a given user session, etc. If you want more help from the people here, spend some of your own time making an effort to more clearly explain what your app does, what your needs are, what language platform you use, etc etc etc. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 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 of tuples as they were originally in the array. What I now get is e.g. if the array contains A,B,C , I get rows B, C and A, but I'd like to get one row containing A, the B, then C My query is: select * from produkt inner join (select a_nr,komp as p_code from r_mi_sfm where a_nr=20110) as auftrag on (produkt.p_code = any(auftrag.p_code)); and explain says: Nested Loop (cost=201.83..2656.51 rows=26992 width=98) Join Filter: (inner.p_code = ANY (outer.komp)) - Index Scan using idx_r_mi_sfm_a_nr on r_mi_sfm (cost=0.00..25.39 rows=7 width=58) Index Cond: (a_nr = 20110) - Materialize (cost=201.83..278.95 rows=7712 width=40) - Seq Scan on produkt (cost=0.00..194.12 rows=7712 width=40) thanks for any input Gerhard
Re: [GENERAL] Memcached for Database server
2011/5/17 Craig Ringer cr...@postnewspapers.com.au: 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 Mysql/ Postgres Production Servers. We have databases from 20 to 230 GB and it's not the OLTP just a simple OLAP where data is fetched and stored in some meaningful format. What are benefits why we used memcahed? What are the bottlenecks to meet? You need to read about memcached. Memcached is not something you enable. You have to program to it. Thanks Rick, just one question.. At what stage we need memcached what is the purpose of using it. I just want to know whether it is worth to use memcahced or not as per our requirements. I just built a software to enable query caching for PostgreSQL with using memcached, which adds a proxy layer. http://pgsnaga.blogspot.com/2011/03/postgresql-query-cache-pqc.html Much like with memcached, it looks like you still have to handle your own cache invalidation with your cache daemon, and it can return outdated or inconsistent results. Your examples clearly show that. It'd be nice if the google code front page clearly pointed out that it's not a fully transparent cache in that it can return stale or inconsistent data and the app has to be aware of that. 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? there is also pgmemcache http://pgfoundry.org/projects/pgmemcache/ It is not a proxy but an extension to access memcache from within postgresql. You can use it to build your own querycache. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 that returns set of records. each record can have many fields. when you have value which is record, you can add .* to get all columns from it, separately. since each(hstore) is expression, and not column name/alias - you need to wrap is in () before adding .* Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Granting privileges on all tables,sequences , views, procedures
2011/5/17 Adarsh Sharma adarsh.sha...@orkash.com: 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 privileges one by one table. Is there a simple way to grant privileges on all tables, views, sequences etc in a database. Also , after granting all privileges , rename command is not granted. Is it impossible to grant alter drop privileges to user which is granted to only the user who created the object. What is ALL privileges comprised of ? Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general I think that you can grant membership in that role, so member role has ability to run ALTER, DROP etc. on tables, which even doesn't own. You can also change owner of object using ALTER TABLE for example ALTER TABLE sometable OWNER TO new_owner, but probably previous owner need to be granted as new_owner's member. To grant privileges on all tables, I think you can write sql function, which obtains list of all tables, schemas, views in database and fire GRANT ALL for each of them. Regards, Grzegorz Szpetkowski -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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, Bernardo Telles btel...@gmail.com wrote: Hi Pavel, I'm running 8.4 On Mon, May 16, 2011 at 3:58 PM, Pavel Stehule pavel.steh...@gmail.comwrote: Hello 2011/5/16 Bernardo Telles btel...@gmail.com: Wow, you guys are some fast-acting dudes (and yes, I am an adult, but a kid at heart). David, yup, that's exactly the part of the documentation that I read, and that is confusing me, because when I try it at home, it's not working. In fact, the exact example that I'm showing in the first email uses that assumption, but it seems to not be working :-/ What PostgreSQL version do you have? It's not supported on older versions regards Pavel Stehule But I'll take another look at the query tonight and see if I'm missing something. On Mon, May 16, 2011 at 1:55 PM, David Johnston pol...@yahoo.com wrote: Please read section “39.6.1. Returning From a Function” in the pl/pgsql section of the documentation (actually, you should read the entire section on pl/pgsql programming). “RETURN QUERY appends the results of executing a query to the function's result set.” [when used with RETURNING SETOF *] Concatenate and “append” are synonyms in this context; otherwise the above quote from section 39.6.1 is basically a word-for-word answer to your question. David J. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bernardo Telles Sent: Monday, May 16, 2011 1:13 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] How do we combine and return results from multiple queries in a loop? Hi John, Thanks for the quick response. I'll elaborate on the actual problem. Basically, I want to call: select * from partiesWithin(DAYTONA, FL, 5); The partiesWithin() function finds all zip codes (and zip_code centroids), then searches a 5 (or n) mile radius around those centroids for parties. Since each zip code has a 'point' column which is a PostGIS feature, I need to iterate through each of those points, and search for parties within 5 miles of each of the centroids, returning a concatenated query of all parties that were found in any of the queries. Someone mentioned that one way to do that is to use a temporary table inside the partiesWithin function. Any thoughts? On Mon, May 16, 2011 at 1:28 AM, John R Pierce pie...@hogranch.com wrote: On 05/15/11 8:53 PM, Bernardo Telles wrote: Hi there, We'd like to use a plpgsql function to use results from query A to execute several queries B, C, etc., and return the results of all B, C, etc queries as one result set. Would placing 'RETURN QUERY' inside a loop automatically concatenate all 'return query' results in the function's return? If not, how would we go about getting this result? all the queries would have to have the same fields to do this. if they do, then you can write it as a join or union. in your example case, its easy. select * from locations l join zipcode z on l.state = z.state where z.zipcode like '32301%'; this also would be more efficient than the way you proposed now, if you're thinking of a DIFFERENT problem thats more complex to solve, well, without knowing the actual problem there's not much I can suggest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 use nested queries in the from clause for this purpose. SELECT a, b, c, x, y, z, case when gr = x then 'x' when gr = y then 'y' when gr = z then 'z' end FROM ( Select distinct a,b,c, x,y,z, greatest(x,y,z) as gr from foo ) Regards On 17 May 2011 01:26, Ivan Sergio Borgonovo m...@webthatworks.it wrote: On Mon, 16 May 2011 20:05:45 -0400 David Johnston pol...@yahoo.com wrote: When asking for help on non-trivial SELECT queries it really helps to tell us the version of PG you are using so that responders know what functionality you can and cannot use. In this case specifically, whether WINDOW (and maybe WITH) clauses available? Unfortunately I'm on 8.3 so no WINDOW. I didn't even think of using them and I can't think of any way to use WINDOW/WITH but if there is a more readable solution that use them I'd like to see it even if I won't be able to use it. Of course I'm more interested to know if there is any cleaner solution for 8.3. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ordering of join using ON expression = any (array)
On Tue, May 17, 2011 at 3: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 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 of tuples as they were originally in the array. What I now get is e.g. if the array contains A,B,C , I get rows B, C and A, but I'd like to get one row containing A, the B, then C My query is: select * from produkt inner join (select a_nr,komp as p_code from r_mi_sfm where a_nr=20110) as auftrag on (produkt.p_code = any(auftrag.p_code)); and explain says: Nested Loop (cost=201.83..2656.51 rows=26992 width=98) Join Filter: (inner.p_code = ANY (outer.komp)) - Index Scan using idx_r_mi_sfm_a_nr on r_mi_sfm (cost=0.00..25.39 rows=7 width=58) Index Cond: (a_nr = 20110) - Materialize (cost=201.83..278.95 rows=7712 width=40) - Seq Scan on produkt (cost=0.00..194.12 rows=7712 width=40) your best best is to not use the 'any' construct but to expand the array with the index position which you can feed back into the query w/order by. In 8.1, there is an undocumented function which you can use to do this: information_schema._pg_expandarray(). It works more or less like unnest, but also returns the index position. select * from produkt inner join ( select pg_expandarray(a_nr,komp) as v from r_mi_sfm where a_nr=20110 ) as auftrag on produkt.p_code = (auftrag).v.x order by (auftrag).v.n; give it a shot -- if it doesn't work quite right let me know and i'll fix it. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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.5.orig.tar.gz (this is based upon looking at how citext handles it plus the documentation and information at pgxn.org) Note that it uses autoconf/make rather than relying on PGXS. Extension support is disabled with --disable-extension. When disabled, it still installs a compatibility script in contrib/ to allow existing scripts to function (but it just wraps CREATE EXTENSION rather than doing everything by hand). I was wondering if anyone could possibly look it over to see if I'm missing anything, or doing anything stupid that I shouldn't in the new order of things. I'll release it and make it available on PGXN once I'm happy it's OK. Many thanks, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ `-GPG Public Key: 0x25BFB848 Please GPG sign your mail. signature.asc Description: Digital signature
Re: [GENERAL] Remove Modifiers on Table
On Mon, May 16, 2011 at 4:58 PM, Bosco Rama postg...@boscorama.com 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 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? Seems very cluttered and messy for PostgreSQL. Can one sequence be attributed to multiple columns in multiple tables? I'm used to MySQL where this was as easy as running: CREATE TABLE test ( id INT PRIMARY KEY AUTO INCREMENT); I guess this is not the case in PostgreSQL, right? Thank you! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remove Modifiers on Table
On Tue, May 17, 2011 at 10:14 AM, Carlos Mennens carlos.menn...@gmail.com 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 messy for PostgreSQL. Can one sequence be attributed to multiple columns in multiple tables? you can use only one sequence for all yes... but then you will have id=1 in one table, id=2 in another, etc... i mean, it will generate one single list of values for all tables I'm used to MySQL where this was as easy as running: CREATE TABLE test ( id INT PRIMARY KEY AUTO INCREMENT); in postgres is as easy as CREATE TABLE test( id SERIAL PRIMARY KEY); hey! it's even less keystrokes! -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remove Modifiers on Table
On Tue, May 17, 2011 at 11:22 AM, Jaime Casanova ja...@2ndquadrant.com 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 learning a lot... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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
counterv...@gmail.comwrote:
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 to grant ownership of every table in a database to a specific
user, you should be able to:
SELECT exec('alter table '||table_name||' OWNER TO {user}') FROM
information_schema.tables WHERE table_schema='{schema}'
{user} = the user you want to grant to (looks like 'neha' in your case)
{schema} = schema search path for your database (probably 'public' if you
haven't changed it)
HTH,
-Chris.
On Mon, May 16, 2011 at 9:41 PM, Adarsh Sharma
adarsh.sha...@orkash.comwrote:
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 privileges one by one table.
Is there a simple way to grant privileges on all tables, views, sequences
etc in a database.
Also , after granting all privileges , rename command is not granted.
Is it impossible to grant alter drop privileges to user which is granted
to only the user who created the object.
What is ALL privileges comprised of ?
Thanks
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 Casanovaja...@2ndquadrant.com 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 learning a lot... 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.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-SERIAL Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remove Modifiers on Table
On Tue, May 17, 2011 at 12:12 PM, Raymond O'Donnell r...@iol.ie 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.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-SERIAL Wow I had no idea. So I do NOT need to manually create a sequence with: CREATE SEQUENCE blah_id_seq; And instead I can just use the SERIAL data type, unless I understood that wrong. I'm going to read up on the URL you provided. Thank you so much! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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'Donnellr...@iol.ie 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.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-SERIAL Wow I had no idea. So I do NOT need to manually create a sequence with: CREATE SEQUENCE blah_id_seq; And instead I can just use the SERIAL data type, unless I understood that wrong. I'm going to read up on the URL you provided. 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. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remove Modifiers on Table
On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell r...@iol.ie 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 have shed upon me makes my PG life so much easier! It's amazing what you can do with information once you know it exist :p -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remove Modifiers on Table
On Tue, May 17, 2011 at 12:57 PM, Carlos Mennens carlos.menn...@gmail.com wrote: On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnell r...@iol.ie 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. So if I have an existing column in my table with a INT data type, I can't seem to understand how to convert this on my 8.4 production server: ALTER TABLE users ALTER COLUMN id TYPE SERIAL; ERROR: type serial does not exist I verified from the docs that 8.4 does support SERIAL but how I convert this data type, I can't seem to figure out. Below is my table definition: orlando=# \d users Table public.users Column | Type | Modifiers +---+--- id | integer | not null fname | character varying(40) | not null lname | character varying(40) | not null email | character varying(40) | not null office | character varying(5) | not null dob| date | not null title | character varying(40) | not null Indexes: users_pkey PRIMARY KEY, btree (id) users_email_key UNIQUE, btree (email) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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-general
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
carlos.menn...@gmail.com wrote:
On Tue, May 17, 2011 at 12:38 PM, Raymond O'Donnellr...@iol.ie 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.
So if I have an existing column in my table with a INT data type, I
can't seem to understand how to convert this on my 8.4 production
server:
ALTER TABLE users ALTER COLUMN id TYPE SERIAL;
ERROR: type serial does not exist
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 default values from the sequence.
alter table users alter column id set default nextval('users_id_seq');
-- Establish a dependency between the column and the sequence.
alter sequence users_id_seq owned by users.id;
HTH
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remove Modifiers on Table
On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell r...@iol.ie 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 default values from the sequence.
alter table users alter column id set default nextval('users_id_seq');
-- Establish a dependency between the column and the sequence.
alter sequence users_id_seq owned by users.id;
Yup - that explains that the shortcut doesn't work for existing tables
but only during CREATE TABLE. Otherwise I will need to manually CREATE
SEQUENCE...blah blah blah.
Thank you!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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'Donnellr...@iol.ie 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 default values from the sequence.
alter table users alter column id set default nextval('users_id_seq');
-- Establish a dependency between the column and the sequence.
alter sequence users_id_seq owned by users.id;
Yup - that explains that the shortcut doesn't work for existing tables
but only during CREATE TABLE. Otherwise I will need to manually CREATE
SEQUENCE...blah blah blah.
It will work for an existing table if you are adding a column with
'type' SERIAL. You just cannot change an existing column to 'type' SERIAL.
Thank you!
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remove Modifiers on Table
On Tue, May 17, 2011 at 2:32 PM, Susan Cassidy scass...@edgewave.com 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
would I set this?
ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('foo_seq_id');
?
On Tue, May 17, 2011 at 2:33 PM, Adrian Klaver adrian.kla...@gmail.com wrote:
It will work for an existing table if you are adding a column with 'type'
SERIAL. You just cannot change an existing column to 'type' SERIAL.
Yup,
That's what I meant to say in a more clear and function statement ;)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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, unless the sequence has been told
what value to start with, it will start at 1.
Per the documentation:
SELECT setval('users_id_seq', 42); -- Next nextval (insert) will return 43
Susan Cassidy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[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-3.0.8. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Remove Modifiers on Table
Carlos Mennens wrote:
On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell r...@iol.ie 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 default values from the sequence.
alter table users alter column id set default nextval('users_id_seq');
-- Establish a dependency between the column and the sequence.
alter sequence users_id_seq owned by users.id;
Yup - that explains that the shortcut doesn't work for existing tables
but only during CREATE TABLE. Otherwise I will need to manually CREATE
SEQUENCE...blah blah blah.
Yeah. We went through this one too many times and finally came up with
this function to handle it all for us. It's crude but it works for us.
create or replace function make_serial(text, text) returns void as
$$
declare
tbl text;
col text;
seq text;
seq_l text;
begin
seq := quote_ident($1||'_'||$2||'_seq');
seq_l := quote_literal($1||'_'||$2||'_seq');
tbl := quote_ident($1);
col := quote_ident($2);
raise notice 'seq = %, tbl = %, col = %', seq, tbl, col;
execute 'create sequence '||seq;
execute 'alter table '||tbl||' alter column '||col||' set default
nextval('||seq_l||')';
execute 'alter sequence '||seq||' owned by '||tbl||'.'||col;
execute 'select setval('||seq_l||', (select max('||col||') from
'||tbl||'))';
end;
$$
language plpgsql;
Then you call it thusly:
select make_serial('users', 'id');
HTH
Bosco.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[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] Query to return every 1st Sat of a month between two dates
On Wed, May 11, 2011 at 10:22 AM, Alex - ainto...@hotmail.com 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 ()
from generate_series('2011-02-01'::date, now()::date, '1
day') as s(d)
where extract(dow from d) = 6
)
select d from q where (lag is null) or (extract(month from d)
extract(month from lag));
where '2011-02-01' is the initial date and now()::date - '1 day' the
final one, replace them with you're own range
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] re-install postgres/postGIS without Loosing data??
Hi salah, I tried C:\Program Files\PostgreSQL\9.0\binpg_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: database system was shut down at 2011-05-17 22:45:00 EEST 2011-05-18 02:09:36 EEST LOG: could not remove cache file base/32803/pg_internal.init: Permission denied 2011-05-18 02:09:36 EEST PANIC: could not open file pg_xlog/00010002 (log file 0, segment 2): Permission denied This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2011-05-18 02:09:36 EEST LOG: startup process (PID 5636) exited with exit code 3 2011-05-18 02:09:36 EEST LOG: aborting startup due to startup process failure Thats why i am considering to re-install postgres From: salah jubeh s_ju...@yahoo.com To: G. P. pagomen2...@yahoo.gr Sent: Wed, May 18, 2011 1:05:38 AM Subject: Re: [GENERAL] re-install postgres/postGIS without Loosing data?? Have a look on http://www.postgresql.org/docs/current/static/app-pg-ctl.html Regards From: G. P. pagomen2...@yahoo.gr To: pgsql-general@postgresql.org Sent: Tuesday, May 17, 2011 11:54 PM Subject: [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] 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 dates is slated for general release with RoR-3.0.8. ... now if only Java would support infinite dates, too. Alas, unlike RoR, I doubt there'll be a quick we've fixed this, grab the next point release post for Java. Not even the 3rd party JodaTime date/time library supports infinite intervals, the +infinity date, etc, so I have to represent unbounded intervals with some distant future date, or use null. Neither option is very palatable. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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\binpg_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 LOG: could not remove cache file base/32803/pg_internal.init: Permission denied/* */2011-05-18 02:09:36 EEST PANIC: could not open file pg_xlog/00010002 (log file 0, segment 2): Permission denied/* You cannot restart postgresql using pg_ctl running under your normal user account if it was originally set up as a Windows service running as the postgres user. You must use runas.exe to run pg_ctl as user postgres, or just use the service control panel (services.msc) or net service command to control it. Rather than trying to reinstall, which might make the problem worse rather than better, stop trying to change things and think for a moment. Restart the computer, then check the services control panel and see if postgresql is shown as running there. If it is not, try to start it. If it does not start, examine the system event logs (Event Viewer in the start menu) to see if there are any error messages from the service. Also check the PostgreSQL logs, which will be in C:/Program Files/PostgreSQL/9.0/data/pg_log to see if there are any informative error messages at the bottom of the most recent log file. Once you've done that, you'll have some idea what's wrong and what to do next. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can't unsubscribe
Wells Oliver woli...@padres.com 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: http://archives.postgresql.org/pgsql-general List-Help: mailto:majord...@postgresql.org?body=help List-ID: pgsql-general.postgresql.org List-Owner: mailto:pgsql-general-ow...@postgresql.org List-Post: mailto:pgsql-general@postgresql.org List-Subscribe: mailto:majord...@postgresql.org?body=sub%20pgsql-general List-Unsubscribe: mailto:majord...@postgresql.org?body=unsub%20pgsql-general Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
