pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Loading current_user and current_timestamp using COPY
5+ messages / 3 participants
[nested] [flat]

* Loading current_user and current_timestamp using COPY
@ 2001-10-09 15:09 Herb Blacker <herbblacker@isgenesis.com>
  2001-10-09 21:33 ` Re: Loading current_user and current_timestamp using COPY Josh Berkus <josh@agliodbs.com>
  2001-10-09 21:35 ` Re: Loading current_user and current_timestamp using COPY Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Herb Blacker @ 2001-10-09 15:09 UTC (permalink / raw)
  To: pgsql-novice@postgresql.org

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.

The declaration for the fields is:
.
.
updateUserId    text      DEFAULT current_user,
updateTimestamp timestamp DEFAULT current_timestamp,
.
Am I doing something wrong?

Thanks in advance,
-- 
Herb Blacker
Database Administrator
ReCare, Inc.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Loading current_user and current_timestamp using COPY
  2001-10-09 15:09 Loading current_user and current_timestamp using COPY Herb Blacker <herbblacker@isgenesis.com>
@ 2001-10-09 21:33 ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2001-10-09 21:33 UTC (permalink / raw)
  To: Herb Blacker <herbblacker@isgenesis.com>; pgsql-novice@postgresql.org

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:



______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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Loading current_user and current_timestamp using COPY
  2001-10-09 15:09 Loading current_user and current_timestamp using COPY Herb Blacker <herbblacker@isgenesis.com>
@ 2001-10-09 21:35 ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2001-10-09 21:35 UTC (permalink / raw)
  To: Herb Blacker <herbblacker@isgenesis.com>; pgsql-novice@postgresql.org; +Cc: pgsql-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@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [NOVICE] Loading current_user and current_timestamp using COPY
@ 2001-10-11 04:20 Josh Berkus <josh@agliodbs.com>
  2001-10-11 05:11 ` Re: [NOVICE] Loading current_user and current_timestamp using COPY Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Josh Berkus @ 2001-10-11 04:20 UTC (permalink / raw)
  To: Herb Blacker <hhb@isgenesis.com>; +Cc: pgsql-sql

Herb,

> Right now I'm loading 1,500 test records in my largest table...but I
> don't 
> want to do inserts...I'll stay with COPY and ignore the NULLS for
> now.
> In the future, however, I could be bulk loading up to 60,000 records.
> Of course, all records after that will be inserts, so perhaps the
> point is 
> moot. Although...if COPY can handle the now() function, it should be
> able 
> to handle the current_user/current_timestamp without too much
> overhead.
> Well....thanks for the help 

Sorry I couldn't be more help.  I haven't seen any answers on the SQL
list.  I can think of several workarounds, depending on what kind of a
user interface you're using, such as having a linked batches table.

-Josh Berkus

______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







^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [NOVICE] Loading current_user and current_timestamp using COPY
  2001-10-11 04:20 Re: [NOVICE] Loading current_user and current_timestamp using COPY Josh Berkus <josh@agliodbs.com>
@ 2001-10-11 05:11 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2001-10-11 05:11 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Herb Blacker <hhb@isgenesis.com>; pgsql-sql

>> Although...if COPY can handle the now() function, it should be able 
>> to handle the current_user/current_timestamp without too much overhead.

Eh?  COPY most assuredly does not handle the now() function, nor any
other function call.

It will accept 'now' --- no parens --- as an input for datetime
datatypes, but that's because those datatypes do, not because COPY
is doing anything special.

IMHO the entire point of COPY is to load bulk data as quickly as
possible ... if you want any convenience processing done for you,
you should be using INSERT not COPY.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-10-11 05:11 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-09 15:09 Loading current_user and current_timestamp using COPY Herb Blacker <herbblacker@isgenesis.com>
2001-10-09 21:33 ` Josh Berkus <josh@agliodbs.com>
2001-10-09 21:35 ` Josh Berkus <josh@agliodbs.com>
2001-10-11 04:20 Re: [NOVICE] Loading current_user and current_timestamp using COPY Josh Berkus <josh@agliodbs.com>
2001-10-11 05:11 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox