Re: Question about copy from with timestamp format

Hi everyone,

First of all, let me thank all of you for the very informative discussion.
I will say my solution was to declare the field YYYYMMDDHH24 as int (can
handle till Dec 31, 2147, Hr23 -- which will be 2147123123). Also this way,
I can still use between etc to select a range of dates.. of course, I will
miss validation.. I believe it will work for me to the best of my
knowledge. (let me know if you have experiences with storing time as int
and there are issues I have not thought of)..

thanks, murali.

On Tue, Aug 4, 2015 at 10:22 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:

> On 7/30/15 3:09 PM, Alban Hertroys wrote:
>
>> COPY is a bit special, I'm afraid. For starters, although it works_like_
>> doing a bunch of INSERTs, it doesn't perform actual INSERTs. Apparently,
>> that also means it won't fire an INSERT rule and thus can't be used with an
>> updatable view. There are no rules on such a view (rules rewrite the query)
>> that would work for COPY.
>>
>> Now perhaps that sounds like a COPY rule is warranted for cases like
>> these, but that doesn't help, exactly because the COPY command has no place
>> in its syntax for expressions (such as this type conversion). INSERT does,
>> hence we can write a rule for it…
>>
>> In hindsight it all makes sense. That doesn't bring you any closer to a
>> solution, unfortunately.
>>
>
> By the way, if you're desperate enough to make this work during copy, you
> could create a new type that understands that time format. It'd involve
> some C coding though.
>
> It would be nice if there was a way to do transforms during COPY. I
> vaguely remember some discussion of that on hackers some time ago.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>