Обсуждение: Loading current_user and current_timestamp using COPY
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.
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
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