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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
