PostgreSQL Timestamp problem

Gigasoft · Post by **Gigasoft** » Thu 31 Mar 2011 02:06

I'm trying to insert a number rows into a PostgreSQL table.
I've got an insert statement in a TUniQuery which includes the following timestamp value '20/05/2010 8:09:04 PM'
Whenever I try to execute it, I get an error back saying "Invalid input syntax for timestamp '20/05/2010 8$1$2 PM'"
I think the component is interpreting :09 and :04 as parameters.
Strange thing is, this is the 19th record to be inserted into the table this way, and the first to have any errors. (The second insert actually has a timestamp of "20/05/2010 8:09:05 PM" - just one second different, which works perfectly)
I've run the exact same query in PgAdmin III (the GUI system that came with my PostgreSQL server install) and it works without issue.

Is there another way I can format the timestamp to not include the colons? Or to force the component into not interpreting the string as having parameters?

AlexP · Post by **AlexP** » Thu 31 Mar 2011 08:17

Hello,

I could not reproduce the problem.
Please specify the exact version of UniDAC.
Also please specify the script to create a table, and the SQL text wich you are using to insert data.

Gigasoft · Post by **Gigasoft** » Thu 31 Mar 2011 21:52

UniDAC7 Trial V3.60.0.16 10-Mar-11
Table:

Code: Select all

CREATE TABLE cde_master
(
  "MasterSystemCode" character varying( 38 ) NOT NULL DEFAULT ''::character varying,
  "MasterUserCode" character varying( 30 ),
  "Title" character varying( 10 ),
  "Name" character varying( 120 ),
  "Firstname" character varying( 40 ),
  "MiddleName" character varying( 40 ),
  "Surname" character varying( 40 ),
  "LegalName" character varying( 120 ),
  "POBox" character varying( 60 ),
  "Street" character varying( 60 ),
  "Other" character varying( 60 ),
  "Suburb" character varying( 60 ),
  "State" character varying( 40 ),
  "Postcode" character varying( 4 ),
  "Phone" character varying( 15 ),
  "Fax" character varying( 15 ),
  "Mobile" character varying( 15 ),
  "eMail" character varying( 120 ),
  "Creditor" boolean DEFAULT FALSE,
  "Debtor" boolean DEFAULT FALSE,
  "Employee" boolean DEFAULT FALSE,
  "TradingName" character varying( 120 ),
  "ABN" character varying( 20 ),
  "ACN" character varying( 20 ),
  "RedundantC" boolean DEFAULT FALSE,
  "RedundantD" boolean DEFAULT FALSE,
  "RedundantE" boolean DEFAULT FALSE,
  "MinimumOrder" numeric(10,2),
  "PostedUser" character varying( 20 ) NOT NULL DEFAULT ''::character varying,
  "PostedSystemDate" date NOT NULL DEFAULT NULL,
  "PostedDT" timestamp NOT NULL DEFAULT NULL,
  "PostedMachine" character varying( 60 ) NOT NULL DEFAULT ''::character varying,
  "PostedCompany" character varying( 80 ) NOT NULL DEFAULT ''::character varying,
  "LastUpdatedUser" character varying( 20 ) NOT NULL DEFAULT ''::character varying,
  "LastUpdatedSystemDate" date NOT NULL DEFAULT NULL,
  "LastUpdatedDT" timestamp NOT NULL DEFAULT NULL,
  "LastUpdatedMachine" character varying( 60 ) NOT NULL DEFAULT ''::character varying,
  "LastUpdatedCompany" character varying( 80 ) NOT NULL DEFAULT ''::character varying,
  CONSTRAINT cde_master_pkey PRIMARY KEY ("MasterSystemCode")
);

Insert: (Edited to preserve privacy)

Code: Select all

INSERT INTO cde_master VALUES(E'{00FF7505-9DD3-4CD5-A4AF-035A42E0023A}', E'OKEST', NULL, E'STEPHEN O\KEEFE', NULL, NULL, NULL, NULL, NULL, E'--------------', NULL, E'-----------', E'------------', E'', E'---------', E'--------', E'------------', E'----------------------------', FALSE, TRUE, FALSE, E'', NULL, NULL, FALSE, FALSE, FALSE, NULL, E'DARYL', '20/05/2010', TIMESTAMP '20/05/2010 8:09:04 PM', E'GIGASOFT1\_Daryl', E'WHIP', E'DARYL', '20/05/2010', TIMESTAMP '20/05/2010 8:09:04 PM', E'GIGASOFT1\_Daryl', E'WHIP');

AlexP · Post by **AlexP** » Fri 01 Apr 2011 07:02

Hello,

Thank you for the information.
We have reproduced the problem.
We will notify you as soon as we have any results.

AlexP · Post by **AlexP** » Fri 01 Apr 2011 08:22

Hello,

Now you can use the following workaround - set '' (two single quotes) instead of \ like
....
E'STEPHEN O''KEEFE',
.....

Gigasoft · Post by **Gigasoft** » Tue 05 Apr 2011 06:43

Thank you very much.
That has solved the problem.

AlexP · Post by **AlexP** » Tue 05 Apr 2011 07:05

Hello,

It is good to see that this problem was solved.
We have fixed the problem.
This fix will be included in the next build.
If any other questions come up, please contact us.