Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f99LZpn94369; Tue, 9 Oct 2001 17:35:52 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 144726; Tue, 09 Oct 2001 14:35:46 -0700 From: "Josh Berkus" Subject: Re: Loading current_user and current_timestamp using COPY To: Herb Blacker , pgsql-novice@postgresql.org Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Tue, 09 Oct 2001 14:35:46 -0700 Message-ID: In-Reply-To: <3BC31321.1000807@isgenesis.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200110/57 X-Sequence-Number: 2161 Herb, > I'm trying to load the current_user and current timestamp (as an > audit) > in a set of data using the COPY FROM... statement. > I cannot get the session_user, current_user or user to give me > anything > but the text itself...I even tried the obsolete getpgusername() and > thats exactly what it entered! > > To get the current_timestamp, I had to use the now() function. Hmmm ... I was under the impression that you could not use any builtin functions with COPY. Just literals. I'm surprised that NOW() worked. Just in case, I'm cross-posting your question to the SQL list. Couple of suggestions for immediate workarounds: 1. If the data sets you are importing are small, use INSERT statements instead of COPY. 2. If you are importing large amounts of data, do the following: 2.1. Modify your table so that the UpdateUserID and UpdateTimestamp columns are last. 2.2. Set the defaults on those columns to current_user and current-timestamp. 2.3. COPY all columns except those two. They should populate according to the defaults (I hope). -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco