Re: Question about copy from with timestamp format

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

Just to be clear COPY FROM is from file to table and COPY TO is from
table/view to file.

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

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