Thread: copy from with trigger

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"

Its mapping the right value to the write column, but it doesn't appear
to be going through the trigger.
Here's the relevant setup info.  Just assume the upload file is one unix
style date.

Thanks for any help, I'm rather confused.

CREATE TABLE testtable
(
  acquire_time timestamp without time zone NOT NULL
);
CREATE FUNCTION importData() RETURNS trigger AS $$
    BEGIN
    NEW.acquire_time := TIMESTAMP 'epoch' + int4(NEW.acquire_time) *
INTERVAL '1 SECOND';
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER btestinsert
  BEFORE INSERT
  ON testtable
  FOR EACH ROW
  EXECUTE PROCEDURE importdata();


COPY testtable
(
  acquire_time
)
FROM '/home/testy/test.tab' WITH DELIMITER E'\t' CSV;

Chris spotts <rfusca@gmail.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

Well that's a bummer, ok.  Thanks.

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

Chris spotts <rfusca@gmail.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

Chris Spotts escribió:
> Well that's a bummer, ok.  Thanks.

See also

http://archives.postgresql.org/message-id/20090406173912.GB4525%40alvh.no-ip.org

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

That's a dead link for me.

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Monday, April 06, 2009 12:42 PM
To: Chris Spotts
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] copy from with trigger

Chris Spotts escribió:
> Well that's a bummer, ok.  Thanks.

See also

http://archives.postgresql.org/message-id/20090406173912.GB4525%40alvh.no-ip
.org

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Chris Spotts escribió:
> That's a dead link for me.

Yes, because the message was very new and the archive indexer hadn't
picked it up yet  :-)  Try again now.

> -----Original Message-----
> From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
> Sent: Monday, April 06, 2009 12:42 PM
> To: Chris Spotts
> Cc: 'Tom Lane'; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] copy from with trigger
>
> Chris Spotts escribió:
> > Well that's a bummer, ok.  Thanks.
>
> See also
>
> http://archives.postgresql.org/message-id/20090406173912.GB4525%40alvh.no-ip
> .org


--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.