Default value for date in FreeMarker

Question

I am generating an insert statements into Postgres using Freemarker template engine with the data coming from a csv file. There's a date-typed columns in the DB ('birthdate') for which the insert should contain a NULL in case the value is not present (to pacify Postgres) or the actual birthdate, if supplied. Something like these:

INSERT INTO web_iws.broker (id,create_timestamp,update_timestamp,sfid,deleted,account_name,first_name,last_name,title,ssn,phone,mobile_phone,fax,email,mailing_street,mailing_city,mailing_state,mailing_postal_code,mailing_country,broker_number,npn,ga,ga_number,birthdate,appointed,indor_corp) VALUES(nextval('web_iws.broker_id_seq'::regclass),now(),now(),'003i00234597gnNAAQ',FALSE,'001i045681aYEiMAAW','Mason','Demarcove','Broker','','','','','[email protected]','','','','','','BRN7J0943','','Savoy Associates','NJGA0306',NULL,'Appointed','');

INSERT INTO web_iws.broker (id,create_timestamp,update_timestamp,sfid,deleted,account_name,first_name,last_name,title,ssn,phone,mobile_phone,fax,email,mailing_street,mailing_city,mailing_state,mailing_postal_code,mailing_country,broker_number,npn,ga,ga_number,birthdate,appointed,indor_corp) VALUES(nextval('web_iws.broker_id_seq'::regclass),now(),now(),'003i07852RjON3sRN',FALSE,'001i0002'43567AAV','Maritza','Galsh','','145-98-1794','','','','[email protected]','718 River Road','Red Bank','NJ','07701','','BRNJ0877','7420702','Walsh Benefits','NJGA0008','1939-12-03','Appointed','Indv');

Is there in Freemarker something more elegant than this to express the above requirement:

"<#if birthdate?has_content>'${birthdate}',<#else>NULL,</#if>"

Perhaps a date built-in able to replace empty values with a configurable default or some kind of a ternary expression allowing to remove the if's...

Thank you in advance.

Update

I tried the suggestions posted by @ddekany, here's what I see:

Random original data record (in csv) with empty birthday value (empty space between NJGA0001,,Appointed) looks like this:

2015-07-28T19:59:09.000Z,2015-07-28T19:59:15.000Z,003i000002oQSRMAA4,FALSE,Centerpoint Consulting,Briant,Hlonan,,,(973) 522-7886,,,[email protected],,,,,,BRNJ0780,,BenefitMall,NJGA0001,,Appointed,Individual

When tried with:

"${birthdate?has_content?then(\"'${birthdate}'\", 'NULL')}"+

in my Java template, I'm getting an error:

11:52:37.168 [main] DEBUG util.BrokerInsertGeneratorTest - Error during saml template buildingfreemarker.core.ParseException: Error on line 1, column 672, in template insertProducingTemplate
Found then, expecting one of:  chunk,  is_date,  last,  root,  j_string,  contains,  is_hash,  long,  float,  ends_with,  namespace,  matches,  time,  values,  seq_last_index_of,  uncap_first,  byte,  substring,  is_transform,  web_safe,  groups,  seq_contains,  is_macro,  index_of,  word_list,  int,  is_method,  eval,  parent,  xml,  number,  capitalize,  if_exists,  rtf,  node_type,  double,  is_directive,  url,  size,  default,  is_boolean,  split,  node_name,  is_enumerable,  seq_index_of,  is_sequence,  sort,  is_node,  sort_by,  left_pad,  cap_first,  interpret,  children,  node_namespace,  chop_linebreak,  date,  short,  last_index_of,  is_collection,  ancestors,  length,  trim,  datetime,  is_string,  reverse,  c,  keys,  upper_case,  js_string,  has_content,  right_pad,  replace,  is_hash_ex,  new,  is_number,  is_indexable,  lower_case,  string,  exists,  html,  first,  starts_with

Using this expression:

"${(\"'${birthdate}',\")!'NULL'}"+

produces the following:

INSERT INTO web_iws.broker (id,create_timestamp,update_timestamp,sfid,deleted,account_name,first_name,last_name,title,ssn,phone,mobile_phone,fax,email,mailing_street,mailing_city,mailing_state,mailing_postal_code,mailing_country,broker_number,npn,ga,ga_number,birthdate,appointed,indor_corp) VALUES(nextval('web_iws.broker_id_seq'::regclass),now(),now(),'003i000002oQSRMAA4',FALSE,'Centerpoint Consulting','Briant','Hlonan','','','(973) 522-7886','','','[email protected]','','','','','','BRNJ0780','','BenefitMall','NJGA0001','','Appointed','Individual');

which is better as it creates 'NJGA0001','','Appointed', but won't be accepted by Postgres - it requires a NULL for an empty date field.

I'm open to hear other suggestions.

Here's the Java template string which works but requires less verbosity in handling its if statement:

public final static String templateStr = "INSERT INTO web_iws.broker (id,create_timestamp,update_timestamp,"
            + "sfid,deleted,account_name,first_name,last_name,title,ssn,phone,mobile_phone,fax,email,mailing_street,mailing_city,mailing_state,mailing_postal_code,mailing_country,broker_number,npn,ga,ga_number,birthdate,appointed,indor_corp) "+
 "VALUES(nextval('web_iws.broker_id_seq'::regclass),now(),now(),"+
"'${sfid}',"+
"${deleted},"+
"'${account_name}',"+
"'${first_name}',"+
"'${last_name}',"+
"'${title}',"+
"'${ssn}',"+
"'${phone}',"+
"'${mobile_phone}',"+
"'${fax}',"+
"'${email}',"+
"'${mailing_street}',"+
"'${mailing_city}',"+
"'${mailing_state}',"+
"'${mailing_postal_code}',"+
"'${mailing_country}',"+
"'${broker_number}',"+
"'${npn}',"+
"'${ga}',"+
"'${ga_number}',"+
"<#if birthdate?has_content>'${birthdate}',<#else>NULL,</#if>"+
"'${appointed}',"+
"'${indor_corp}');";

lance-java lance-java · Accepted Answer · 2015-10-29T13:30:16

You could use postgres' to_char function. eg:

<#if birthdate?has_content>to_char('${birthdate}', 'YYYY-MM-DD'),<#else>NULL,</#if>

http://www.postgresql.org/docs/current/static/functions-formatting.html

Default value for date in FreeMarker

Update

2 Answers