Re: Question about copy from with timestamp format

On 07/30/2015 08:44 AM, Sherrylyn Branchaw wrote:
> 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.

From here:

http://www.postgresql.org/docs/9.4/interactive/sql-copy.html

"COPY can only be used with plain tables, not with views. However, you
can write COPY (SELECT * FROM viewname) TO ...."

>
> 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
> <mailto:haramrae(at)gmail(dot)com>> wrote:
>
>
> > On 30 Jul 2015, at 2:27, Sherrylyn Branchaw <sbranchaw(at)gmail(dot)com <mailto: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 <tel: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.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com