Re: BUG #1643: dbf2pg broken + quick fix - Mailing list pgsql-bugs

Boris van Schooten <schooten@cs.utwente.nl> writes:
> Don't know anything about nulls in dbf though.  I am not a dbase expert, I
> just run into dbfs often when trying to enter gis data into postgis.

I'm considering the following patch, which turns around the test: check
for an empty string and if so believe it's a null, otherwise just insert
the value as-is.  I dunno if the check for null is actually meaningful,
but I doubt this will break any cases that worked before.  Comments anyone?

            regards, tom lane


Index: contrib/dbase/dbf2pg.c
===================================================================
RCS file: /cvsroot/pgsql/contrib/dbase/dbf2pg.c,v
retrieving revision 1.21
diff -c -r1.21 dbf2pg.c
*** contrib/dbase/dbf2pg.c    14 Sep 2004 03:28:28 -0000    1.21
--- contrib/dbase/dbf2pg.c    4 May 2005 17:55:29 -0000
***************
*** 63,93 ****
  char       *convert_charset(char *string);
  #endif
  void        usage(void);
- unsigned int isinteger(char *);


-
- unsigned int
- isinteger(char *buff)
- {
-     char       *i = buff;
-
-     while (*i != '\0')
-     {
-         if (i == buff)
-             if ((*i == '-') ||
-                 (*i == '+'))
-             {
-                 i++;
-                 continue;
-             }
-         if (!isdigit((unsigned char) *i))
-             return 0;
-         i++;
-     }
-     return 1;
- }
-
  static inline void
  strtoupper(char *string)
  {
--- 63,70 ----
***************
*** 471,478 ****
                  /* handle the date first - liuk */
                  if (fields[h].db_type == 'D')
                  {
!                     if ((strlen(foo) == 8) && isinteger(foo))
                      {
                          snprintf(pgdate, 11, "%c%c%c%c-%c%c-%c%c",
                                   foo[0], foo[1], foo[2], foo[3],
                                   foo[4], foo[5], foo[6], foo[7]);
--- 448,462 ----
                  /* handle the date first - liuk */
                  if (fields[h].db_type == 'D')
                  {
!                     if (strlen(foo) == 0)
                      {
+                         /* assume empty string means a NULL */
+                         strcat(query, "\\N");
+                     }
+                     else if (strlen(foo) == 8 &&
+                              strspn(foo, "0123456789") == 8)
+                     {
+                         /* transform YYYYMMDD to Postgres style */
                          snprintf(pgdate, 11, "%c%c%c%c-%c%c-%c%c",
                                   foo[0], foo[1], foo[2], foo[3],
                                   foo[4], foo[5], foo[6], foo[7]);
***************
*** 480,505 ****
                      }
                      else
                      {
!                         /*
!                          * empty field must be inserted as NULL value in
!                          * this way
!                          */
!                         strcat(query, "\\N");
                      }
                  }
!                 else if ((fields[h].db_type == 'N') &&
!                          (fields[h].db_dec == 0))
                  {
!                     if (isinteger(foo))
!                         strcat(query, foo);
!                     else
                      {
                          strcat(query, "\\N");
-                         if (verbose)
-                             fprintf(stderr, "Illegal numeric value found "
-                                     "in record %d, field \"%s\"\n",
-                                     i, fields[h].db_name);
                      }
                  }
                  else
                  {
--- 464,482 ----
                      }
                      else
                      {
!                         /* try to insert it as-is */
!                         strcat(query, foo);
                      }
                  }
!                 else if (fields[h].db_type == 'N')
                  {
!                     if (strlen(foo) == 0)
                      {
+                         /* assume empty string means a NULL */
                          strcat(query, "\\N");
                      }
+                     else
+                         strcat(query, foo);
                  }
                  else
                  {