X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 38D0E9DC9B3 for ; Thu, 23 Mar 2006 09:24:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27334-08 for ; Thu, 23 Mar 2006 09:24:30 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 24BB59DC9E9 for ; Thu, 23 Mar 2006 09:24:25 -0400 (AST) Received: from laptop.thirdspace.tv (unknown [195.166.116.245]) by svr4.postgresql.org (Postfix) with ESMTP id AEFE45AF03C for ; Thu, 23 Mar 2006 13:24:28 +0000 (GMT) Received: from [127.0.0.1] (laptop.kieser2.demon.co.uk [127.0.0.1]) by laptop.thirdspace.tv (Postfix) with ESMTP id 2E68013B8B2 for ; Thu, 23 Mar 2006 13:23:09 +0000 (GMT) Message-ID: <4422A13D.8060804@sms-speedway.com> Date: Thu, 23 Mar 2006 13:23:09 +0000 From: Bradley Kieser User-Agent: Thunderbird 1.5 (X11/20051201) MIME-Version: 1.0 To: PgSQL ADMIN Subject: OT: using column in an interval Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200603/245 X-Sequence-Number: 21002 All, I know that this isn't strictly an admin question but please forgive me for asking. I am writing a (admin) job which does a select off a PG database based on three columns: last_backup: timestamp backup_unit: integer - Represents day, week, quarter, annual, etc. The text is stored in backup_code (e.g. 'days', 'months') backup_period: integer - Represents the skip factor. i.e. if backup_unit is 1 (days) and period is 3, then together they represent "every three days". Obviously I want to do something like: select id, client from backupSchedule where last_backup + interval backup_period backup_code <= CURRENT_DATE However, interval seems to only take text such as inverval '3 days' and I get an error even with this: select id, client from backupSchedule where last_backup + interval backup_period::text || backup_code <= CURRENT_DATE Can someone please point me to the right statement to use for column-based interval arithmetic? The docs all give hardcoded text strings in examples. Not found one yet with a proper column-based query!