[postgis-devel] DBF-Column type boolen and date

[Paul Ramsey]

DBF Date is an unrestricted YYYYMMDD format string of numbers, and as  
such often contains invalid dates, which PostgreSQL will dutifully  
fail to accept.  For example, it is not uncommon to find 00000000 in  
a DBF date field, which is year 0 (ok....), month 0 and day 0 (bzzzzt).

I'm not sure I've seen many examples of DBF booleans, but again if  
it's a character string, there is the possibility of a number of  
corner cases.

P

On Jan 11, 2006, at 9:59 AM, strk at refractions.net wrote:

> Attached is a patch that will implement
> support for Date and Bool DBF<->pgsql transfer.
>
> I didn't commit yet as I'm concerned about compatibility
> of PostgreSQL 'Date' type canonical output and DBF
> expectations in a 'Date' field.
>
> Also note the case mismatch for boolean types.
>
> If you happen to have Shapefile (or .dbf) writers/readers
> please test them to see how shp2pgsql and pgsql2shp cope
> with Date and Boolean fields (does anyone complain about them?).
>
> In particular, PostgreSQL Date canonical input and output would
> depend on locale settings, and there's no direct way to specify
> locale settings in shp2pgsql and pgsql2shp.
>
> Finally, note that the patch includes patches to the shapelib,
> not supporting writing of Date fields up to latest release.
>
> --strk;
>
> On Wed, Jan 11, 2006 at 04:39:08PM +0100, strk at refractions.net wrote:
>> [included postgis-devel]
>>
>> Thanks, here is (one of) the problem(s):
>>
>> The input has this form:
>>
>> Id         : 1
>> Date       : 20051130
>> Bool       : T
>>
>> This is safely imported in 'date' and 'bool' fields.
>>
>> When exported, postgresql returns this:
>>
>> Id         : 1
>> Date       : 2005-11-30
>> Bool       : t
>>
>> Attached is the output of pgsql2shp (after shp2pgsql).
>> Does your reader chokes ? Please try multiple readers
>> if you do have them.
>>
>> The problem (if it is a problem at all) is that
>> we use canonical output of every attribute w/out doing
>> any particular check, when exporting. So if the DBF
>> format specification expects a specific format for
>> dates and boolean we'll need to add a post processing
>> based on type.
>>
>> --strk;
>>
>
>
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
>
> -- 
>
>  /"\    ASCII Ribbon Campaign
>  \ /    Respect for low technology.
>   X     Keep e-mail messages readable by any computer system.
>  / \    Keep it ASCII.
>
> <pgis_loader_date_bool.patch>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel