Re: Question about copy from with timestamp format

I was thinking that perhaps an updatable view might do the trick?

Interesting idea! Are you able to get it to work? I keep getting 'ERROR:
cannot copy to view "view_ts_test"' even before my trigger fires.
Inserting, though, works fine.

Still curious why the triggers I'm writing won't fire before my statement
errors out on copying to a view, or inserting an out-of-range timestamp,
when the trigger would resolve all the illegal operations if it just fired
first.

On Thu, Jul 30, 2015 at 5:57 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

>
> > On 30 Jul 2015, at 2:27, Sherrylyn Branchaw <sbranchaw(at)gmail(dot)com> wrote:
> >
> > Based on your PS asking about data types and commenting that you don't
> want to put hour in a separate column, it sounds like this is a brand-new
> table you're creating. If so, and if this is a one-time COPY operation, you
> can create a text column for the initial import. Then after you're done
> importing, you can execute
> >
> > ALTER TABLE ts_test ALTER COLUMN ts_fld TYPE TIMESTAMP USING
> (to_timestamp(ts_fld, 'YYYYMMDDHH24'));
> >
> > to convert the format of the imported data to a timestamp. Then you're
> set.
> >
> > If there will be ongoing imports of more files like this, though, you'll
> need the intermediate table solution offered by Adrian.
>
> Or keep both columns and update those where the text-column is NOT NULL
> and the timestamp column is NULL.
>
> > I was going to suggest a trigger, but it turns out that the data type
> checking happens even before the BEFORE trigger fires, so you don't get a
> chance to massage your data before actually inserting it. I got 'ERROR:
> date/time field value out of range: "2015072913"' before the trigger even
> fired. I wonder if that's deliberate? I was able to implement a workaround
> by adding a raw_ts_fld column of type text, but an extra column might be
> too ugly for you relative to a temp table, I don't know.
>
> I was thinking that perhaps an updatable view might do the trick?
>
> You would need to create a view with the timestamp column converted to
> text in the format in your CSV file. Next you add an INSERT rule that does
> the conversion from text to timestamp and inserts the row in the actual
> table. Finally, you use the view in the COPY statement instead of the table.
> Added bonus, you can now also use the view to export your table to the
> same CSV format.
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>