pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLoading 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