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

[strk at refractions.net]

Ok, I've made pgsql2shp handle formatting of Bool and Date
types. Nulls are always written in the expected form
(documented found in shapelib code).

Non-null are handled as follows:
	- Boolean are upercased 
	- Date are made YYYYMMDD

To make parsing of date simpler the dumper exports the
environmental variable PGDATESTYLE=ISO to ensure
dates are returned in the format YYYY-MM-DD.

Note that this also affects TIMESTAMP fields, which are
NOT handled in any particular way (end up in text DBF fields).

Another thing to take into account would be locale, but
user can probably handle that explicitly exporting the
appropriate environmental variables.

As for shp2pgsql valid formats are understood correctly by
postgresql. Invalid date would probably prevent shp2pgsql
output script to succeed. In that case you can still
run shp2pgsql in prepare mode (-p) and use a varchar field
rather then a date.

Comments ? Fears ? :)

--strk;


On Wed, Jan 11, 2006 at 08:16:11PM +0100, strk at refractions.net wrote:
> On Wed, Jan 11, 2006 at 10:35:19AM -0800, Paul Ramsey wrote:
> > 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).
> 
> 00000000 is already handled to represent the NULL value
> (interpretation found in shapelib).
> 
> Other invalid dates are still a problem.
> Maybe we should add explicit switches to shp2pgsql and pgsql2shp
> to actually use those types ?
> 
> --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.