X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id CDA949FB2DA for ; Thu, 10 Aug 2006 10:28:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 19782-01 for ; Thu, 10 Aug 2006 10:28:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id BB79D9FB269 for ; Thu, 10 Aug 2006 10:28:41 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 09B7930AEE; Thu, 10 Aug 2006 15:28:40 +0200 (MET DST) From: "Andrew Hammond" X-Newsgroups: pgsql.sql Subject: Re: timestamp (MS SQLServer's rowversion) functionality Date: 10 Aug 2006 06:28:30 -0700 Organization: http://groups.google.com Lines: 22 Message-ID: <1155216510.452118.282090@h48g2000cwc.googlegroups.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Complaints-To: groups-abuse@google.com In-Reply-To: User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.5) Gecko/20060731 Ubuntu/dapper-security Firefox/1.5.0.5,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: h48g2000cwc.googlegroups.com; posting-host=207.219.45.62; posting-account=cSUlcA0AAABWACaPjo2Z9C0GBLTio512 To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.099 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200608/61 X-Sequence-Number: 26019 Tomski wrote: > Hello! > As many of you know, SQL Server (2000) has peculiar data type "timestamp" > which is not SQL standard timestamp. In fact it is "rowversion" type. It > makes tha field to be updated with current timestamp when row is updated or > inserted. > Is there any similiar functionality in PostgreSQL? If not, how to achieve > that? > I need such fields in many tables. Maybe triggers could help? Do I have to > write functions for each trigger for each table? Or can it be done by one > function with parameters? Partial or final solutions are welcome :) Create your table with a column of type timestamp and DEFAULT (now()) and you have the on insert functionality. You need to use triggers to get the on update fuctionality (and also for inserts if you don't trust the application to leave it default). I think this is actually covered by an example in the triggers documentation for postgres. If not then there's certainly a full code solution in the archives of this list. Please do some research before asking questions to the list. Drew