Thread: Rollback in Postgres
Hi all....
This is a very basic question.....can we roll back data after we run a query.
I know that a delete within a transaction can be rolled back. But how about independent delete queries???
If i ran a delete statement and lost data...how do i recover. I know that oracle has this provision of rollingback queries.
Iam surprised iam not able to find the same in postgres.
Sam
On Fri, 2008-07-11 at 11:43 -0400, samantha mahindrakar wrote: > Hi all.... > This is a very basic question.....can we roll back data after we run a > query. > I know that a delete within a transaction can be rolled back. But how > about independent delete queries??? > If i ran a delete statement and lost data...how do i recover. I know > that oracle has this provision of rollingback queries. > Iam surprised iam not able to find the same in postgres. > > Sam Postgres certainly can roll back queries, table creations, and many other actions. You can find more information about rollback here: http://www.postgresql.org/docs/8.3/interactive/sql-rollback.html Best of luck in your endeavor :) -Mark
On Fri, Jul 11, 2008 at 9:43 AM, samantha mahindrakar <sam.mahindrakar@gmail.com> wrote: > Hi all.... > This is a very basic question.....can we roll back data after we run a > query. > I know that a delete within a transaction can be rolled back. But how about > independent delete queries??? > If i ran a delete statement and lost data...how do i recover. I know that > oracle has this provision of rollingback queries. > Iam surprised iam not able to find the same in postgres. If you were not in a query, then you cannot just roll back. This is because each statement is an individual transaction and a delete query "outside" a transaction is actually a begin;delete...;commit; in nature. Oracle only supports the rollback after commit if you have the right module installed and activated. And it uses up a fair bit of disk space to do it. TANSTAAFL. IF you have PITR setup in postgresql then you can recover to a previous point in time. Otherwise, you need to restore from backups.
On Fri, 2008-07-11 at 11:21 -0600, Scott Marlowe wrote: > rollback after commit Are you sure? Personally I don't think its viable. If it really does that it will would also need to rollback all transactions whose changes depend upon the earlier transaction. It would also need to track transactions that read data changed by an earlier transaction and then makes changes to the database. It's got no way to track that without extensive and costly additional infrastructure, since after transaction commit row locking information can be cleaned up by read-only transactions accessing those changed data blocks. Flashback query allows reading data as it was at a certain point in the past. We might one day provide that, but undoing individual transactions isn't ever going to be feasible, without unknowable risk. Not jumping on you, just think their marketing is ahead of the reality. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
In addition to allowing you to read old data, Flashback will allow you to rollback to a point in time, including returninga single table to a specific state. Flashback database is like PITR without the log files. It started in 9i and improved dramatically in 10g. 11g has made additional improvements. http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/intro007.htm http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmflash.htm Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ Postgres Forums http://postgres.enterprisedb.com/forum.do --- On Fri, 7/11/08, Simon Riggs <simon@2ndquadrant.com> wrote: > From: Simon Riggs <simon@2ndquadrant.com> > Subject: Re: [SQL] Rollback in Postgres > To: "Scott Marlowe" <scott.marlowe@gmail.com> > Cc: "samantha mahindrakar" <sam.mahindrakar@gmail.com>, pgsql-sql@postgresql.org > Date: Friday, July 11, 2008, 2:58 PM > On Fri, 2008-07-11 at 11:21 -0600, Scott Marlowe wrote: > > rollback after commit > > Are you sure? > > Personally I don't think its viable. If it really does > that it will > would also need to rollback all transactions whose changes > depend upon > the earlier transaction. It would also need to track > transactions that > read data changed by an earlier transaction and then makes > changes to > the database. It's got no way to track that without > extensive and costly > additional infrastructure, since after transaction commit > row locking > information can be cleaned up by read-only transactions > accessing those > changed data blocks. > > Flashback query allows reading data as it was at a certain > point in the > past. We might one day provide that, but undoing individual > transactions > isn't ever going to be feasible, without unknowable > risk. > > Not jumping on you, just think their marketing is ahead of > the reality. > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
On Fri, 2008-07-11 at 18:56 -0700, Lewis Cunningham wrote: > In addition to allowing you to read old data, Flashback will allow you > to rollback to a point in time, including returning a single table to > a specific state. Flashback database is like PITR without the log > files. Like I said: you cannot rollback a single transaction after commit. Please don't put links to copyrighted material on our lists. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > > Please don't put links to copyrighted material on our lists. That's an odd thing to say, given that virtually every link on our lists probably points to material copyrighted in some way. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Sat, 2008-07-12 at 09:40 +0100, Dave Page wrote: > On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > > > > Please don't put links to copyrighted material on our lists. > > That's an odd thing to say, given that virtually every link on our > lists probably points to material copyrighted in some way. Prudence is all I ask for. We don't need to provide additional advertising for others, nor do we wish to embroil ourselves in accusations over copyright violations. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
On Sat, Jul 12, 2008 at 3:20 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > > On Sat, 2008-07-12 at 09:40 +0100, Dave Page wrote: >> On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs <simon@2ndquadrant.com> wrote: >> > >> > Please don't put links to copyrighted material on our lists. >> >> That's an odd thing to say, given that virtually every link on our >> lists probably points to material copyrighted in some way. > > Prudence is all I ask for. We don't need to provide additional > advertising for others, nor do we wish to embroil ourselves in > accusations over copyright violations. Neither do we need to bury our heads in the sand and not know what's happening in the world at large around us. I appreciated the links to the Oracle docs. I can't see how a link to oracle documentation that is open and requires no login can be an issue of copyright violation. I might see some issue of "poisoning the well" as regards patented methods getting into pgsql, but that's about it. As for advertising, I doubt there's anyone on this list that just decided to switch to Oracle over just those links. What I would appreciate as regards Oracle's flashback technology would have been a link to a well written review showing the warts as well as the beauty. I've found that Oracle stuff sounds good on paper, and turns into a giant maintenance nightmare upon deployment. But that's just what I've seen looking over Oracle DBA shoulders in the past.
> > Please don't put links to copyrighted material on our > lists. > Postgres docs are copyrighted. The oracle docs are free to access just like the postgres docs. What is the issue? LewisC
--- On Sat, 7/12/08, Scott Marlowe <scott.marlowe@gmail.com> wrote: > What I would appreciate as regards Oracle's flashback > technology would > have been a link to a well written review showing the warts > as well as > the beauty. I've found that Oracle stuff sounds good > on paper, and > turns into a giant maintenance nightmare upon deployment. > But that's > just what I've seen looking over Oracle DBA shoulders > in the past. Oracle-base is a site I trust and use. Tim writes very good articles and this is one he did recently covering flashbackin 11g. The example on flashback transaction is the best I've seen. http://www.oracle-base.com/articles/11g/FlashbackAndLogminerEnhancements_11gR1.php Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ Postgres Forums http://postgres.enterprisedb.com/forum.do
I didnt no the thread would become a postgresVSoracle thing. I just lost couple of thousand rows and could not retrieve them back, so i wanted to know if postgres had some way to get it back. Iam just a few days expereinced in postgres hence iam still discovering its features.
No intention of comparing the two technologies......just trying find a solution and ended up comparing because i had worked in oracle before and very well knewit provide a rollback option for queries.
I dont see anything wrong in knowing what features oracle has.
Peace
Sam
On 7/12/08, Lewis Cunningham <lewisc@rocketmail.com> wrote:
--- On Sat, 7/12/08, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> What I would appreciate as regards Oracle's flashback
> technology would
> have been a link to a well written review showing the warts
> as well as
> the beauty. I've found that Oracle stuff sounds good
> on paper, and
> turns into a giant maintenance nightmare upon deployment.
> But that's
> just what I've seen looking over Oracle DBA shoulders
> in the past.
Oracle-base is a site I trust and use. Tim writes very good articles and this is one he did recently covering flashback in 11g. The example on flashback transaction is the best I've seen.
http://www.oracle-base.com/articles/11g/FlashbackAndLogminerEnhancements_11gR1.php
Lewis R Cunningham
An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/
Postgres Forums
http://postgres.enterprisedb.com/forum.do
On Mon, Jul 14, 2008 at 9:20 AM, samantha mahindrakar <sam.mahindrakar@gmail.com> wrote: > I didnt no the thread would become a postgresVSoracle thing. I just lost > couple of thousand rows and could not retrieve them back, so i wanted to > know if postgres had some way to get it back. Iam just a few days > expereinced in postgres hence iam still discovering its features. > No intention of comparing the two technologies......just trying find a > solution and ended up comparing because i had worked in oracle before and > very well knewit provide a rollback option for queries. > I dont see anything wrong in knowing what features oracle has. Much like the processes that make up postgresql, the discussion can fork in any number of directions. :) I just lost a months worth of stats data myself, so join the club. It wasn't critical data, but it would have been nice to have kept around...
> I just lost a months worth of stats data myself, so join the club. It > wasn't critical data, but it would have been nice to have kept > around... I also think there could be a TODO item in it. If vacuum instead of removing items, somehow stashed them away in a storage limited archive it would be possible to do a SELECT...AS OF TIMESTAMP. The idea is of course to be able to retrieve rows that really are deleted, but are still on disk as non-vacuumed or vacuumed and not removed completely. And it would also take a 2. stage vacuumer to keep the storage within its limits. I don't say it's an important feature, but it would come in handy for people who really really need it. And perhaps a developer wouldn't mind scratching this itch some time in the future. -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: kaare@jasonic.dk
On Mon, Jul 14, 2008 at 12:59 PM, Kaare Rasmussen <kaare@jasonic.dk> wrote: > I also think there could be a TODO item in it. If vacuum instead of removing > items, somehow stashed them away in a storage limited archive it would be > possible to do a SELECT...AS OF TIMESTAMP. This sounds a lot like the functionality that a temporal data model would give you. In this model you never delete tuples from your database, your only insert and update tuples that are valid for specific periods of time. If you want to contribute development time, I would check out postgresql's temporal db project on PGfoundry. This project is just getting started and could benefit from a lot of development help. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Kaare Rasmussen escribió: > I don't say it's an important feature, but it would come in handy for people > who really really need it. And perhaps a developer wouldn't mind scratching > this itch some time in the future. It would need to be enabled beforehand, and most people I've seen for which "it would come in handy" wouldn't have enabled it. (FWIW this feature used to exist in the Berkeley code, under the cool name "time travel", and was removed a long time ago.) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Mon, 2008-07-14 at 21:59 +0200, Kaare Rasmussen wrote: > > I just lost a months worth of stats data myself, so join the club. It > > wasn't critical data, but it would have been nice to have kept > > around... > > I also think there could be a TODO item in it. If vacuum instead of removing > items, somehow stashed them away in a storage limited archive it would be > possible to do a SELECT...AS OF TIMESTAMP. > > The idea is of course to be able to retrieve rows that really are deleted, but > are still on disk as non-vacuumed or vacuumed and not removed completely. And > it would also take a 2. stage vacuumer to keep the storage within its limits. I've got the design all worked out for this. The "only" thing we need is a VACUUM that will remove unseen data from within the middle of the sum-of-all-snapshots, if there is a gap. At the moment we never remove rows beyond global xmin, but we could iff the transactions at xmin promise never to update data. That should go on the TODO list as a precursor. Some discussion required :-) -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
> which "it would come in handy" wouldn't have enabled it. (FWIW this > feature used to exist in the Berkeley code, under the cool name "time > travel", and was removed a long time ago.) No, it didn't AFAIK. Timetravel kept all tuples in the database with all indexes and constraints active at all time. That's not the case with the flashback technology. You put aside some storage space that you don't need for something else. When that space is spent, tuples start dropping off the edge. I've talked to people who was very much happy with this feature. Mostly DBA's recovering from their own stupid mistakes of course :-) But yes, it has to be enabled, and yes it has to have a performance cost somehow, but people are requesting it, and somehow I don't think Oracle developed the feature just for fun. If you plug into Postgres' vacuum it would be rather cheap to make, I recon. I wouldn't worry about query speed as I guess that the use cases for retrieving already deleted rows don't aren't performance dependant. -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: kaare@jasonic.dk
> This sounds a lot like the functionality that a temporal data model > would give you. In this model you never delete tuples from your > database, your only insert and update tuples that are valid for > specific periods of time. Isn't this exactly what Alvaro describes? The time travel feature that was removed because it made Postgres too slow to use in production? -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: kaare@jasonic.dk
On Mon, Jul 14, 2008 at 1:38 PM, Kaare Rasmussen <kaare@jasonic.dk> wrote: > Isn't this exactly what Alvaro describes? The time travel feature that was > removed because it made Postgres too slow to use in production? No, I imagine that time travel was built into the Postgresql architecture and would work automatically with transaction ids and tuple ids. On the other hand, temporal tables/schemes are implemented by the data modeller. Also the associated temporal operations on the data would be handled by client DML designed to simulate temporal data operations. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
--- On Mon, 7/14/08, Kaare Rasmussen <kaare@jasonic.dk> wrote: > But yes, it has to be enabled, and yes it has to have a > performance cost > somehow, but people are requesting it, and somehow I AFAIK, It is built from undo so there is no ADDITIONAL overhead. It just saves the undo that is created anyway for any DMLanyway. That undo is already on disk. Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ Database Wisdom http://databasewisdom.com
Lewis Cunningham wrote: > --- On Mon, 7/14/08, Kaare Rasmussen <kaare@jasonic.dk> wrote: > > > But yes, it has to be enabled, and yes it has to have a > > performance cost > > somehow, but people are requesting it, and somehow I > > AFAIK, It is built from undo so there is no ADDITIONAL overhead. It > just saves the undo that is created anyway for any DML anyway. That > undo is already on disk. Which means it doesn't work for us, because we don't have UNDO (we only have REDO). -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Mon, 2008-07-14 at 22:38 +0200, Kaare Rasmussen wrote: > > This sounds a lot like the functionality that a temporal data model > > would give you. In this model you never delete tuples from your > > database, your only insert and update tuples that are valid for > > specific periods of time. > > Isn't this exactly what Alvaro describes? The time travel feature that was > removed because it made Postgres too slow to use in production? Similar. Performance is the issue to be solved with row removal, yes. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Kaare Rasmussen <kaare@jasonic.dk> writes:
> But yes, it has to be enabled, and yes it has to have a performance cost
> somehow, but people are requesting it, and somehow I don't think Oracle
> developed the feature just for fun.
No, they developed it for marketing.
Keep in mind that Oracle has six thousand full-time developers and an
already extremely mature database. Stuff that they see fit to add is
not necessarily going to be on our radar screen in the foreseeable
future.
regards, tom lane
On Mon, Jul 14, 2008 at 9:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Kaare Rasmussen <kaare@jasonic.dk> writes: >> But yes, it has to be enabled, and yes it has to have a performance cost >> somehow, but people are requesting it, and somehow I don't think Oracle >> developed the feature just for fun. > > No, they developed it for marketing. No, they developed it because it was needed. In addition to knowing quite a bit about the design and implementation of this feature, I've been a production Oracle DBA and can speak from experience. In fact, one of the primary reasons for creating this feature was for the very purpose of why the original poster needed it, human-induced disasters/mistakes. While flashback does give you the ability to perform temporal-related queries, it was designed to allow recovery of individual database objects (or the entire database itself) to a certain point in time, thereby giving DBAs the ability to undo changes (intentional or otherwise). > Keep in mind that Oracle has six thousand full-time developers and an > already extremely mature database. True. > Stuff that they see fit to add is not necessarily going to be on our radar > screen in the foreseeable future. Agreed. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
On Mon, 2008-07-14 at 22:54 -0400, Jonah H. Harris wrote: > On Mon, Jul 14, 2008 at 9:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Kaare Rasmussen <kaare@jasonic.dk> writes: > >> But yes, it has to be enabled, and yes it has to have a performance cost > >> somehow, but people are requesting it, and somehow I don't think Oracle > >> developed the feature just for fun. > > > > No, they developed it for marketing. > > No, they developed it because it was needed. I agree such improvements would be welcomed. I'm pretty sure they sat around saying we can already do that some other way at first, until the requests started to pile up. > > Stuff that they see fit to add is not necessarily going to be on our radar > > screen in the foreseeable future. I'm not clear on why there should be an inherent delay. I think PostgreSQL adoption is mostly held back by operational features, like performance management, locking, backup. But we're mainly constrained on people's time, i.e. money. And AFAICS nothing like this is going to happen in this release. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Howdy, all, I have a problem. I have a table which one of the fields is of type date. I need to obtain the totals of the other fields in a by-month basis IS there any easy way to do this using the GROUP BY or any other construct? Thanks in advance for your kind help Best, Oliveiros
am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes: > Howdy, all, > > I have a problem. > > I have a table which one of the fields is of type date. > > I need to obtain the totals of the other fields in a by-month basis > IS there any easy way to do this using the GROUP BY or any other construct? ... group by extract(month from date) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Oliveiros Cristina wrote: > Howdy, all, > > I have a problem. > > I have a table which one of the fields is of type date. > > I need to obtain the totals of the other fields in a by-month basis > IS there any easy way to do this using the GROUP BY or any other > construct? Yes, use date_trunc( 'month', time_stamp ). Example: select count(*),date_trunc( 'month', time_stamp ) from rb group by date_trunc( 'month', time_stamp ); Herouth
Oliveiros Cristina wrote: > Howdy, all, > > I have a problem. > > I have a table which one of the fields is of type date. > > I need to obtain the totals of the other fields in a by-month basis > IS there any easy way to do this using the GROUP BY or any other > construct? Yes, use date_trunc( 'month', time_stamp ). Example: select count(*),date_trunc( 'month', time_stamp ) from rb group by date_trunc( 'month', time_stamp ); Herouth
On Tue, Jul 15, 2008 at 2:17 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > I agree such improvements would be welcomed. I'm pretty sure they sat > around saying we can already do that some other way at first, until the > requests started to pile up. Agreed. > >> > Stuff that they see fit to add is not necessarily going to be on our radar >> > screen in the foreseeable future. > > I'm not clear on why there should be an inherent delay. I think > PostgreSQL adoption is mostly held back by operational features, like > performance management, locking, backup. I agree. From what I've seen, the lack of good incremental backup is still a major problem regarding adoption of large-scale systems. Similarly, upgrade-in-place makes using PG fairly prohibitive in several cases, though I'm glad Zdenek et al. are working on that. I still hear lots of questions regarding, "how do I tell what PG is doing right now?", which is why we (EnterpriseDB) developed RITA (the Runtime Instrumentation and Tracing Architecture). DTrace is great, but it's not cross-platform, can't be queried from within the database, and until now, didn't really give you enough information to diagnose many user-related problems. > But we're mainly constrained on people's time, i.e. money. And AFAICS > nothing like this is going to happen in this release. Agreed. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
On Tue, Jul 15, 2008 at 6:12 AM, Oliveiros Cristina
<oliveiros.cristina@marktest.pt> wrote:
> Howdy, all,
>
> I have a problem.
>
> I have a table which one of the fields is of type date.
>
> I need to obtain the totals of the other fields in a by-month basis
> IS there any easy way to do this using the GROUP BY or any other construct?
>
In addition to the responses on grouping by extract('month' from
timestamp) you can also index on this function as long as timestamp
isn't timestamp with timezone. With that index in place, grouping by
month can be pretty fast even for large datasets covering many months.
On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote:
> am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes:
> > Howdy, all,
> >
> > I have a problem.
> >
> > I have a table which one of the fields is of type date.
> >
> > I need to obtain the totals of the other fields in a by-month basis
> > IS there any easy way to do this using the GROUP BY or any other construct?
>
> ... group by extract(month from date)
>
>
> Andreas
It's worth noting that extract(month from timestamp) returns a month_no, and thus will not be suitable for grouping
queriesthat span years.
I recommend group by date_trunc('month', <<timestamp field>>)
-Mark
On Tue, Jul 15, 2008 at 7:15 PM, Mark Roberts
<mailing_lists@pandapocket.com> wrote:
>
> On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote:
>> am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes:
>> > Howdy, all,
>> >
>> > I have a problem.
>> >
>> > I have a table which one of the fields is of type date.
>> >
>> > I need to obtain the totals of the other fields in a by-month basis
>> > IS there any easy way to do this using the GROUP BY or any other construct?
>>
>> ... group by extract(month from date)
>>
>>
>> Andreas
>
> It's worth noting that extract(month from timestamp) returns a month_no, and thus will not be suitable for grouping
queriesthat span years.
>
> I recommend group by date_trunc('month', <<timestamp field>>)
Both have their uses. If you're viewing the last 5 decembers versus
the last 5 novembers, then extract would be a good choice. But mostly
date_trunc is more useful.
am Tue, dem 15.07.2008, um 15:57:54 -0600 mailte Scott Marlowe folgendes:
> On Tue, Jul 15, 2008 at 6:12 AM, Oliveiros Cristina
> <oliveiros.cristina@marktest.pt> wrote:
> > Howdy, all,
> >
> > I have a problem.
> >
> > I have a table which one of the fields is of type date.
> >
> > I need to obtain the totals of the other fields in a by-month basis
> > IS there any easy way to do this using the GROUP BY or any other construct?
> >
>
> In addition to the responses on grouping by extract('month' from
> timestamp) you can also index on this function as long as timestamp
> isn't timestamp with timezone. With that index in place, grouping by
You can also create an index on an timestamptz column for a particular
time zone ;-)
test=*# create table foo (ts timestamptz);
CREATE TABLE
test=*# create index foo_idx on foo(extract(month from ts at time zone 'GMT'));
CREATE INDEX
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts folgendes: > > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote: > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes: > > > Howdy, all, > > > > > > I have a problem. > > > > > > I have a table which one of the fields is of type date. > > > > > > I need to obtain the totals of the other fields in a by-month basis > > > IS there any easy way to do this using the GROUP BY or any other construct? > > > > ... group by extract(month from date) > > > > > > Andreas > > It's worth noting that extract(month from timestamp) returns a month_no, and thus will not be suitable for grouping queriesthat span years. Right, but that wasn't the question... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
> No, they developed it for marketing. Perhaps, but towards whom? PostgreSQL wouldn't hurt if a lot of developers and DBA's was lured into the trap by this new feature. > Keep in mind that Oracle has six thousand full-time developers and an > already extremely mature database. Stuff that they see fit to add is > not necessarily going to be on our radar screen in the foreseeable > future. I wasn't proposing to add it in 8.4. Just to add it to the TODO. Perhaps someone would look at it some point in the future. -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: kaare@jasonic.dk
On Wed, 2008-07-16 at 07:39 +0200, A. Kretschmer wrote: > am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts > folgendes: > > > > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote: > > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros > Cristina folgendes: > > > > Howdy, all, > > > > > > > > I have a problem. > > > > > > > > I have a table which one of the fields is of type date. > > > > > > > > I need to obtain the totals of the other fields in a by-month > basis > > > > IS there any easy way to do this using the GROUP BY or any other > construct? > > > > > > ... group by extract(month from date) > > > > > > > > > Andreas > > > > It's worth noting that extract(month from timestamp) returns a > month_no, and thus will not be suitable for grouping queries that span > years. > > Right, but that wasn't the question... Honestly, the way the question was phrased, I'd have assumed that it wanted to group by month (not group by a group of months). Jan 08 is distinct from Jan 07. Please accept my sincerest apologies if you you feel that I misinterpreted the question. I was merely trying to illustrate the difference between what each approach was. -Mark
All, Thanks a million for your help and thoughtful considerations. From this thread I learned lots. As my concrete problem just concerns one year, I used the extract function, but I ve mentally wrote down the date_trunc construct as suggested by Herouth Andreas and Scott, thanks for the tips on indexing Again, thanks a lot. Best, Oliveiros ----- Original Message ----- From: "Mark Roberts" <mailing_lists@pandapocket.com> Cc: <pgsql-sql@postgresql.org> Sent: Wednesday, July 16, 2008 5:29 PM Subject: Re: [SQL] How to GROUP results BY month > > On Wed, 2008-07-16 at 07:39 +0200, A. Kretschmer wrote: >> am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts >> folgendes: >> > >> > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote: >> > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros >> Cristina folgendes: >> > > > Howdy, all, >> > > > >> > > > I have a problem. >> > > > >> > > > I have a table which one of the fields is of type date. >> > > > >> > > > I need to obtain the totals of the other fields in a by-month >> basis >> > > > IS there any easy way to do this using the GROUP BY or any other >> construct? >> > > >> > > ... group by extract(month from date) >> > > >> > > >> > > Andreas >> > >> > It's worth noting that extract(month from timestamp) returns a >> month_no, and thus will not be suitable for grouping queries that span >> years. >> >> Right, but that wasn't the question... > > Honestly, the way the question was phrased, I'd have assumed that it > wanted to group by month (not group by a group of months). Jan 08 is > distinct from Jan 07. > > Please accept my sincerest apologies if you you feel that I > misinterpreted the question. I was merely trying to illustrate the > difference between what each approach was. > > -Mark > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
i think this work select id,count from table group by to_char(date,'MM') --- On Wed, 7/16/08, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > From: A. Kretschmer <andreas.kretschmer@schollglas.com> > Subject: Re: [SQL] How to GROUP results BY month > To: pgsql-sql@postgresql.org > Date: Wednesday, July 16, 2008, 5:39 AM > am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark > Roberts folgendes: > > > > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer > wrote: > > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte > Oliveiros Cristina folgendes: > > > > Howdy, all, > > > > > > > > I have a problem. > > > > > > > > I have a table which one of the fields is of > type date. > > > > > > > > I need to obtain the totals of the other > fields in a by-month basis > > > > IS there any easy way to do this using the > GROUP BY or any other construct? > > > > > > ... group by extract(month from date) > > > > > > > > > Andreas > > > > It's worth noting that extract(month from > timestamp) returns a month_no, and thus will not be > suitable for grouping queries that span years. > > Right, but that wasn't the question... > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: > -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA > http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Still another way to do :-) Thanks, Lennin. Best, Oliveiros ----- Original Message ----- From: "Lennin Caro" <lennin.caro@yahoo.com> To: <pgsql-sql@postgresql.org>; "A. Kretschmer" <andreas.kretschmer@schollglas.com> Sent: Friday, July 18, 2008 3:04 PM Subject: Re: [SQL] How to GROUP results BY month > > i think this work > > select id,count from table group by to_char(date,'MM') > > --- On Wed, 7/16/08, A. Kretschmer <andreas.kretschmer@schollglas.com> > wrote: > >> From: A. Kretschmer <andreas.kretschmer@schollglas.com> >> Subject: Re: [SQL] How to GROUP results BY month >> To: pgsql-sql@postgresql.org >> Date: Wednesday, July 16, 2008, 5:39 AM >> am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark >> Roberts folgendes: >> > >> > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer >> wrote: >> > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte >> Oliveiros Cristina folgendes: >> > > > Howdy, all, >> > > > >> > > > I have a problem. >> > > > >> > > > I have a table which one of the fields is of >> type date. >> > > > >> > > > I need to obtain the totals of the other >> fields in a by-month basis >> > > > IS there any easy way to do this using the >> GROUP BY or any other construct? >> > > >> > > ... group by extract(month from date) >> > > >> > > >> > > Andreas >> > >> > It's worth noting that extract(month from >> timestamp) returns a month_no, and thus will not be >> suitable for grouping queries that span years. >> >> Right, but that wasn't the question... >> >> >> Andreas >> -- >> Andreas Kretschmer >> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: >> -> Header) >> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA >> http://wwwkeys.de.pgp.net >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >