Skip site navigation (1) Skip section navigation (2)

Re: Loading current_user and current_timestamp using COPY

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: Herb Blacker <herbblacker(at)isgenesis(dot)com>, pgsql-novice(at)postgresql(dot)org
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Loading current_user and current_timestamp using COPY
Date: 2001-10-09 21:35:46
Message-ID: web-144726@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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(at)agliodbs(dot)com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

In response to

pgsql-novice by date

Next:From: Smith, David (DMSMITH)Date: 2001-10-10 17:19:26
Subject: Win2000 and postgresql
Previous:From: Josh BerkusDate: 2001-10-09 21:33:11
Subject: Re: Loading current_user and current_timestamp using COPY

pgsql-sql by date

Next:From: Allan EngelhardtDate: 2001-10-09 23:16:42
Subject: Re: ROUND function ??
Previous:From: Josh BerkusDate: 2001-10-09 21:33:11
Subject: Re: Loading current_user and current_timestamp using COPY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group