PosgreSQL NULL dates show parsing error

[mike_e]

I am copying data between two identical PostgreSQL databases.

I have several columns of type DATE. Some of the dates are empty, postgres
considers them NULL. When a column with a NULL source date is uploaded to the
downstream server this error occurs:

client] - PostgreSqlDatabasePlatform - Could not convert a value of for column
dateupdated of type DATE

• PostgreSqlDatabasePlatform - java.text.ParseException: Unable to parse the date:

When I look at the CSV file in symmetric-ds-3.0.10/tmp/outgoing/1 I see the
DATE is not represented as a NULL but just as blank. PostgreSQL will not
accept a blank insert into a DATE column, only NULL.

This is the beginning of the CSV line with a blank date, the first column is
'dateupdated'

insert,,"5","0","413","0","0","0","","","299","0","117"

How can I get this to work?

[Chris Henson]

I'll test tomorrow and release a patch if I can recreate. Thanks for the heads
up.

[Chris Henson]

Tested with Postgres 9 and SymmetricDS 3.1. Could not reproduce? I insert:

insert into test (id, created) values(2, null);

Am I doing something wrong to recreate?

[mike_e]

I am using PostgreSQL 8.4.9. I got the same error using 8.2.21.

Below is the entry in the error DB. Line 11 is the first record in the CSV
file that has a blank/null entry in the first field 'date updated'.

select * from sym_incoming_error where batch_id = 14;

batch_id | node_id | failed_row_number | failed_line_number |
target_catalog_name | target_schema_name | target_table_name | event_type |
binary_encoding |

column_names

|

pk_column_names

|

row_data

| old_data | resolve_data | resolve_ignore | create_time | last_update_by |
last_update_time

----------+---------+-------------------+--------------------+---------------------+--------------------+-------------------+------------+-----------------+--------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------+----------+--------------+----------------+-------------------------+----------------+-------------------------
14 | 00000 | 11 | 11 | | | listing | I | BASE64 | dateupdated,capacity,lastmod
ifiedagentid,capacityunit,businesstypeid,agerangebottom,admincontactid,eligibi
lity,webs

ite,programtypeid,everyday,active,intakecontactid,agencyid,reportproblemproced
ure,agerangetop,id,dateentered,description,additionalsiteinfo,reportproblemfla
g,responsetimeid,neverdeniedservice,spacesavailable,emailreminderid,intakeproc
ess,availabilityid,name,housingtypeid,

waitliststatus,waitlistopens,malebeds,femalebeds,malesuppsocincomebeds,females
uppsocincomebeds,selfrefer,providerrefer,coordinatingentity,acceptsuppsocincom
e,grossincomecost,facilitywaiver,maleambulatorybeds,femaleambulatorybeds,malea
mbulatoryssibeds,femaleambulatoryssibe

ds,coordinatingentityid,score,listingnumber,approvaldate,approvedby,reviewdate
,reviewedby,listingstatus,getcareentitytype,draftid,needsformalreview,source,l
engthofwait,lengthofwaitunit,waitlistfee,refundable,malemedicaid,malemedicare,
maledementia,femalemedicaid,femalemedi

care,femaledementia,seasonalfromdate,seasonaltodate,feerange,fixedfee,slidings
cale,suggesteddonation,nofee,grossincomepercent,ppcredicard,ppcash,ppcheck,cal
linfo,availscholarship,internalupdatedate,listinginternalstatus,referralagent,
externalupdate,aboutservicecosts,addit

ionalcomment,createdby,externalupdateby,externalupdatedate,internalupdateby,up
datestatus,deletestatus,otherreason,deletedate,deletedby,spacesavailablestr,fo
rmalupdate,alsoknownas,unitedwayid,listingprivate,bisreport,savedlistinglocati
on,ppdstatus,xraystatus,consumersitede

letedbyemail,housingunitsbuilding,housingbuildingtitle,email,housingbuildingco
ntactfirstname,housingbuildingcontactlastname,housingpropertytype,housingprope
rtyrestrictions,propertywaitlistid,photo,heading,propertyappear,propertyrelati
onship,buildingdescription,authorizerc

ontactid,lastmodifieddate,lastactionmade,alertactivated,alertmessage,createdby
consumer,createdbyconsumeremail,createdbyconsumerphone,undeletedby,undeletedda
te,previewslistingstatus,previewsactive,deliverysitename,whattobringtoapply,re
i,applicationform,applicationformfilen

ame,website2,updatedby,consumerlastrevieweddate,consumerlastreviewedby,updated
listingmarker,capacityaddinfo,intake,continuinged | dateupdated,capacity,lastm
odifiedagentid,capacityunit,businesstypeid,agerangebottom,admincontactid,eligi
bility,website,programtypeid,everyday,

