Обсуждение: BUG #5281: Timestamp fields not inserting from 8.3 to 8.4
The following bug has been logged online: Bug reference: 5281 Logged by: Jodi Escalante Email address: jescalante@assistgroup.com PostgreSQL version: 8.3 and 8.4 Operating system: Linux Description: Timestamp fields not inserting from 8.3 to 8.4 Details: Hi - I am trying to insert some data from one table in an 8.3 instance to the same table in an 8.4 instance. (I am actually using an ETL tool but get the same error with the tool or using pgAdmin. Everything is the same in both tables. I am erroring out on record number one on the first timestamptz field. I have tested several different tables and they all error out on the timestamptz field. Below is a sample SQL. I downloaded the most recent JDBC driver and that did not help. INSERT INTO assessment (id, created, taken, current_weight, note, assessment_type, stay_id, contact_id, estimated_discharge_date, cond_chf, cond_pulm_heart, cond_endocrine_other, cond_skin_temp, ) VALUES ( 50, 2008-01-11 15:06:40.257000 -07:00:00, 2008-01-11 00:00:00.000000 -07:00:00, 2000.0, NULL, Initial, 3452, 2147, NULL, N, N, N, N) ERROR: syntax error at or near "15" SQL state: 42601 Character: 545 "15" is the time section of the date field.
Jodi Escalante wrote: > I am erroring out on record number one on the first timestamptz field. I > have tested several different tables and they all error out on the > timestamptz field. Below is a sample SQL. > > I downloaded the most recent JDBC driver and that did not help. > > INSERT INTO assessment (id, created, taken, current_weight, note, > assessment_type, stay_id, > contact_id, estimated_discharge_date, > cond_chf, cond_pulm_heart, cond_endocrine_other, cond_skin_temp, ) > VALUES ( 50, 2008-01-11 15:06:40.257000 -07:00:00, > 2008-01-11 00:00:00.000000 -07:00:00, 2000.0, NULL, Initial, 3452, > 2147, NULL, N, N, N, N) Not a bug. You need to quote those timestamps. As in: INSERT INTO assessment (id, created, taken, current_weight, note, assessment_type, stay_id, contact_id, estimated_discharge_date, cond_chf, cond_pulm_heart, cond_endocrine_other, cond_skin_temp, ) VALUES ( 50, '2008-01-11 15:06:40.257000 -07:00:00', '2008-01-11 00:00:00.000000 -07:00:00', 2000.0, NULL, Initial, 3452, 2147, NULL, N, N, N, N) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
"Jodi Escalante" <jescalante@assistgroup.com> wrote: > INSERT INTO assessment (id, created, taken, current_weight, note, > assessment_type, stay_id, contact_id, estimated_discharge_date, > cond_chf, cond_pulm_heart, cond_endocrine_other, cond_skin_temp, > ) VALUES ( 50, 2008-01-11 15:06:40.257000 -07:00:00, > 2008-01-11 00:00:00.000000 -07:00:00, 2000.0, NULL, Initial, > 3452, 2147, NULL, N, N, N, N) Without quotes you've got the calculation (2008 minus 1 minus 11) which equals the integer 1996. It doesn't know what to make of the number which comes next. Try something like TIMESTAMP WITH TIME ZONE '2008-01-11 15:06:40.257000 -0700'. Similar issues seem to exist with most of your other literals. -Kevin
On Fri, Jan 15, 2010 at 9:38 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > "Jodi Escalante" <jescalante@assistgroup.com> wrote: > >> INSERT INTO assessment (id, created, taken, current_weight, note, >> assessment_type, stay_id, contact_id, estimated_discharge_date, >> cond_chf, cond_pulm_heart, cond_endocrine_other, cond_skin_temp, >> ) VALUES ( 50, =A02008-01-11 15:06:40.257000 -07:00:00, >> 2008-01-11 00:00:00.000000 -07:00:00, =A02000.0, =A0NULL, =A0Initial, >> 3452, 2147, =A0NULL, =A0N, =A0N, =A0N, =A0N) > > Without quotes you've got the calculation (2008 minus 1 minus 11) > which equals the integer 1996. =A0It doesn't know what to make of the > number which comes next. =A0Try something like TIMESTAMP WITH TIME > ZONE '2008-01-11 15:06:40.257000 -0700'. =A0Similar issues seem to > exist with most of your other literals. Just as a note this might have appeared to work in pre-8.3 but may have done the wrong thing. Definitely quote your literals. Best Wishes, Chris Travers