Re: copy from with trigger

Well that's a bummer, ok. Thanks.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Sunday, April 05, 2009 10:27 PM
To: Chris spotts
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] copy from with trigger

Chris spotts <rfusca(at)gmail(dot)com> writes:
> I'm trying to copy from a tab delimited file. The dates inside the file
> are Unix timestamp style dates.
> I thought the following script would do the trick, but it just gives me
> an error saying
> ERROR: invalid input syntax for type timestamp: "1238736600"
> CONTEXT: COPY testtable line 1, column acquire_time: "1238736600"

A trigger isn't going to help in the least for that; the data has to be
a valid timestamp before the trigger will ever see it.

The usual trick for this type of conversion is to load the data into a
temporary table that has simple column types (eg integer or text) and
then do your transformations during an INSERT/SELECT into the real
target table.

regards, tom lane