active,intakecontactid,agencyid,reportproblemprocedure,agerangetop,id,dateente
red,description,additionalsiteinfo,reportproblemflag,responsetimeid,neverdenie
dservice,spacesavailable,emailreminderid,intakeprocess,availabilityid,name,hou
singtypeid,waitliststatus,waitlistopen

s,malebeds,femalebeds,malesuppsocincomebeds,femalesuppsocincomebeds,selfrefer,
providerrefer,coordinatingentity,acceptsuppsocincome,grossincomecost,facilityw
aiver,maleambulatorybeds,femaleambulatorybeds,maleambulatoryssibeds,femaleambu
latoryssibeds,coordinatingentityid,sco

re,listingnumber,approvaldate,approvedby,reviewdate,reviewedby,listingstatus,g
etcareentitytype,draftid,needsformalreview,source,lengthofwait,lengthofwaituni
t,waitlistfee,refundable,malemedicaid,malemedicare,maledementia,femalemedicaid
,femalemedicare,femaledementia,seasona

lfromdate,seasonaltodate,feerange,fixedfee,slidingscale,suggesteddonation,nofe
e,grossincomepercent,ppcredicard,ppcash,ppcheck,callinfo,availscholarship,inte
rnalupdatedate,listinginternalstatus,referralagent,externalupdate,aboutservice
costs,additionalcomment,createdby,exte

rnalupdateby,externalupdatedate,internalupdateby,updatestatus,deletestatus,oth
erreason,deletedate,deletedby,spacesavailablestr,formalupdate,alsoknownas,unit
edwayid,listingprivate,bisreport,savedlistinglocation,ppdstatus,xraystatus,con
sumersitedeletedbyemail,housingunitsbu

ilding,housingbuildingtitle,email,housingbuildingcontactfirstname,housingbuild
ingcontactlastname,housingpropertytype,housingpropertyrestrictions,propertywai
tlistid,photo,heading,propertyappear,propertyrelationship,buildingdescription,
authorizercontactid,lastmodifieddate,l

astactionmade,alertactivated,alertmessage,createdbyconsumer,createdbyconsumere
mail,createdbyconsumerphone,undeletedby,undeleteddate,previewslistingstatus,pr
eviewsactive,deliverysitename,whattobringtoapply,rei,applicationform,applicati
onformfilename,website2,updatedby,cons

umerlastrevieweddate,consumerlastreviewedby,updatedlistingmarker,capacityaddin
fo,intake,continuinged | "","5","0","413","0","0","0","","","299","0","117","0
","383139","","0","122174","2010-02-11 00:00:00","Assisted Living programs are
residential or facility-based that pr

ovide housing and supportive services, supervision, personalized assistance,
and/or health-related services to meet the needs of residents who are unable
to perform or need assistance in performing activities of daily living.
Assisted living facilities will create a uniqu

e service plan for each resident. ","","0","0","0","0","0","","0","Assisted
Living Program - Assisted Living Facilities","0","0",,"0","0","0","0","0","0",
"","0","0","0","0","0","0","0","0","3",,"2010-09-01
00:00:00","0",,"0","2450","1511","113918","0","MDOHCQ","0","0","0"

,"0","0","0","0","0","0","0",,,"0","0","0","0","0","0","0","0","0","0","0",,"0
","0","0",,,"0","0",,"0","0","0",,"2010-11-01 00:00:00","390523",,"0","","0","
0","0","0","0","0",,"","","","","","0","0","0","","","0","0","","0",,"","0",,,
,,"0",,"0","0",,"",,"","",,"0",,"0","0

",,"0","0" | | | | 2012-08-23 23:08:54.593 | symmetricds | 2012-08-23
23:08:54.593

(1 row)

[mike_e]

I tried again with a much smaller table, 2 columns, and got the same results..
Try recreating with the date column first rather than second.

=# select * from miketest ;

dateupdated | id

-------------+----
| 1

| 2

| 3

2011-10-01 | 4

[Chris Henson]

I was able to recreate when I removed the primary key. Do you have a primary
key on your table or a primary key with a date object? It would be nice to
verify. Regardless, the issue will be fixed soon.

[Chris Henson]

Can you see if this fixes your issue?

[http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric-
assemble/3.1.3-SNAPSHOT/symmetric-assemble-3.1.3-20120824.234304-2-server.zip]
(http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric-
assemble/3.1.3-SNAPSHOT/symmetric-assemble-3.1.3-20120824.234304-2-server.zip)

Fix was done under issue: http://www.symmetricds.org/issues/view.php?id=783

[mike_e]

That fixed it. Thanks you so much!