Re: Question about copy from with timestamp format

> On 30 Jul 2015, at 17:59, Sherrylyn Branchaw <sbranchaw(at)gmail(dot)com> wrote:
>
> 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 ...."
>
> Right, so you can COPY FROM a view, but not, as far as I can tell, TO a view, unless Alban found a workaround.

Well, that's why I said it _might_ work.

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.

> On Thu, Jul 30, 2015 at 11:48 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> 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
>

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.