Re: Question about copy from with timestamp format

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.

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
>