Re: Importing data w/ Unix timestamp

brianb-pggeneral@edsamail.com writes:
> I want to copy in some data from a tab-delimited flat file, but one of the
> columns that should translate into a datetime is in Unix timestamp format
> (seconds since epoch).

COPY isn't going to apply any datatype conversions for you.  What you
could do, though, is import into a temp table that has an int4 column
in that position, and then transfer the data to the real table with
something like

insert into RealTable select uname, timestamp(abstime(timeinAsInt4)),
                             duration, etc from TempTable;


Side comment: a lot of people seem to think that COPY is a data
translation utility.  It's not; it was only really intended as a simple
dump/reload method, for which purpose it should be as picky as possible
about the reloaded data.  At some point it'd be nice to have a program
that *is* designed as a data importer and is willing to do data format
conversions for you.  I envision this as a separate client program,
so it wouldn't take any deep dark backend-programming ability to write
it, just some knowledge about typical file formats and conversions.
Anyone want to take on the project?

            regards, tom lane