Re: Question about copy from with timestamp format

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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com