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)..
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
