>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.kla...@aklaver.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 <haram...@gmail.com >> <mailto:haram...@gmail.com>> wrote: >> >> >> > On 30 Jul 2015, at 2:27, Sherrylyn Branchaw <sbranc...@gmail.com >> <mailto:sbranc...@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, 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.kla...@aklaver.com >
