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 triggerChris 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.