X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3A997D1B540 for ; Fri, 3 Oct 2003 11:47:31 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 20675-08 for ; Fri, 3 Oct 2003 08:46:48 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 02D1FD1B4E3 for ; Fri, 3 Oct 2003 08:46:45 -0300 (ADT) Received: (qmail 3300 invoked by uid 500); 3 Oct 2003 11:54:05 -0000 Date: Fri, 3 Oct 2003 06:54:05 -0500 From: Bruno Wolff III To: David B Cc: pgsql-sql@postgresql.org Subject: Re: How to figure out when was a table created Message-ID: <20031003115405.GA3039@wolff.to> Mail-Followup-To: David B , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/41 X-Sequence-Number: 15441 On Thu, Oct 02, 2003 at 16:01:16 -0700, David B wrote: > Hi folks, > > I posted this question a few days ago and got no response so I guess it > cannot be done (surprising!) > So that leaves me with my business problem. > > We create a table for each days activity. > After N days (typically 7 days) we can drop the table. > The table name is not known so cannot force business to make tablename > something like mydata_MMDDYY > > I'd like to be able to do something like: > SELECT tablename > FROM pg_??? > WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7 days', > 'dd/mm/yy' ) > > Any suggestions? You could consider changing the way you do things. You might be able to accomplish your goal by keeping the data in one table and keeping partial indexes on the table corresponding to the last seven days.