copy from with trigger

From: Chris spotts <rfusca(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: copy from with trigger
Date: 2009-04-06 02:36:06
Message-ID: 1238985366.17829.5.camel@chris-laptop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kashmir 2009-04-06 02:51:13 user defined aggregate for percentile calculations
Previous Message Colin Streicher 2009-04-05 23:11:08 Re: Connect to server PG from laptop java app