[BUGS] Insert statement changes timestamp value from MS Access ODBC
Please see attached text document. Thanks, Dave David Dabney OHH Database Specialist/Programmer Hollings Marine Lab 131 Fort Johnson Road Charleston, SC 29464 843.762.8984 insert into public_collection (original_collection_code, method, station_id, date_time, sampling_project_id, contributor) select LUCES_wq.deploy_code, LUCES_wq.method, LUCES_wq.station_id, Min(LUCES_wq.datetime) AS MinOfdatetime, 12, 1 FROM LUCES_wq GROUP BY LUCES_wq.deploy_code, LUCES_wq.method, LUCES_wq.station_id ORDER BY LUCES_wq.deploy_code INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l, depth_m ) SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m FROM LUCES_wq as lwq INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l, depth_m) SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m FROM LUCES_wq as lwq INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code WHERE c.id <=1980 INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l, depth_m) SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m FROM LUCES_wq as lwq INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code WHERE c.id <=1981 and c.id >1980 TEST IN EMS insert into public.wq (collection_id, date_time, original_deployment_code, water_temp, depth_m, salinity, sp_cond, do_per, do_mg_l, ph) values (1982,'4/1/2001 3:00:00','MLM20010327',17.67,1.64,30.20,46.40,72.80,5.84,7.61) psql COPY wq ( collection_id , date_time , original_deployment_code , water_temp , depth_m , salinity , sp_cond , do_per , do_mg_l , ph ) FROM '/usr/local/pgsql/ddluces.csv' CSV; COPY ohh_v8=# Deleted inserted records and tried to do in Access with ' ' around datetime. Access gives type mismatch. Tried query w/out. Now it works. Should we always concatenate to insert date_time? Now all the sudden this works. INSERT INTO public_wq ( original_deployment_code, collection_id, date_time, water_temp, ph, sp_cond, salinity, do_per, do_mg_l, depth_m) SELECT deploy_code, c.id, datetime, water_temp, ph, sp_cond, salinity, do_sat, do_mg_l, depth_m FROM LUCES_wq as lwq INNER JOIN public_collection as c on lwq.deploy_code = c.original_collection_code WHERE c.id >=1982 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Insert statement changes timestamp value from MS Access
I'm sorry, but I attached the incorrect file the first time!
We are using MS Access as a frontend to import many water quality records into
postgresql v. 8.0.2 on Red Hat 3 Enterprise.
When importing a table of 40,000+ records something is going wrong with the
timestamp field only for certain records.
The records are listed below.
MS Access Details:
DATA TO IMPORT FROM LOCAL MSACCESS Table into linked ODBC pg table:
ID original_deployment_codedate_time water_temp depth_m
salinitysp_cond do_per do_mg_l ph
1 MLM20010327 2001-04-01 02:00:00 17.860001 1.49352
30.146.29 80.42 6.427.64
2 MLM20010327 2001-04-01 02:30:00 17.71 1.61544
30.21 46.42 78.67 6.3 7.64
3 MLM20010327 2001-04-01 03:00:00 17.67 1.64592
30.21 46.42 72.83 5.847.62
4 MLM20010327 2001-04-01 03:30:00 17.63 1.524
30.146.29 79.33 6.367.61
Here's the SQL run from Access:
INSERT INTO public_wq ( original_deployment_code, collection_id, date_time,
water_temp, ph, sp_cond, salinity, do_per, do_mg_l, depth_m )
SELECT original_deployment_code, c.id, lwq.date_time, water_temp, ph, sp_cond,
salinity, do_per, do_mg_l, depth_m
FROM DD5 AS lwq INNER JOIN public_collection AS c ON
lwq.original_deployment_code=c.original_collection_code;
I get a uniqueness constraint error.
Here are the Postgresql table details:
CREATE TABLE "public"."wq" (
"id" SERIAL,
"collection_id" INTEGER NOT NULL,
"date_time" TIMESTAMP(0) WITH TIME ZONE NOT NULL,
"original_deployment_code" VARCHAR(20),
"water_temp" NUMERIC(28,6),
"depth_m" NUMERIC(28,6),
"salinity" NUMERIC(28,6),
"sp_cond" NUMERIC(28,6),
"do_per" NUMERIC(28,6),
"do_mg_l" NUMERIC(28,6),
"ph" NUMERIC(28,6),
"turbidity" NUMERIC(28,6),
"chlorophyll_ug_l" NUMERIC(28,6),
"orp_mv" NUMERIC(28,6),
"reviewed" BOOLEAN DEFAULT false NOT NULL,
"date_entered" TIMESTAMP(0) WITH TIME ZONE NOT NULL,
"date_updated" TIMESTAMP(0) WITH TIME ZONE NOT NULL,
"entered_by" VARCHAR(50) NOT NULL,
"updated_by" VARCHAR(50) NOT NULL,
CONSTRAINT "wq_pkey" PRIMARY KEY("id"),
CONSTRAINT "fk_collection" FOREIGN KEY ("collection_id")
REFERENCES "public"."collection"("id")
ON DELETE NO ACTION
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;
COMMENT ON TABLE "public"."wq"
IS 'This is continuous wq. Unique constraint is on collection and date_time.
Do we need fields for raw and interpolated values???';
COMMENT ON COLUMN "public"."wq"."collection_id"
IS 'fk to collection table. ';
COMMENT ON COLUMN "public"."wq"."date_time"
IS 'Date that the sample was taken. This is usually in 30 minute increments
per collection.';
COMMENT ON COLUMN "public"."wq"."original_deployment_code"
IS '???';
COMMENT ON COLUMN "public"."wq"."do_per"
IS 'Calculate this in a trigger?';
COMMENT ON COLUMN "public"."wq"."reviewed"
IS 'This defaults to false and must be set manually in order to verify entries.
Possibly setup a view to filter these out.';
COMMENT ON COLUMN "public"."wq"."date_entered"
IS 'Triggered value upon insert. Will use current_timestamp unless specified.';
COMMENT ON COLUMN "public"."wq"."date_updated"
IS 'Triggered value upon update. Will use current_timestamp.';
COMMENT ON COLUMN "public"."wq"."entered_by"
IS 'Triggered value upon insert. Will use current_user unless otherwise
specified.';
COMMENT ON COLUMN "public"."wq"."updated_by"
IS 'Triggered value upon update. Will use current_user.';
CREATE UNIQUE INDEX "wq_unique_key" ON "public"."wq"
USING btree ("collection_id", "date_time");
CREATE TRIGGER "new_wq_trig" BEFORE INSERT
ON "public"."wq" FOR EACH ROW
EXECUTE PROCEDURE "public"."new_record_logger"();
CREATE TRIGGER "update_wq_trig" BEFORE UPDATE
ON "public"."wq" FOR EACH ROW
EXECUTE PROCEDURE "public"."update_record_logger"();
new_record_logger trigger
BEGIN
if new.date_entered is null then
new.date_entered := current_timestamp;
end if;
if new.entered_by is null then
new.entered_by := current_user;
end if;
if new.date_updated is null then
new.date_updated := current_timestamp;
end if;
if new.updated_by is null then
new.updated_by := current_user;
end if;
return new;
END;
RESULTS:
Taking the uniqueness constraint off allows import and the above inserted data
is below (Notice that 2:00 and 2:30 changed to 3:00 and 3:30 during the insert):
id collection_id date_time original_deployment_code
water_temp depth_m salinitysp_cond do_per do_mg_l ph
turbidity chlorophyll_ug_lorp_mv revieweddate_entered
date_updatedentered_by updated_by
204414 19822001-04-01 03:30:00 MLM20010327
17.63 1.524
Re: [BUGS] Insert statement changes timestamp value from MS Access
Tom, Thanks for the quick reply and I'm sorry I wasn't explicit enough in my description. At the top of the attachment there is this section which is the piece of data from DD5 that is causing the problem: MS Access Details: DATA TO IMPORT FROM LOCAL MSACCESS Table into linked ODBC pg table: ID original_deployment_codedate_time water_temp depth_m salinitysp_cond do_per do_mg_l ph 1 MLM20010327 2001-04-01 02:00:00 17.860001 1.49352 30.146.29 80.42 6.427.64 2 MLM20010327 2001-04-01 02:30:00 17.71 1.61544 30.21 46.42 78.67 6.3 7.64 3 MLM20010327 2001-04-01 03:00:00 17.67 1.64592 30.21 46.42 72.83 5.847.62 4 MLM20010327 2001-04-01 03:30:00 17.63 1.524 30.146.29 79.33 6.367.61 I needed to get this data in, so I tried to manually correct the invalid date_time field after inserting and then put the uniqueness constraint back on. From EMS I tried to change the times from 3:30 and 3:00 to 2:30 and 2:00. When I refresh the data it now shows 1:30 and 1:00 for these records! I then tried to change 1:30 and 1:00 to 2:30 and 2:00 and it went back to 3:30 and 3:00. So I backed up and tried to import directly from psql. The same results happen as when I did it from MS Access. I was driving home and realized 4/1/2001 was probably when daylight savings time changed.and it was. So I'm not sure if this is a bug or not. Regards, Dave Tom Lane wrote: "David Dabney" <[EMAIL PROTECTED]> writes: I'm assuming this is a bug. Perhaps, but there is absolutely no chance of anyone reproducing the problem from the information you've offered. You say "it fails when I try to insert data from table DD5", but you give us no clue about what data is in DD5. There is some generic advice about how to create a useful bug report here: http://www.postgresql.org/docs/8.0/static/bug-reporting.html The short and sweet version of it is "give us a SQL script that delivers a wrong result" ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Insert statement changes timestamp value from MS Access
The more I thought about this I realized it's not a bug and I'm sorry to have perstered the list. The fact that the data logger was deployed before and after a time change and it was not setup to update itself created this problem. I just have to push forward all the times after the change and then import the data. We've been trying to get the scientists to use UTC.maybe this will help them understand why they should be doing that. Thanks, Dave PS Postgres rocks. Please keep up the good work! David Dabney wrote: Tom, Thanks for the quick reply and I'm sorry I wasn't explicit enough in my description. At the top of the attachment there is this section which is the piece of data from DD5 that is causing the problem: MS Access Details: DATA TO IMPORT FROM LOCAL MSACCESS Table into linked ODBC pg table: ID original_deployment_codedate_time water_temp depth_m salinitysp_cond do_per do_mg_l ph 1 MLM20010327 2001-04-01 02:00:00 17.860001 1.49352 30.146.29 80.42 6.427.64 2 MLM20010327 2001-04-01 02:30:00 17.71 1.61544 30.21 46.42 78.67 6.3 7.64 3 MLM20010327 2001-04-01 03:00:00 17.67 1.64592 30.21 46.42 72.83 5.847.62 4 MLM20010327 2001-04-01 03:30:00 17.63 1.524 30.146.29 79.33 6.367.61 I needed to get this data in, so I tried to manually correct the invalid date_time field after inserting and then put the uniqueness constraint back on. From EMS I tried to change the times from 3:30 and 3:00 to 2:30 and 2:00. When I refresh the data it now shows 1:30 and 1:00 for these records! I then tried to change 1:30 and 1:00 to 2:30 and 2:00 and it went back to 3:30 and 3:00. So I backed up and tried to import directly from psql. The same results happen as when I did it from MS Access. I was driving home and realized 4/1/2001 was probably when daylight savings time changed.and it was. So I'm not sure if this is a bug or not. Regards, Dave Tom Lane wrote: "David Dabney" <[EMAIL PROTECTED]> writes: I'm assuming this is a bug. Perhaps, but there is absolutely no chance of anyone reproducing the problem from the information you've offered. You say "it fails when I try to insert data from table DD5", but you give us no clue about what data is in DD5. There is some generic advice about how to create a useful bug report here: http://www.postgresql.org/docs/8.0/static/bug-reporting.html The short and sweet version of it is "give us a SQL script that delivers a wrong result" ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
