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;
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 |