Thread: Question about copy from with timestamp format

On 07/29/2015 03:42 PM, Murali M wrote:
> Hi,
>
> I wanted to copy a file from local file system to postgres. I have
> timestamp value specified as:
> YYYYMMDDHH24 format -- for example:
> 2015072913 -- is July 29, 2015 at 13:00
>
> how do I import this data into a timestamp field?
>
> thanks, murali.
>
> PS: I believe if I need the hour, I need to use timestamp (I do not want
> to put the hour as a separate column). I believe date datatype does not
> work, if I am right??

test=# create table ts_test(ts_fld timestamp);
CREATE TABLE

test=# insert into ts_test values (to_timestamp('2015072913',
'YYYYMMDDHH24'));
INSERT 0 1

test=# select * from ts_test ;
        ts_fld
---------------------
  2015-07-29 13:00:00
(1 row)

For more information:

http://www.postgresql.org/docs/9.4/interactive/functions-formatting.html

--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/29/2015 03:55 PM, Murali M wrote:
> How do I specify that when I use copy from? this is what I am trying
> right now..
> copy myTable (myTimeCol, col2) from myFile delimiter as '\t'

Argh, missed that.

>
> I am not sure how to specify the time format..

Yeah, the time component prevents you from even changing the datestyle
to get the data in. Looks you are going to have either change the values
before you do the COPY or do the COPY to a temporary/staging table and
then do the to_timestamp when you transfer to the final table.

>
> thanks, murali.
>
>
> On Wed, Jul 29, 2015 at 3:49 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 07/29/2015 03:42 PM, Murali M wrote:
>
>         Hi,
>
>         I wanted to copy a file from local file system to postgres. I have
>         timestamp value specified as:
>         YYYYMMDDHH24 format -- for example:
>         2015072913 <tel:2015072913> -- is July 29, 2015 at 13:00
>
>         how do I import this data into a timestamp field?
>
>         thanks, murali.
>
>         PS: I believe if I need the hour, I need to use timestamp (I do
>         not want
>         to put the hour as a separate column). I believe date datatype
>         does not
>         work, if I am right??
>
>
>     test=# create table ts_test(ts_fld timestamp);
>     CREATE TABLE
>
>     test=# insert into ts_test values (to_timestamp('2015072913
>     <tel:2015072913>', 'YYYYMMDDHH24'));
>     INSERT 0 1
>
>     test=# select * from ts_test ;
>             ts_fld
>     ---------------------
>       2015-07-29 13:00:00
>     (1 row)
>
>     For more information:
>
>     http://www.postgresql.org/docs/9.4/interactive/functions-formatting.html
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/29/2015 03:55 PM, Murali M wrote:
> How do I specify that when I use copy from? this is what I am trying
> right now..
> copy myTable (myTimeCol, col2) from myFile delimiter as '\t'
>
> I am not sure how to specify the time format..

My previous post would have been more useful if I had added that the
temporary/staging table should have the 'timestamp' field set to
varchar/text so you could get the data in.

>
> thanks, murali.
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

> On 30 Jul 2015, at 2:27, Sherrylyn Branchaw <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
soundslike this is a brand-new table you're creating. If so, and if this is a one-time COPY operation, you can create a
textcolumn 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
byAdrian. 

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
outof range: "2015072913"' before the trigger even fired. I wonder if that's deliberate? I was able to implement a
workaroundby 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
addan INSERT rule that does the conversion from text to timestamp and inserts the row in the actual table. Finally, you
usethe 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.

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>> wrote:
>
>
>     > On 30 Jul 2015, at 2:27, Sherrylyn Branchaw <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,
itsounds like this is a brand-new table you're creating. If so, and if this is a one-time COPY operation, you can
createa 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
offeredby 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
triggerfires, so you don't get a chance to massage your data before actually inserting it. I got 'ERROR:  date/time
fieldvalue 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.klaver@aklaver.com

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

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.

As to your other question:

"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."

See this thread:

http://www.postgresql.org/message-id/AANLkTik6-sNYpAbiaZtipPJkqxbOrfVAZb1EFk0CNHYw@mail.gmail.com

in particular this post:

http://www.postgresql.org/message-id/24930.1276658060@sss.pgh.pa.us


--
Adrian Klaver
adrian.klaver@aklaver.com

> On 30 Jul 2015, at 17:59, Sherrylyn Branchaw <sbranchaw@gmail.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
actualINSERTs. Apparently, that also means it won't fire an INSERT rule and thus can't be used with an updatable view.
Thereare 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
COPYcommand has no place in its syntax for expressions (such as this type conversion). INSERT does, hence we can write
arule 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.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>> wrote:
>
>
>     > On 30 Jul 2015, at 2:27, Sherrylyn Branchaw <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,
itsounds like this is a brand-new table you're creating. If so, and if this is a one-time COPY operation, you can
createa 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
offeredby 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
triggerfires, so you don't get a chance to massage your data before actually inserting it. I got 'ERROR:  date/time
fieldvalue 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.klaver@aklaver.com
>

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

On 7/30/15 3:09 PM, Alban Hertroys wrote:
> COPY is a bit special, I'm afraid. For starters, although it works_like_  doing a bunch of INSERTs, it doesn't
performactual INSERTs. Apparently, that also means it won't fire an INSERT rule and thus can't be used with an
updatableview. 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
theCOPY command has no place in its syntax for expressions (such as this type conversion). INSERT does, hence we can
writea rule for it… 
>
> In hindsight it all makes sense. That doesn't bring you any closer to a solution, unfortunately.

By the way, if you're desperate enough to make this work during copy,
you could create a new type that understands that time format. It'd
involve some C coding though.

It would be nice if there was a way to do transforms during COPY. I
vaguely remember some discussion of that on hackers some time ago.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 08/05/2015 09:16 AM, Murali M wrote:
> Hi everyone,
>
> First of all, let me thank all of you for the very informative
> discussion. I will say my solution was to declare the field YYYYMMDDHH24
> as int (can handle till Dec 31, 2147, Hr23 -- which will be 2147123123).
> Also this way, I can still use between etc to select a range of dates..
> of course, I will miss validation.. I believe it will work for me to the
> best of my knowledge. (let me know if you have experiences with storing
> time as int and there are issues I have not thought of)..

postgres@production=# select to_date('201508051314', 'YYYYMMDDHH24MI') -
to_date('201508041314', 'YYYYMMDDHH24MI');
  ?column?
----------
         1


(1 row)





postgres@production=# select 201508051314 - 201508041314;

  ?column?


----------


     10000


(1 row)

postgres@production=# select 201508051314::timestamp -
201508041314::timestamp;
ERROR:  cannot cast type bigint to timestamp without time zone
LINE 1: select 201508051314::timestamp - 201508041314::timestamp;


So it comes down to where you want to spend the time, doing a one time
convert on import or do conversions every time you want to use the data
as a timestamp instead of the type it is stored as.

>
> thanks, murali.



--
Adrian Klaver
adrian.klaver@aklaver.com