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

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.klaver@aklaver.com <mailto:adrian.klaver@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
>         <haramrae@gmail.com <mailto:haramrae@gmail.com>
>         <mailto:haramrae@gmail.com <mailto:haramrae@gmail.com>>> wrote:
>
>
>              > On 30 Jul 2015, at 2:27, Sherrylyn Branchaw
>         <sbranchaw@gmail.com <mailto:sbranchaw@gmail.com>
>         <mailto: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, 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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com