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.na...@bluetreble.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 >
