Re: Question about copy from with timestamp format - Mailing list pgsql-general

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@gmail.com
> <mailto:haramrae@gmail.com>> wrote:
>
>
>     > On 30 Jul 2015, at 2:27, Sherrylyn Branchaw <sbranchaw@gmail.com <mailto:sbranchaw@gmail.com>> wrote:
>     >
>     > Based on your PS asking about data types and commenting that you don't want to put hour in a separate column,
itsounds like this is a brand-new table you're creating. If so, and if this is a one-time COPY operation, you can
createa 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
offeredby 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
triggerfires, so you don't get a chance to massage your data before actually inserting it. I got 'ERROR:  date/time
fieldvalue 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.klaver@aklaver.com