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. 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"' 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. Sherrylyn P.S. Yes, you're right that the date data type won't work if you want to keep the hour value in the same column. On Wed, Jul 29, 2015 at 7:47 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > 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.kla...@aklaver.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
